SQL Server: selecting distinct values per one column

Question

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

select * from (select Row_Number() Over(partition by week_Date Order by Value desc) ,.. ) a where rn = 1 — Pரதீப்
– Pரதீப், Commented Jan 9, 2017 at 10:24

Alexei - check Codidact · Accepted Answer · 2017-01-09 10:25:48Z

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.

Giorgos Betsos · Accepted Answer · 2017-01-09 10:26:09Z

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.

Collectives™ on Stack Overflow

SQL Server: selecting distinct values per one column

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related