April 1, 2013 at 3:30 pm
Hi
I have a query which returns the following
Key segment Value
A001 001 3
A001 002 4
A001 003 4
A002 001 2
A002 002 2
A002 003 1
A003 001 4
A003 002 4
A003 003 4
I would like to change the query so it only returns the first row for each Key which contains the max value for the key. The output I want with look like the following
Key segment Value
A001 002 4
A002 001 2
A003 001 4
Thanks in advance.
April 1, 2013 at 3:38 pm
Something like:
select key, segment, max(value) from whatevertheheckyourtablenameis
group by key,segment
order by key,segment
April 1, 2013 at 3:44 pm
David Webb-200187 (4/1/2013)
Something like:
select key, segment, max(value) from whatevertheheckyourtablenameis
group by key,segment
order by key,segment
That does not work, it does remove the records that does not have the max value. It returns the following
Key segment Value
A001 002 4
A001 003 4
A002 001 2
A002 002 2
A003 001 4
A003 002 4
A003 003 4
As you can see key A003 still has 3 records.
April 1, 2013 at 3:44 pm
SELECT
, segment, value
FROM (
SELECT
, segment, value,
ROW_NUMBER() OVER(PARTITION BY ORDER BY value DESC, segment) AS row_num
FROM dbo.tablename
) AS derived
WHERE
row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 2, 2013 at 7:47 am
ScottPletcher (4/1/2013)
SELECT
, segment, value
FROM (
SELECT
, segment, value,
ROW_NUMBER() OVER(PARTITION BY ORDER BY value DESC, segment) AS row_num
FROM dbo.tablename
) AS derived
WHERE
row_num = 1
Thank You very much, this works great.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply