COMPARING CELLS IN SQL

110 pts.
Tags:
SQL
SQL Server 2000
My database KoloID FIRST SECOND THIRD FORTH 01 4 8 6 10 02 10 8 12 9 03 4 5 6 9 04 3 6 6 9 ETC How can I use select to return P,P1, P2,... P10 ETC P = relationship between values of the last koloId(04) and koloID(3) = Number of cell with the sam values = 2 (6 and 9) P1 = relationship between values of the last koloId(04) and koloID(2) = Number of cell with the sam values = 1 ( 9) P2 = relationship between values of the last koloId(04) and koloID(1) = Number of cell with the sam values = 1 (6 ) The database is being updated every week The return should be in this form KoloID P P1 P2 P3 01 - - - - 02 1 - - - 03 1 2 - - 04 2 1 1 - IF FOR THE NEXT WEEK UPDATE VALUES ARE(05 4, 6, 7, 9) THE RETURN SHOULD BE 05 2 2 1 1 Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

It looks like this is 1 of 2 very similar questions you posted. For this question at least, I would suggest that you normalize the data. That is, restructure the table as numberid, jobnumber, jobcount. If you’d rather not restructure the table, you can use a subselect to allow ou to treat the data as if it is in a normalized table. Either way, you can then compare various jobcount values across different numberid values.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Welcome
    Hi, It looks to me that your main problem here is that the number of COLUMNS of your result set is increasing indefinitely, if you really want to compare EACH new row with ALL the previous ones. I don't know what database you are using, but for example you use Oracle8i or higher, then you can easily use an analytical function named LAG for comparing the current row's values with the values in ANY of the previous rows. For example: LAG(First,2) OVER (ORDER BY KoloId) means "the value of First in the row that is 2 rows preceeding the current row in the ordering indicated. Another solution could be to self-join the table to itself, like this SELECT ... FROM my_table t1, my_table t2 where t2.KoloId > t1.KoloId This allows you to compare EACH row to each of the preceeding rows, but, again, you should find a method to decide upon how many columns you want to return, which means, how many preceeding rows do you want to compare with. Rgds, Iudith
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following