CODEWITHSUNDEEP
sqlsql-server
Antediluvian
Antediluvian

Reputation: 723

Select group by with a max predicate

Quite often I have to do queries like below:

select    col1, max(id)
from      Table
where     col2 = 'value'
and       col3 = (    select    max(col3)
                      from      Table
                      where     col2 = 'value'
                 )
group by col1

Are there any other ways to avoid subqueries and temp tables? Basically I need a group by on all the rows with a particular max value. Assuming all proper indices are used.

Upvotes: 0

Views: 246

Answers (1)

e_i_pi
e_i_pi

Reputation: 4820

You can use an OLAP function to achieve this. I would say this solution is marginally better in that your predicates are not duplicated between the main query and subquery, so you don't violate DRY:

SELECT *
FROM (
    select    col1, max(id) as max_id,
              RANK() OVER (PARTITION BY col1 ORDER BY col3 DESC) AS irow
    from      [Member]
    where     col2 = 'value'
    group by  col1
) subquery
WHERE subquery.irow = 1

Upvotes: 1

Related Questions