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 5 (of 5 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