question

a_car11 avatar image
a_car11 asked

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!
sqlsql-server
2 comments
10 |1200 characters needed characters left characters exceeded

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

Do you mean you want to find the latest date in the MYDATE column and then select all the rows with that date?
0 Likes 0 ·
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 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
select * from my_table where MYDATE = (select max(MYDATE) from my_table)
10 |1200 characters needed characters left characters exceeded

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

a_car11 avatar image
a_car11 answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
sravan avatar image
sravan answered
select top 3.* from my_table order by MYDATE asc
10 |1200 characters needed characters left characters exceeded

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

srivivek avatar image
srivivek answered
SELECT * FROM my_table WHERE my_date IN (SELECT max(mydate) FROM my_table GROUP BY account)
10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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