Re: Daily SQL quiz - revisited

In article <1993May13.170650.27426@wvus.org> sford@wvus.org (Scott Ford) writes: >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. > Sorry, but this IS basic :-) In any true SQL database (i.e., ANSI-compliant SQL-86 and up), you say: select * from mytable outermytable where sequence = (select max(sequence) from mytable innermytable where innermytable.keycol = outermytable.keycol) This is called a correlated subquery. The extra names assigned in the from clause after each table name allow you to differentiate the inner version of mytable from the outer version of mytable (these are technically called correlation names but most people call them table aliases). In the subquery, the where clause restricts its execution to those rows that are equal to the "current" keycol in the outer query. This subquery gets run once for each row of the outer query (or probably less, since the system will probably only run it once for each unique value of keycol and cache the results). The only problem you may find is that there are many SQLs which do NOT support correlated subqueries. However, if they don't, they are NOT ANSI-compliant. Good luck, Jon Rosen