Help query to select latest date row

Question

Post reply

Help query to select latest date row

imranazam

SSC Enthusiast

Points: 189
More actions
February 28, 2021 at 11:24 pm

#3851428

Hi Guys
I have the data below ( this is an example) and i am trying to create a query to pull the data to show the latest status of the city, i am struggling to do this
so from the data above (this is an example , new daily data is added every day) i need a query to show the latest row for each city only , so the below should be the rest
Can anyone help me with this?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Use the MAX function on the date, and group by the columns that uniquely identify the city.

Put that in a subquery, and join the table to the subquery on all the columns and the MAX date value.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

imranazam SSC Enthusiast Points: 189 More actions · Answer 2

Thank yo for this max but i don't fully understand what you mean?

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 3

SELECT[Date]
, city
, area
, Status
FROM
(
SELECT[T_City_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY city
ORDER BY [Date] DESC
)
, [Date]
, city
, area
, Status
FROMMyTable
) AS T
WHERET.T_City_RowNumber = 1

EDIT : I had to remove the square brackets around the column names because the formatter ate them ... so you will need to put them back for column names like [Date]

EDIT2: OK, managed to put [Date] back without formatter gobbling the others. Dunno if any other columns are reserved words ... but if so they will need square brackets

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

Michaels suggestion was probably this

SELECT[Date] = MAX([Date])
, city
, area
, Status
FROMMyTable
GROUP BY city
, area
, Status

which is a lot more skinny, provided that your code isn't more complicated than the example you have provided.

Michael L John One Orange Chip Points: 27055 More actions · Answer 5

Kristen-173977 wrote:

Michaels suggestion was probably this
which is a lot more skinny, provided that your code isn't more complicated than the example you have provided.

They never really provided an example.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

GaryV Hall of Fame Points: 3307 More actions · Answer 6

Sorry, replied to wrong post - will try again

This reply was modified 3 years, 8 months ago by GaryV.
This reply was modified 3 years, 8 months ago by GaryV. Reason: Replied to wrong post

GaryV Hall of Fame Points: 3307 More actions · Answer 7

Kristen-173977 wrote:

Michaels suggestion was probably this
SELECT[Date] = MAX([Date])
, city
, area
, Status
FROMMyTable
GROUP BY city
, area
, Status
which is a lot more skinny, provided that your code isn't more complicated than the example you have provided.

Doesn't this give the latest date for each city, area and status? It sounds like the OP only wants the latest date for each city. Which is what your first code would do.

(I'm assuming area, code and city are somehow linked, which means the data isn't really normalized properly, but perhaps this data sample comes from a query of joined tables. If London can move from South to North or from A3 to A2 in some data rows, we may have a problem.)