Extract datum without unique identifier 1

[croxford]

Hello,

I have a table of temperature changes over time:

item no. time temp.
1 09:05 44.2
1 09:06 44.4
1 09:08 45.2
1 09:09 45.8
2 09:12 43.0
2 09:13 44.0
2 09:15 46.0
3 09:18 21.0
3 09:19 22.0
3 09:20 23.0

How can I select the last temperature value for each item such that the output would be

item no. time temp.
1 09:09 45.8
2 09:15 46.0
3 09:20 23.0

Have tried sub queries based around MAX(time) but this returns those values that were taken at 23:59

Many thanks in advance

 

[PHV]

You may try something like this:
SELECT A.*
FROM yourTable A INNER JOIN (
SELECT item, MAX(time) LastTime FROM yourTable GROUP BY item
) B ON A.item=B.item AND A.time=B.LastTime

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[croxford]

PHV,

Thank you for your help - very useful

 

[maheswarkv]

Hi..

I am a beginer...so i m not having much idea and want to learn things..so can u please check whether this query works or not?

select a.* from table1 a, table1 b where a.item=b.item and a.time= select max(time) from b
group by itemno;

 

[PHV]

You meant something like this ?
select a.* from table1 a
where a.time=(select max(time) from table1 where item=a.item)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

maheswarkv, you will get a syntax error in every database system except mysql if you use "select star" and then GROUP BY only one column


r937.com | rudy.ca