Re: sql query problem

Heather Dyson <heather.dyson_at_template.com> wrote:
>I have a table that has a primary key of
>number, code, month
>
>Let's say that I have the following data
>
>number code month
>------ ---- -----
>1 a 1
>1 b 1
>1 b 2
>
>I want to formulat a query so that I get only the rows with the greatest
>month values for number and code. So the result of my query for the
>example would be

If I understand you correctly, I think this will work.

Select number, code, month from mytable a where month = (select max(month) from mytable b

               where  a.number = b.number
                  and a.code   = b.code)

You match the table to itself joining on the number and code so you only bring back rows when the month value is equal to the highest month value for that number/code combination.

Warning...This comes from a background in another DBMS's SQL implementation. This should do the trick if I read things right, but there may be a function in Oracle SQL that will do the same thing without the subquery.

Hope this helps,
Frank Received on Fri Jan 19 1996 - 00:00:00 CET