MS SQL Select Statement

Databases
1

I am trying to write a select statement on MS SQL to get back the person id and price for the latest date in the table.

Here’s my table:


--------------------------
Date      |Price|PersonID|
--------------------------
2001-01-01|$200 |1
2002-01-01|$300 |1
2001-01-01|$100 |2
2002-01-01|$150 |2
2001-01-01|$200 |3
2002-01-01|$250 |3
2001-01-01|$300 |4
2002-01-01|$350 |4

Here is what I’m trying to get back with my query:


--------------------------
Date      |Price|PersonID|
--------------------------
2002-01-01|$300 |1
2002-01-01|$150 |2
2002-01-01|$250 |3
2002-01-01|$350 |4

I think I need to use TOP or GROUP BY somewhere in the query, but I can’t work out how to do it. Can anyone help me?

2

then you should’ve said “latest date per person” instead of “latest date in the table” :slight_smile:

SELECT t.Date      
     , t.Price
     , t.PersonID
  FROM ( SELECT PersonID
              , MAX(Date) AS latest
           FROM daTable 
         GROUP
             BY PersonID ) AS m
INNER
  JOIN daTable AS t
    ON t.PersonID = m.PersonID
   AND t.Date = m.latest
3

That would work for the example, but what if the table looked something like this (which is possible in my case):


--------------------------
Date      |Price|PersonID|
--------------------------
2001-01-01|$200 |1
2002-01-01|$300 |1
2001-01-01|$100 |2
2002-01-01|$150 |2
2001-01-01|$200 |3
2002-01-01|$250 |3
2004-01-01|$250 |3
2001-01-01|$300 |4
2002-01-01|$325 |4
2003-01-01|$350 |4

I’ll need it to return this:


--------------------------
Date      |Price|PersonID|
--------------------------
2002-01-01|$300 |1
2002-01-01|$150 |2
2004-01-01|$250 |3
2003-01-01|$350 |4
4 
SELECT Date      
     , Price
     , PersonID
  FROM daTable 
 WHERE Date =
       ( SELECT MAX(Date) FROM daTable )