CODEWITHSUNDEEP
sql-server
milo2011
milo2011

Reputation: 339

Get value with MAX(date) from two table

I have two tables.

MainTable:
MainID | LastValue | LastReadingDate
1      | 234       | 01.01.2012
2      | 534       | 03.02.2012

Readings:
MainID | ValueRead | ReadingDate
1      | 123       | 03.02.2012
1      | 488       | 04.03.2012
2      | 324       | 03.02.2012
2      | 683       | 05.04.2012

I want to get 

SELECT MainTable.MainID, MainTable.LastValue, MainTable.LastReadingDate, (SELECT ValueRead, MAX(ReadingDate) 
FROM Readings 
WHERE Readings.MainID=MainTable.MainID ORDER BY ValueRead)

In other words, I want to get the current LastValue and LastReadingDate from MainTable along side the ValueRead with the most recent ReadingDate from Readings.

Upvotes: 0

Views: 1860

Answers (3)

Diego
Diego

Reputation: 36136

try this:

select M.LastValue, M.LastReadingDate, 
       (select top 1 ValueRead from Readings where MainID=M.MainID order by ReadingDate desc)
from MainTable M

Upvotes: 0

cairnz
cairnz

Reputation: 3957

Here is a query you could use. It'll show all MainTable entries, including those that doesn't have a "Reading" entry yet. Change the LEFT JOIN to an INNER JOIN if you don't want it like that.

WITH LastReads AS (

SELECT ROW_NUMBER() OVER (PARTITION BY MainID ORDER BY ReadingDate DESC) AS ReadingNumber, 
MainID, 
ValueRead, 
ReadingDate

FROM Readings

)

SELECT M.MainID, M.LastValue, M.LastReadingDate, R.ValueRead, R.ReadingDate
FROM MainTable M
LEFT OUTER JOIN LastReads R
ON M.MainID = R.MainID
AND R.ReadingNumber = 1 -- Last reading, use 2 or 3 to get the 2nd newest, 3rd newest, etc.

SQLFiddle-link: http://sqlfiddle.com/#!3/16c68/3

Another link with N number of readings per mainid: http://sqlfiddle.com/#!3/16c68/4

Upvotes: 3

Chetter Hummin
Chetter Hummin

Reputation: 6817

Not tried this myself, but here goes. Please try

select max(r.readingdate), max(t.lastvalue), max(t.lastreadingdate) 
from readings r inner join
( select MainID, LastValue, LastReadingDate 
   from MainTable m 
   where LastReadingDate = 
      (select max(minner.LastReadingDate)
       from MainTable minner
       where minner.MainID = m.MainID
      )
) t
on (r.mainid = t.mainid)

Upvotes: 0

Related Questions