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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply