Skip to main content
O
Olivia_Crazy_Horse
Known Participant
May 23, 2008
Question

SQL Question

  • May 23, 2008
  • 2 replies
  • 295 views
I have a table that contains this data/layout :

San Francisco PO-12345 5/10/2008
San Francisco PO-98734 4/13/2008
Seattle PO-38484 4/28/2008
San Diego PO-98149 5/23/2008
San Diego PO-52221 5/20/208
San Diego PO-00973 5/16/2008

What I need to do is to find the last record (order by date desc) for each city. For example, I need to get this
data into a view to be used later on :
San Francisco PO-12345 5/10/2008
Seattle PO-38484 4/28/2008
San Diego PO-98149 5/23/2008

I can find one record at a time using select top 1 and order by date desc, but how do I find the last record for all the cities ?
    This topic has been closed for replies.

    2 replies

    A
    Anonymous
    May 23, 2008
    I had a similar problem where I wanted to show only the latest version of content listed in the database - I used the query below (which I have modified somewhat to match the data you seem to be using.)

    Hope it helps!

    SELECT *
    FROM table t
    INNER JOIN (
    SELECT city, max(date) as maxDate
    FROM table
    GROUP BY city
    ) m
    ON t.city = m.city
    AND t.date = m.maxDate
    ORDER BY t.date DESC
    D
    Dan_Bracuk
    Inspiring
    May 23, 2008
    select a, b, max(c)
    from whereever
    where whatever
    group by a, b
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices