CODEWITHSUNDEEP
sqlsql-servert-sql
Kris Edwards
Kris Edwards

Reputation: 35

Select all from max date

Good morning,

I am writing a SQL query for the latest metal prices with the latest date they were put into the database. Example table below:

ID    Date Created
1     01/01/01 01:01
2     01/01/01 01:02
3     01/01/01 01:03
4     01/01/01 01:04
1     02/01/01 01:01
2     02/01/01 01:02

So from this I want the following result:

ID    Date Created
1     02/01/01 01:01
2     02/01/01 01:02

When I run the below query it is just giving me the last one entered into the date base so from the above example it would be ID 2 DateCreated 02/01/01 01:02. The query I am using is below:

SELECT mp.MetalSourceID, ROUND(mp.PriceInPounds,2), 
mp.UnitPrice, mp.HighUnitPrice, mp.PreviousUnitPrice, 
mp.PreviousHighUnitPrice, ms.MetalSourceName, 
ms.UnitBasis, cu.Currency 
FROM tblMetalPrice  AS mp
INNER JOIN tblMetalSource AS ms
ON tblMetalPrice.MetalSourceID = tblMetalSource.MetalSourceID 
INNER JOIN tblCurrency AS cu
ON tblMetalSource.CurrencyID = tblCurrency.CurrencyID  
WHERE DateCreated = (SELECT MAX (DateCreated) FROM tblMetalPrice)
GROUP BY mp.MetalSourceID;

Could anyone please help its driving me crazy not knowing and my brain is dead this friday morning.

Thanks

Upvotes: 0

Views: 82

Answers (3)

saikumarm
saikumarm

Reputation: 1575

with maxDates as (select max(datecreated) maxd, ids grp , count(1) members from s_tableA group by ids having count(1) > 1) select ids, datecreated from s_tableA,maxDates where maxd = datecreated and ids = grp;

this query will give your desired result. Correlated sub queries tend to consume lot of processing time, because for each row of the outer query it has to process all the rows in the inner query.

Upvotes: 0

mlinth
mlinth

Reputation: 3118

You can join on a subquery, and I don't think you'll need the group by, or indeed the where clause (because that's handled by the join).

SELECT mp.MetalSourceID, 
       ROUND(mp.PriceInPounds,2), 
       mp.UnitPrice, 
       mp.HighUnitPrice, 
       mp.PreviousUnitPrice, 
       mp.PreviousHighUnitPrice, 
       ms.MetalSourceName, 
       ms.UnitBasis, 
       cu.Currency 
FROM tblMetalPrice  AS mp
INNER JOIN tblMetalSource AS ms
    ON tblMetalPrice.MetalSourceID = tblMetalSource.MetalSourceID 
INNER JOIN tblCurrency AS cu
    ON tblMetalSource.CurrencyID = tblCurrency.CurrencyID  
INNER JOIN (SELECT ID,MAX(DateCreated) AS maxdate FROM tblMetalPrice GROUP BY ID) AS md
    ON md.ID = mp.ID 
    AND md.maxdate = mp.DateCreated

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use a correlated subquery for the where clause:

WHERE DateCreated = (SELECT MAX(DateCreated) FROM tblMetalPrice mp2 WHERE mp2.id = mp.id)

Upvotes: 1

Related Questions