Advanced grouping without using a sub query

Question

Hi guys

This is a sample of the data that I have.

-ID-     -Rank-  -Type-  -Status- -Amount-
1142474  2  Under Offer Approved  23
1148492  1  Present  Current   56
1148492  2  Under Offer Approved  3
2273605  1  Present  Current  24

Where the ID is the same I only want the record with the highest rank. So the end result of the query.

-ID-     -Rank-  -Type-  -Status- -Amount-
1142474  2  Under Offer Approved  23
1148492  1  Present  Current   56
2273605  1  Present  Current  24

Now to get the original data set is an expensive operation, so I don't want to do a group by the ID and then mins the rank and then joins back onto the dataset again. Hence the query needs to do its work another way.

Cheers Anthony

Answer 1

Options available in general include:

• Store the illustrated data in a temp table, then query the temp table.
• Use a WITH clause to define the complex query, then have the DBMS sort out the query.

The WITH clause effectively allows you to give a name to a sub-query; the optimizer will avoid re-evaluating it if at all possible. The TEMP table solution is likely to be the simplest. And that will do GROUP BY of ID and MIN(rank) and join back.

Answer 2

select t1.id
       , t1.rank
       , t1.type
       , t1.status
       , t1.amount

from   my_table t1 

       left outer join my_table as t2 
       on t1.id = t2.id 
    and 
       t2.rank < t1.rank 

where  t2.id is null

Answer 3

SELECT * FROM TheTable
WHERE 1 = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Rank DESC)

Answer 4

This will work:

with temp as (
select *, row_number() over (partition by id order by rank) as rownum
from table_name
)
select * from temp where rownum = 1

Will give one record per id where rank represents the least number

Answer 5

Why is getting the data set so expensive, I see nothing terribly complex here. Do you have the indexes you need, is the query using them? Are the statistics out of date?