January 26, 2011 at 7:49 pm
Lets say you have the following tables:
Name:
--Name_ID Int
--Name Char(20)
Colors
-- Color Char(20)
-- Name_ID Int
-- Rank Int
So each name has 20 colors ranked from 1 to 20. I would like to get the top 10 colors for each name. Any way to do this in a T-SQL statement?
Thanks,
Mike
January 26, 2011 at 8:01 pm
I feel this is a straight forward answer ; we can understand the intricacies of the problem only if u post sample data.
For the meantime, you can have this:
SELECT NM.Name , Clr.Color
FROM Name NM
INNER JOIN Colors Clr
ON NM.Name_ID = Clr.Name_ID
WHERE Clr.Rank BETWEEN 1 AND 10
January 26, 2011 at 8:11 pm
Ok, now what if there is not a rank. What if the color is ordered by the way it is entered. How could I get the 1st 10 colors entered for each name?
Thanks,
Mike
January 26, 2011 at 10:33 pm
Try this :
; WITH Grouper AS
(
SELECT NM.Name , Clr.Color ,
RN = ROW_NUMBER() OVER(PARTITION BY Clr.Name_ID ORDER BY (SELECT 0))
FROM Name NM
INNER JOIN Colors Clr
ON NM.Name_ID = Clr.Name_ID
)
SELECT Name , Color
FROM Grouper
WHERE RN >= 1 AND RN <= 10
January 29, 2011 at 12:49 pm
Mike,
Another way to do this is using APPLY. In this case I don't know whether ROW_NUMBER() or APPLY would be more optimum.
SELECT NM.Name, Clr.Color
FROM Name NM
CROSS APPLY
(SELECT TOP 10 C.Color FROM Colors C
WHERE C.Name_ID = NM.Name_ID
ORDER BY C.Color
) AS Clr
Todd Fifield
January 29, 2011 at 4:14 pm
I understand the APPLY much better than the other solution. It works!
Now, using the same data, I was asked if there was an easy way to flatten the data. Example:
Name: John (1 record in name table)
Color: Red ( 1 record in color linked to John)
Color: Blue ( 1 record in color linked to John)
Color: Green ( 1 record in color linked to John)
(4 records total)
Turn it to 1 record result set with 4 columns: Name, c1, c2, c3 (John, Red, Blue, Green)
And... to do this for the entire name table.
Thanks All!
Mike
January 30, 2011 at 4:27 pm
mike 57299 (1/26/2011)
Ok, now what if there is not a rank. What if the color is ordered by the way it is entered. How could I get the 1st 10 colors entered for each name?Thanks,
Mike
Is there an identity column, a date column or any other column which determines when a row was inserted? Otherwise, how would you know the order it was entered?
January 30, 2011 at 4:56 pm
Yes, there is an identity column.
January 30, 2011 at 9:02 pm
mike 57299 (1/30/2011)
Yes, there is an identity column.
You can modify ColdCoffee's query to order by the id column. For example if the identity column is name [id], you can replace the "(SELECT 0)" in ColdCoffee's query with Clr.Id. Also, you may want to move the joining to the Name table outside of the CTE, but his query should work nonetheless.
January 30, 2011 at 9:48 pm
So ColdCoffee's solution would get me one record per name with up to 3 colors listed in 3 columns?
January 30, 2011 at 10:21 pm
Mike, are u sure you will be getting only 3 colors per name ?
-If yes, then we can fix the query easily.
Are there chances that u might get more than 3 colors per name ?
-If yes, we will have to use dynamic cross tabs to solve the problem
January 31, 2011 at 5:24 pm
mike 57299 (1/30/2011)
So ColdCoffee's solution would get me one record per name with up to 3 colors listed in 3 columns?
No, it would get you close to what you originally asked for.
January 31, 2011 at 7:23 pm
Mike,
The APPLY method I suggested first would be a bit messy if you want to pivot these out. You can use the PIVOT operator, but I've had some bad experiences using it as far as performance goes.
Here's an example of pivoting out up to 3 colors per person. You can expand on the idea. The basic idea is to create a CTE of all names and colors with their rankings and another one with the first one. You keep left joining for each color after the first one:
; WITH CTEClr AS
( SELECT NM.Name, Clr.Color
, ROW_NUMBER() OVER (PARTITION BY NM.Name ORDER BY Clr.Rank AS RowNum
FROM Name NM
INNER JOIN Colors Clr ON
NM.Name_ID = Clr.Name_ID
), X1 AS -- This gets the first row for each name
( SELECT Name, Color
FROM CDEClr
WHERE RowNum = 1
)
SELECT X1.Name, X1.Color
, X2.Color, X3.Color -- These are additional colors up to 3
FROM X1
LEFT JOIN
(SELECT CTEClr.Name, CTEClr.Color
FROM CTEClr
WHERE RowNum = 2
) AS X2 ON
X1.Name = X2.Name
LEFT JOIN
(SELECT CTEClr.Name, CTEClr.Color
FROM CTEClr
WHERE RowNum = 3
) AS X2 ON
X1.Name = X3.Name
Let me know if this helps.
Todd Fifield
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply