Distinct or... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Distinct or...

Author

Topic

Erik.doe
Starting Member

6 Posts

Posted - 2014-02-04 : 13:38:01

I need to get the result of all fields but only one of each different Field1.
It must be the record with the highest Field2 belonging to the same record.

TIA
Best regards
Erik

Table

ID Field1 Field2 Field3
1 AAA 12 ab
2 DDD 42 df
3 CCC 11 tr
4 BBB 15 ik
5 DDD 31 mn
6 BBB 11 pi
7 AAA 16 er
8 EEE 56 sw
9 BBB 18 dc

Result should be in this order:
7 AAA 16 er
9 BBB 18 dc
3 CCC 11 tr
2 DDD 42 df
8 EEE 56 sw

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2014-02-04 : 14:12:08

[code]

WITH CTE AS
(select Field1, max(Field2) as mField2 from @YourTable group by Field1)
select a.ID, a.Field1, a.Field2, a.Field3 from @YourTable a join CTE c on a.Field2 = c.mField2;

[/code]

Erik.doe
Starting Member

6 Posts

Posted - 2014-02-06 : 02:55:46

Hi MuMu88

It gives me not the correct result.
Row with ID 6 shows up in the result. It may not.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-06 : 03:16:54

;with cte(field2,Field1)
as
(select MAX(field2),Field1 from @YOURTABLE group by Field1)
select v.Id,c.Field1,c.Field2,v.Field3 FROM CTE as c
inner join @YOURTABLE as v
on v.Field2 = c.Field2
and v.Field1 = c.Field1
ORDER BY c.Field1

Veera

Erik.doe
Starting Member

6 Posts

Posted - 2014-02-06 : 03:26:59

Veera, Thank You very much.
Exactly what I was looking for.

Best regards
Erik

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-06 : 03:29:43

Welcome

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources