Daily SQL quiz - revisited

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)". However, what do we do if we want the current records for each of the different key values. This is the basic challenge of the SQL quiz. We're beginning to think it cannot be done easily and that we should just throw in the towel by adding one more column to the table called "current". Then only the most current record for each key value would have the value "y" for the column current and one could say, "select * from mytable where current = "y"". Of course, we must then be careful to keep the "current" column correct and to never have more than one "y" value for any key value at any given moment. Thanks much for your responses to this nagging issue... -- Scott F. Ford, Data Base Administrator | sford@wvus.org bps (Benefit Panel Services) | elroy.jpl.nasa.gov!wvus!sford 888 South Figueroa Street, Suite 1400 | Voice: (213) 489-2694 Los Angeles, CA. 90017 | FAX: (213) 489-7973 "...The bozone layer: shielding the rest of the solar system from the earth's harmful effects...."