May 1, 2009 at 12:47 pm
I have the following data:
ID COLOR
1 red
1 blue
1 green
2 red
2 orange
3 pink
I need to return only one row for each unique/distinct ID - I don't care which "color" is returned. So I need to see the following:
ID COLOR
1 blue
2 red
3 pink
All data is in one table. Any idea how I can get it to just return the above?
Thanks
May 1, 2009 at 12:56 pm
If you don't care which colors are returned, something as simple as this will work:
DECLARE @Colors TABLE (ID int, Color varchar(12))
INSERT INTO @Colors
SELECT 1, 'red' UNION ALL
SELECT 1, 'blue' UNION ALL
SELECT 1, 'green' UNION ALL
SELECT 2, 'red' UNION ALL
SELECT 2, 'orange' UNION ALL
SELECT 3, 'pink'
SELECT ID, MIN(Color)
FROM @Colors
GROUP BY ID
May 12, 2009 at 11:56 am
OK this works and I thought all was good until the following was added to the equation:
ID COLOR DATE
1 red 5/1/09
1 blue 5/1/09
1 green 5/23/09
2 red 5/10/09
2 orange 5/10/09
3 pink 4/13/09
I need to return the following based on MIN date
Desired Result
ID COLOR DATE
1 blue 5/1/09
2 orange 5/10/09
3 pink 4/13/09
INSTEAD I get the following
ID COLOR DATE
1 red 5/1/09
1 blue 5/1/09
2 red 5/10/09
2 orange 5/10/09
3 pink 4/13/09
Regarding ID 1 & 2 - I don't care which color is returned, I just want one row for ID 1 and ID 2 and one color for each.
I've tried the following, but can not get my desired result
SELECT a.ID, a.COLOR
FROM TABLE a
inner join
(
SELECT ID, MIN(DATE)
FROM TABLE
GROUP BY ID
) b on
a.ID = b.ID
ORDER BY a.ID
May 12, 2009 at 1:43 pm
Here's a couple different ways to do it:
[Code]
DECLARE @Colors TABLE (ID int, Color varchar(12), Date datetime)
INSERT INTO @Colors
SELECT 1, 'red', '5/1/09' UNION ALL
SELECT 1, 'blue', '5/1/09' UNION ALL
SELECT 1, 'green', '5/23/09' UNION ALL
SELECT 2, 'red', '5/10/09' UNION ALL
SELECT 2, 'orange', '5/10/09' UNION ALL
SELECT 3, 'pink', '4/13/09'
SELECT ID,
Color,
Date
FROM (
SELECT C1.*,
RANK() OVER (PARTITION BY C1.ID, C1.Date ORDER BY C1.Color) as 'Rank'
FROM @Colors C1
INNER JOIN (
SELECT ID,
MIN(Date) FirstDate
FROM @Colors
GROUP BY ID
) C2 ON C1.ID=C2.ID AND C1.Date=C2.FirstDate
) C
WHERE [Rank]=1
SELECT ID,
MIN(Color),
Date
FROM (
SELECT C1.*
FROM @Colors C1
INNER JOIN (
SELECT ID,
MIN(Date) FirstDate
FROM @Colors
GROUP BY ID
) C2 ON C1.ID=C2.ID AND C1.Date=C2.FirstDate
) C
GROUP BY ID, Date
[/code]
May 12, 2009 at 4:03 pm
To build on Johns example, here is a mroe simplified version:SELECT
ID,
Color,
Date
FROM
(
SELECT
C1.*,
ROW_NUMBER() OVER (PARTITION BY C1.ID ORDER BY C1.Date ASC, C1.Color) as RowNum
FROM
@Colors C1
) AS T
WHERE RowNum = 1
May 12, 2009 at 4:07 pm
Ah, that will work. I was attempting to do just that when I came up with my version that used the RANK function. I just couldn't get away from the MIN(date) logic. I attempted to use the date in the OVER and ORDER BY, but just couldn't get the results right. Good job....seems so simple now that I've taken a step back.
May 13, 2009 at 7:13 am
Thank you all very much - its working.
Do you have any website that can help explain the ROW_NUMBER() function...this one is new to me, and I'm not totally understanding it.
THANKS!!
May 13, 2009 at 8:41 am
You're already on the web-site!! I would search SSC for ROW_NUMBER() as there's bound to be an article or several dozen forum posts where it is used and explained.
Also, don't forget BOL. It will have a worthwhile explanation as well.
May 13, 2009 at 8:49 am
There's an article on the home page of SSC now on ROW_NUMBER().
Here's the link: http://www.sqlservercentral.com/articles/T-SQL/66512/
May 14, 2009 at 9:09 am
1) Don't you hate it when the OP doesn't put in the full set of requirements to start with??
2) This sure does sound like a homework assignment to me.
:blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply