CODEWITHSUNDEEP
sqlsql-server-2014window-functions
Neha
Neha

Reputation: 233

SQL Partition by clause

I am using ROW_NUMBER function in sql within a Partition of a result set where I want only the first row in each partition. Here is the query :

Select A, B, C, ROW_NUM
FROM
(SELECT A,B,C, ROW_NUMBER() OVER (PARTITION BY A ORDER BY C DESC) AS R0W_NUM
FROM TABLE 
)X
WHERE ROW_NUM = 1

It yields the desired results.

But instead of this, now I am using the FIRST_VALUE function as follows :

SELECT A,B,C, FIRST_VALUE(C) OVER (PARTITION BY A ORDER BY C DESC) AS ROW_NUM
FROM TABLE

But the above query gives me duplicate values as well. Can anyone guide me on how do I get rid of the duplicates ? I tried using DISTINCT, does not help.

Upvotes: 0

Views: 418

Answers (1)

Karin
Karin

Reputation: 26

Using FIRST_VALUE on C will not get rid of duplicates of A.

If you want to get rid of duplicates of A you could use one of the following querys, depending on the role of B.

If B is considered part of the partition, similar to A

Select A, B, MAX(C)
FROM TABLE
GROUP BY A, B

If B contains a value that can be aggregated (min, max, sum...) similar to C

Select A, MAX(B), MAX(C)
FROM TABLE
GROUP BY A

If B is neither similar to A (possible to partition by) or C (possible to aggregate) then I would keep the ROW_NUMBER() solution you included in the question.

Upvotes: 0

Related Questions