I have a table with multiple rows for a "Shelf Location" with the same date. example
shelf 0095 has 10 records for 11/12/21 13:50:31.012
shelf 0095 has 15 records for 11/15/21 17:50:31.012
shelf 0145 has 5 records for 11/12/21 13:50:31.012
shelf 0145 has 3 records for 11/19/21 17:50:31.012
I want to select max date for all the records for each shelf.
So i would have 15 records returned for shelf 0095 because the max date it 11/15
and i would have 3 records for shelf 0145 because its max date is 11/19
SELECT
[Shelf Location], date /*, ...*/
FROM (
SELECT *, DENSE_RANK() OVER(PARTITION BY [Shelf Location] ORDER BY date DESC) AS rank_num
FROM dbo.your_table_name
) AS derived1
WHERE rank_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 17, 2021 at 8:00 pm
Without DDL and sample data, this is largely an educated guess, but may get you started
With Ordered as (SELECT Grp = DENSE_RANK() OVER (PARTITION BY ShelfLocation ORDER BY SomeDate desc), *
FROM SomeTable)
SELECT * from Ordered where Grp = 1
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 17, 2021 at 8:01 pm
SELECT
[Shelf Location], date /*, ...*/FROM (
SELECT *, DENSE_RANK() OVER(PARTITION BY [Shelf Location] ORDER BY date DESC) AS rank_num
FROM dbo.your_table_name
) AS derived1
WHERE rank_num = 1
Beat me to it!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply