Re: Daily SQL quiz - revisited

Scott Ford (sford@wvus.org) wrote: : Thanks to all who contributed to the SQL quiz -- trying to get a number : of rows back when a max(col) is involved. The responses ranged from, "you : probably can't do this", to "this is basic". Given the sum of the : responses, I would have to say that the former is likely more accurate. : Perhaps I can simplify the question (albeit not the resolution, however): : Say we have a table that contains many rows. For each row, we have a : column called "sequence", such that we could have the following: : key_col sequence many other columns : ------- -------- ------------------ : 16 1 : 16 2 : 17 1 : 17 2 : 17 3... : Okay, you get the picture. If we know we want the most current record : for key value '16', we merely state, "select * from mytable where key_col : = 16 and sequence = (select max sequence from mytable where key_col = : 16)". This isn't elegant, but it seems to work. select key_col, max(sequence) max_seq from mytable group by key_col into temp temptable; select mytable.* from mytable, temptable where mytable.key_col = temptable.key_col and mytable.sequence = temptable.max_seq; Regards, Jim Fuller ICD Materials Support jimf@cv.hp.com