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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy