Extracting records with the latest date

Question

Post reply

Extracting records with the latest date

nfpacct

Right there with Babe

Points: 718
More actions
October 24, 2011 at 4:00 pm

#261872

I need assistance in constructing a query that will extract record(s) that have the "latest date".
For example if the latest date in the table is 3/3/11 I would like to know all records.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 1

You could use something like this:

SELECT Fields FROM dbo.MyTable

WHERE MyDate = (SELECT MAX(MyDate) FROM dbo.MyTable);

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

nfpacct Right there with Babe Points: 718 More actions · Answer 2

Sorry Andre,

I omitted one key item in the request. I would like the max date for by a field in my table called vendor.

The suggested query works as it returns the max date for the entire table.

Thanks

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

nfpacct (10/24/2011)
Sorry Andre,
I omitted one key item in the request. I would like the max date for by a field in my table called vendor.
The suggested query works as it returns the max date for the entire table.
Thanks

If you want the latest date for each of your vendors you could group them.

I'm not sure I understood your question correctly but here it goes:

SELECT MAX(MyDate), VendorID FROM dbo.MyTable

GROUP BY VendorID;

If that's not what you want could you please elaborate on your question? 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

So you're looking for the last record by vendor?

Something like this would work:

select

drv.vendor, ca.dateofValue

FROM

(SELECT DISTINCT vendor FROM Tbl) AS drv

CROSS APPLY

(SELECT TOP 1 Vendor, DateOfValue

FROM Tbl

WHERE Vendor = drv.Vendor

ORDER BY DateOfValue DESC

) AS ca

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

nfpacct Right there with Babe Points: 718 More actions · Answer 5

Andre thanks - I believe we are almost there. Here is more detail on the issue. I have a single table.

Vendor# Purchases Date Item#

A $100 1/1/2011 XYZ

A $250 1/5/2011 * ABC

A $500 1/5/2011 * Pens

B $200 2/4/2011 Pencils

B $100 2/6/2011 * paper

C $200 3/1/2011 Pens

C $100 3/11/2011 * paper

C $200 3/11/2011 * Pencils

C $300 3/11/2011 * Paper

The desired output are the rows that have an * (asterisk). As it contains the latest date.

Thanks you

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

nfpacct (10/24/2011)
The desired output are the rows that have an * (asterisk). As it contains the latest date.
Thanks you

Ah, that helped. Try this:

select

drv.vendor, ca.dateofValue

FROM

Tbl as t

JOIN

(SELECT Vendor, Max([Date]) AS MaxDate

FROM Tbl

GROUP BY Vendor

) AS drv

ONt.Vendor = drv.Vendor

AND t.MaxDate = drv.MaxDate

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

Ah, I see.

Try this:

SELECT * FROM dbo.TblTemp AS TMP1

INNER JOIN (SELECT MAX(Date) AS Date, Vendor FROM dbo.TblTemp

GROUP BY Vendor) AS TMP2 ON TMP1.Vendor = TMP2.Vendor AND TMP1.Date = TMP2.Date

ORDER BY TMP1.Vendor;

[EDIT] Ha, Evil Kraig F beat me to it. 😀

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

nfpacct Right there with Babe Points: 718 More actions · Answer 8

Thank you Evil Kraig and Andre the following query worked great: I do have one more request.

I have another query that joins the below listed query which has three columns with the query that you assisted with.

Query 1 - has the following three columns and will have Vendor as the Key field

Vendor, Sum(Billings),Location

*****This is the query that you suggested, which will have the "many" vendor records")****

Query 2

select

drv.vendor, ca.dateofValue

FROM

Tbl as t

JOIN

(SELECT Vendor, Max([Date]) AS MaxDate

FROM Tbl

GROUP BY Vendor

) AS drv

ON t.Vendor = drv.Vendor

AND t.MaxDate = drv.MaxDate

Thank you so much for your help

--------------------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 9

Just to let you know, if you read and follow the guidelines in the first article I reference below in my signature block, you will get better answers faster. It shows you how and what to post to help us help you.