CODEWITHSUNDEEP
sqlsql-servert-sql
user2146212
user2146212

Reputation: 93

How do I get the most recent records based on as of date?

I have the following table:

+------------------------------------+
|     Number  Name     Date          |
+------------------------------------+
| 1   1050    Name1    2015-01-01    |
| 2   1051    Name2    2015-04-27    |
| 3   1057    Name3    2015-04-27    |
+------------------------------------+

How should I get the most recent records? I've tried something like:

SELECT number, name, MAX(DATE) AS recent_date
FROM Recent_Table
HAVING recent_date < '2015-05-19'
GROUP BY number, name

I'm expecting to get the most recent records but instead I'm getting all three because of my having clause. Is there a way to work around this while still keeping my having? Thanks.

Expected output would be:

  1051    Name2    2015-04-27
  1057    Name3    2015-04-27

Upvotes: 1

Views: 75

Answers (4)

Robert
Robert

Reputation: 25753

Try this

select number, name, date
from Recent_Table
where Date = (SELECT MAX(DATE) AS recent_date
              FROM Recent_Table
              WHERE DATE < '2015-05-19')

there is problem with name column not with having clause. I think you don't need having.

Sql fiddle demo

Upvotes: 1

The Reason
The Reason

Reputation: 7973

Let me guess 

 SELECT number, name
 FROM   Recent_Table
 WHERE recent_date >= (Select Max(Date) FROM Recent_Table)

Upvotes: 0

mohan111
mohan111

Reputation: 8865

SELECT 
number,
name,
DATE AS recent_date
FROM Recent_Table
WHERE DATE = (SELECT MAX(t2.DATE)
                 FROM Recent_Table t2
                 WHERE t2.number = t1.number)

Upvotes: 1

Matt
Matt

Reputation: 15061

SELECT number, name, MAX(DATE) AS recent_date
FROM Recent_Table
WHERE DATE <= MAX(DATE)
GROUP BY number, name

Will return values that are the latest entries

Upvotes: 0

Related Questions