CODEWITHSUNDEEP
sql-servert-sql
Adrian
Adrian

Reputation: 346

SQL Server: selecting distinct values per one column

I was wandering if it's possible to filter select results removing values that partially overlap

For example below, i have thousands of records, but i need the 'week date' value to be unqiue, and in case of duplicates the one with the highest value should remain.

emplo   project_id  Value   week_Date    week_ActualStart   week_ActualEnd
A0001   project001  100     2015-12-28   2015-12-28         2016-01-03 
A0001   project001  60      2015-12-28   2016-01-01         2016-01-03

So only the first row should remain.

I could really use someone's advice

Upvotes: 0

Views: 75

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use ROW_NUMBER for this:

SELECT emplno, project_id, Value, week_Date,
       week_ActualStart, week_ActualEnd
FROM (
   SELECT emplno, project_id, Value, week_Date,
          week_ActualStart, week_ActualEnd,
          ROW_NUMBER() OVER (PARTITION BY emplno, week_Date 
                             ORDER BY Value DESC) AS rn
   FROM mytable) AS t
WHERE t.rn = 1

The query picks the row having the greatest Value per emplno, week_Date slice.

Upvotes: 0

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

Try something like the following:

;WITH WeekDateCte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY emplno, week_Date ORDER BY Value DESC) RowNo 
    FROM employee
)
SELECT * 
FROM WeekDateCte 
WHERE RowNo = 1

For more information about ROW_NUMBER function, check here.

NOTE: ROW_NUMBER() returns BIGINT.

Upvotes: 1

Related Questions