Select only the newest Record

Question

question

a_car11 asked Mar 22, '16

Select only the newest Record

Hi, I am trying to select ONLY the newest records from this table in SQL Server. My goal is to have only these records for the result:

00003334	6TH2AXXFN	2016-03-22 00:00:00
00008889	111Z4HS1D	2016-03-22 00:00:00
00001119	6XVZR9990	2016-03-22 00:00:00

Using this SQL but is pulling more than what I need:

 Select *
    from my_table
    inner join 
    (
        Select max(mydate) as LatestDate, account
        from my_table
        Group by account
    ) TempMax 
    on my_table.my_date = TempMax.LatestDate

Data Sample:

ACCOUNT         REF_CODE            MYDATE
00006034	730ZR8VDK	2016-01-29 00:00:00
00008889	111Z4HS1D	2016-03-22 00:00:00
00006035	6W83ZL36V	2016-01-29 00:00:00
00006036	70C05G189	2016-01-29 00:00:00
00006037	6YDMZW6HX	2016-01-29 00:00:00
00006038	71NC7RHW1	2016-01-29 00:00:00
00003334	6TH2AXXFN	2016-03-22 00:00:00
00006039	71ZJMFN31	2016-01-29 00:00:00
00006040	6TH2SL7FN	2016-02-29 00:00:00
00006041	762Z4HS1D	2016-03-09 00:00:00
00006042	6XVZR7T4B	2016-03-09 00:00:00
00006043	71DJBY3P6	2016-03-09 00:00:00
00006044	6Y80TQTNT	2016-03-09 00:00:00
00006045	6Z8QY1KKZ	2016-03-09 00:00:00
00006046	7297J0H8F	2016-03-09 00:00:00
00001119	6XVZR9990	2016-03-22 00:00:00
00006047	6Z6WPZJ89	2016-03-09 00:00:00


Thanks for looking!

sql sql-server

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush commented · Mar 22, 2016 at 07:33 PM

Do you mean you want to find the latest date in the MYDATE column and then select all the rows with that date?

0 ·

Grant Fritchey ♦♦ commented · Apr 28, 2016 at 08:49 AM

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question. You can mark your own answer too.

0 ·

Answer 1 · 2016-03-22T20:57:24Z

Wilfred van Dijk answered Mar 22, '16

select * from my_table where MYDATE = (select max(MYDATE) from my_table)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2016-03-22T20:57:27Z

a_car11 answered Mar 22, '16

I am working with this one and it seems fine now, unless someone can improve it:

SELECT *
FROM table
WHERE 
my_date IN (
    SELECT MAX( my_date )
      FROM table
  )
ORDER BY account ASC , my_date DESC

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk commented · May 10, 2016 at 05:08 PM

order by my_date desc is useless, because the MAX() function returns only one row. So every row from [table] has the same [my_date]

0 ·

Answer 3 · 2016-05-10T14:16:15Z

sravan answered May 10, '16

select top 3.* from my_table order by MYDATE asc

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2016-05-10T18:14:32Z

srivivek answered May 10, '16

SELECT * FROM my_table WHERE my_date IN (SELECT max(mydate) FROM my_table GROUP BY account)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question