CODEWITHSUNDEEP
sqlsql-server
Babak Mahmoudabadi
Babak Mahmoudabadi

Reputation: 455

select top 1 with max 2 fields

I have this table :

+------+-------+------------------------------------+
| id   | rev   | class                              |
+------+-------+------------------------------------+
| 1    | 10    | 2                                  |
| 1    | 10    | 5                                  |

| 2    | 40    | 6                                  |
| 2    | 50    | 6                                  |
| 2    | 52    | 1                                  |

| 3    | 33    | 3                                  |
| 3    | 63    | 5                                  |
+------+-------+------------------------------------+

I only need the rows where rev AND then class columns have max value.

+------+-------+------------------------------------+
| id   | rev   | class                              |
+------+-------+------------------------------------+
| 1    | 10    | 5                                  |
| 2    | 52    | 1                                  |
| 3    | 63    | 5                                  |
+------+-------+------------------------------------+

Query cost is important for me.

Upvotes: 0

Views: 1388

Answers (4)

Charles
Charles

Reputation: 342

Here is a SQL 2012 example. Very straight forward with the implied table and the PARTITION function.

Basically, with each ID as a partition/group, sort the values of the other fields in a descending order assigning each one an incrementing RowId, then only take the first one.

select id, rev, [class]
from
    (
        SELECT id, rev, [class], 
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY rev DESC, [class] desc) AS RowId
        FROM sample
    ) t
where RowId = 1

Here is the SQL Fiddle

Keep in mind, this works with the criteria in the example dataset, and not the MAX of two fields as stated in the question's title.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

The fastest way might be to have an index on t(id, rev) and t(id, class) and then do:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.id = t.id and t2.rev > t.rev
                 ) and
      not exists (select 1
                  from table t2
                  where t2.id = t.id and t2.class > t.class
                 );

SQL Server is pretty smart in terms of optimization, so the aggregation approach might be just as good. However, in terms of performance, this is just a bunch of index lookups.

Upvotes: 1

Avarkx
Avarkx

Reputation: 1065

Just the rows that satisfy the condition that it has both max values?

Here's an SQL Fiddle;

SELECT  h.id, h.rev, h.class
FROM (  SELECT  id, 
                MAX( rev ) rev, 
                MAX( class ) class
        FROM    Herp
        GROUP BY id ) derp
INNER JOIN Herp h
    ON  h.rev = derp.rev
    AND h.class = derp.class;

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156978

I guess you mean: the max of rev and the max of class. If not, please clarify what to do when there is no row where both fields have the highest value.

select id
,      max(rev)
,      max(class)
from   table
group
by     id

If you mean total value of rev and class use this:

select id
,      max
,      rev
from   table
where  id in
       (  select id
          ,      max(rev + class)
          from   table
          group
          by     id
       )

Upvotes: 0

Related Questions