July 13, 2015 at 2:28 pm
Having issues with this query. I'm thinking it could be simplified with OVER/PARTITION somehow, but can't see the solution.
DECLARE @temp TABLE(widgetID varchar(50)
, funkCode char(2)
, ID int
);
INSERT INTO @temp(widgetID
, funkCode
, ID
)
SELECT 'A550', 'GG',10001
UNION
SELECT 'A550','EE', 10002
UNION
SELECT 'A451','XX',10003
UNION
SELECT 'A550','BB',10004
UNION
SELECT 'A451','XX',10005
UNION
SELECT 'A555','CC',10006
---------------------------------
-- is there a simpler way to do this?
-- I need the funkCode for the lowest ID for each widgetID
select T.widgetID, T.funkCode
from @temp T
inner join (
select widgetID, min(ID) as minID
from @temp
group by widgetID) as O
on T.widgetID = O.widgetID
and T.ID = O.minID
July 13, 2015 at 2:40 pm
;WITH cte AS (
SELECT widgetid, funkcode, ROW_NUMBER() OVER(PARTITION BY widgetid ORDER BY widgetid, id) AS rownum
FROM @temp)
SELECT *
FROM cte
WHERE rownum = 1
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2015 at 2:51 pm
Very nice, Kevin! Thanks!
July 13, 2015 at 3:54 pm
This also works, but it doesn't perform as well as Kevin's even though it doesn't use a CTE.
SELECT DISTINCT widgetID
, FIRST_VALUE(funkCode) OVER(PARTITION BY widgetID ORDER BY ID)
FROM @temp
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 14, 2015 at 7:43 am
Drew, there's beauty in simplicity! Great solution, even if a bit slower. Thanks!
SQL rocks!
\m/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply