April 2, 2012 at 4:27 am
Hi,
how do I select a column in a "GROUP BY" query that can not be included in the GROUP BY clause and can not be aggregated?
In the following example I need for each "typ" the max. "value" and the "id" belonging to it
My simplified table:
CREATE TABLE test_groupby
(
[id] [int] NOT NULL,
[typ] [nvarchar](5) NOT NULL,
[val] [int] NOT NULL
)
Values:
INSERT INTO test_groupby
(id,typ,val)
VALUES
(1,a,5),
(2,a,12),
(3,a,8),
(4,a,15),
(5,b,9),
(6,b,5),
(7,b,2),
(8,c,6),
(9,c,7)
expected output:
4,a,15
5,b,9
9,c,7
Thanks in advance for any help
April 2, 2012 at 4:38 am
this should do the trick
with cte as
(
select
ROW_NUMBER() over(PARTITION BY typ ORDER BY val desc) AS RowNum,
Id,
TYP,
val
from
test_groupby
)
select ID, Typ, Val from cte where rownum = 1
April 2, 2012 at 6:51 am
It did the trick,
was exactly what i needed
Thank you
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply