Home > IT Answers > Development > COMPARING CELLS IN SQL
Ajibola
110 pts.
 COMPARING CELLS IN SQL
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

Software/Hardware used:
ASKED: July 22, 2005  3:16 PM
UPDATED: July 25, 2005  3:56 AM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  July 22, 2005  4:36 pm  by  Stevewaltz   0 pts.
All Answer Wiki Contributors:  Stevewaltz   0 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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

Welcome
 0 pts.