October 16, 2013 at 5:39 pm
Hello Everyone
I am working on a query that needs to select the MAX(DateStated). There are other rows with the same ProductID and Different RowID. I am trying to select the Only row if there is only one ProductID, and if there are Multiple of the same ProductID, then select the one with the MAX(DateStarted)
I have used Row_Number(), but I was asked to not use that for this query. Kind of makes things rather difficult. This query will not only need to be ran against a SQL Server database, but possible a (And I hate saying this) DB2 database.:crying:
I greatly appreciate any and all assistance. Thank You in advance for all your suggestions, assistance and comments
Andrew SQLDBA
CREATE TABLE #AllData
(
DateStarted Datetime
,RowID int
,ProductID bigint
)
INSERT INTO #AllData
SELECT '2012-06-01 06:03:46.023',66937,777002387 UNION ALL
SELECT '2012-06-01 14:03:57.780',67100,777002387 UNION ALL
SELECT '2012-06-02 06:03:44.377',67243,777002387 UNION ALL
SELECT '2012-06-02 14:03:43.873',67347,777002387 UNION ALL
SELECT '2012-09-24 21:09:16.183',75558,701327244 UNION ALL
SELECT '2012-06-30 06:02:40.850',75357,701298894 UNION ALL
SELECT '2012-06-30 14:02:42.547',75461,701298894 UNION ALL
SELECT '2012-06-30 06:02:56.210',75369,701298827 UNION ALL
SELECT '2012-06-30 14:03:00.770',75473,701298827 UNION ALL
SELECT '2012-06-29 06:02:39.467',75157,701298589 UNION ALL
SELECT '2012-06-29 14:02:37.700',75260,701298589 UNION ALL
SELECT '2012-06-30 06:03:11.013',75381,701298589 UNION ALL
SELECT '2012-06-30 14:03:14.190',75484,701298589 UNION ALL
SELECT '2012-06-29 06:02:41.137',75158,701298587 UNION ALL
SELECT '2012-06-29 14:02:39.553',75261,701298587 UNION ALL
SELECT '2012-06-30 06:03:12.400',75382,701298587 UNION ALL
SELECT '2012-06-30 14:03:15.590',75485,701298587 UNION ALL
SELECT '2012-06-28 14:02:35.307',75052,701298403 UNION ALL
SELECT '2012-06-29 06:03:05.407',75173,701298373 UNION ALL
SELECT '2012-06-29 14:03:01.060',75275,701298373 UNION ALL
SELECT '2012-06-30 06:03:23.780',75394,701298373 UNION ALL
SELECT '2012-06-30 14:03:25.600',75496,701298373 UNION ALL
SELECT '2012-06-28 06:02:45.220',74955,701298288 UNION ALL
SELECT '2012-06-28 14:02:47.733',75059,701298288 UNION ALL
SELECT '2012-06-28 06:03:02.437',74966,701298112 UNION ALL
SELECT '2012-06-28 14:03:06.297',75070,701298112 UNION ALL
SELECT '2012-06-29 06:03:15.763',75185,701298112 UNION ALL
SELECT '2012-06-29 14:03:09.863',75286,701298112 UNION ALL
SELECT '2012-06-27 06:02:36.813',74753,701297947 UNION ALL
SELECT '2012-06-27 14:02:42.880',74856,701297947 UNION ALL
SELECT '2012-06-28 06:03:19.753',74977,701297947 UNION ALL
SELECT '2012-06-28 14:03:21.300',75081,701297947 UNION ALL
SELECT '2012-06-29 06:03:22.450',75194,701297947 UNION ALL
SELECT '2012-06-29 14:03:15.533',75295,701297947 UNION ALL
SELECT '2012-06-26 14:02:33.670',74652,701297565 UNION ALL
SELECT '2012-06-27 06:03:01.627',74768,701297565 UNION ALL
SELECT '2012-06-27 14:03:08.833',74871,701297565
SELECT * FROM #AllData
ORDER BY ProductID DESC
DROP TABLE #AllData
ResultSet:
DateStartedRowIDProductID
2012-06-01 06:03:46.02366937777002387
2012-06-01 14:03:57.78067100777002387
2012-06-02 06:03:44.37767243777002387
2012-06-02 14:03:43.87367347777002387 <-- Need This one
2012-09-24 21:09:16.18375558701327244 <-- Need This one
2012-06-30 06:02:40.85075357701298894
2012-06-30 14:02:42.54775461701298894 <-- Need This one
2012-06-30 06:02:56.21075369701298827
2012-06-30 14:03:00.77075473701298827 <-- Need This one
2012-06-29 06:02:39.46775157701298589
2012-06-29 14:02:37.70075260701298589
2012-06-30 06:03:11.01375381701298589
2012-06-30 14:03:14.19075484701298589 <-- Need This one
2012-06-29 06:02:41.13775158701298587
2012-06-29 14:02:39.55375261701298587
2012-06-30 06:03:12.40075382701298587
2012-06-30 14:03:15.59075485701298587 <-- Need This one
2012-06-28 14:02:35.30775052701298403
2012-06-29 06:03:05.40775173701298373
2012-06-29 14:03:01.06075275701298373
2012-06-30 06:03:23.78075394701298373
2012-06-30 14:03:25.60075496701298373 <-- Need This one
2012-06-28 06:02:45.22074955701298288
2012-06-28 14:02:47.73375059701298288 <-- Need This one
2012-06-28 06:03:02.43774966701298112
2012-06-28 14:03:06.29775070701298112
2012-06-29 06:03:15.76375185701298112
2012-06-29 14:03:09.86375286701298112 <-- Need This one
2012-06-27 06:02:36.81374753701297947
2012-06-27 14:02:42.88074856701297947
2012-06-28 06:03:19.75374977701297947
2012-06-28 14:03:21.30075081701297947
2012-06-29 06:03:22.45075194701297947
2012-06-29 14:03:15.53375295701297947 <-- Need This one
2012-06-26 14:02:33.67074652701297565
2012-06-27 06:03:01.62774768701297565
2012-06-27 14:03:08.83374871701297565 <-- Need This one
October 16, 2013 at 6:28 pm
As long as you don't have any duplicates in the MAX date, you can do it this way:
WITH GroupedData AS
(
SELECT DateStarted=MAX(DateStarted), ProductID
FROM #AllData
GROUP BY ProductID
)
SELECT a.*
FROM #AllData a
JOIN GroupedData b
ON a.ProductID = b.ProductID AND a.DateStarted = b.DateStarted
ORDER BY a.ProductID DESC;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 16, 2013 at 11:10 pm
dwain.c (10/16/2013)
As long as you don't have any duplicates in the MAX date:
SELECT * FROM #AllData WHERE CONVERT(VARCHAR(20),DateStarted) + CONVERT(VARCHAR(30), ProductID) IN(
SELECT CONVERT(VARCHAR(20),max(DateStarted))+ CONVERT(VARCHAR(30), ProductID) FROM #AllData
group BY ProductID)
October 16, 2013 at 11:50 pm
Thanks DWain
That is exactly what I was after. Works very fast also. Got to Love that part.
Andrew SQLDBA
October 17, 2013 at 2:02 am
You're most welcome.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 17, 2013 at 3:18 am
Don't know if it's any better, but an alternative is: -
SELECT DateStarted, RowID, ProductID
FROM (SELECT DateStarted, RowID, ProductID,
MAX(DateStarted) OVER(PARTITION BY ProductID) AS MaxDateStarted
FROM #AllData
) a
WHERE a.DateStarted = a.MaxDateStarted
ORDER BY a.ProductID DESC;
October 17, 2013 at 7:40 am
Thanks Cadavre
That one works really efficiently also. I will keep that code handy.
Greatly appreciate it
Andrew SQLDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply