CODEWITHSUNDEEP
sql
stats_noob
stats_noob

Reputation: 5935

Alternatives to the "Partition By" Statement in SQL

If I am interested in selecting records that have the largest value in each group of duplicate records (and some general conditions), I normally do this with the following SQL code:

select a.id, a.col2, b.col3
from (select id, col2, col3,
rank() over (partition by id order by col2 desc, col3 desc) as r1 from my_table where col2 > 5 and col3 > 5) a
where a.r1 =1

I am interested in learning alternate ways to do this.

Thank you!

Upvotes: 0

Views: 725

Answers (1)

forpas
forpas

Reputation: 164214

One way to do it is with NOT EXISTS:

SELECT t1.id, t1.col2, t1.col3
FROM my_table t1
WHERE t1.col2 > 5 AND t1.col3 > 5
  AND NOT EXISTS (
            SELECT 1
            FROM my_table t2
            WHERE t2.id = t1.id AND t2.col2 > 5 AND t2.col3 > 5
              AND (t2.col2 > t1.col2 OR (t2.col2 = t1.col2 AND t2.col3 > t1.col3))
          );

Or, if you use a CTE to preselect from the table:

WITH cte AS (
  SELECT id, col2, col3
  FROM my_table
  WHERE col2 > 5 AND col3 > 5
)
SELECT c1.*
FROM cte c1
WHERE NOT EXISTS (
            SELECT 1
            FROM cte c2
            WHERE c2.id = c1.id 
              AND (c2.col2 > c1.col2 OR (c2.col2 = c1.col2 AND c2.col3 > c1.col3))
          );

Depending on the requirement, the WHERE clause inside the subquery may be a lot more complex. This is one of the reasons that, if you can, you should use a window function.

Upvotes: 1

Related Questions