How do I get the most recent data from a table?

I'm trying to create a stored procedure from a join of two tables. One table holds a list of containers, and the other table holds the history of the contents of those containers. All I want is to retrive the most recent history for each container. For example, the containers table has the container number and name, and the history table has the

volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.

Normally, I would just create a cursor that holds a list of the containers and some blank fields, and then loop through it, retrieving the most recent record one by one, but I don't know how to do that in Transact-SQL. Also, I thought maybe some SQL wizard out there might know of a way to do it with a simple select statement.

Geoffrey Callaghan

In 2005, the easiest way is to use the ROW_NUMBER() function:

create table container
(
containerId int primary key,
name varchar(10)
)
create table containerHistory
(
containerId int references container(containerId),
containerHistoryDate datetime,
value numeric(4,2),
primary key (containerId, containerHistoryDate)
)
insert into container
select 1,'Fred'
union all
select 2,'Barney'

insert into containerHistory
select 1,'20070101',1.1
union all
select 1,'20070102',1.12
union all
select 1,'20070103',1.1
union all
select 1,'20070104',1.8
union all
select 2,'20070101',1.1

select container.containerId, container.name, containerHistory.value
from container
join (select containerId,
row_number() over (partition by containerId order by containerHistoryDate desc) as rowNum,
value
from containerHistory) as containerHistory
on container.containerId = containerHistory.containerId
and containerHistory.rowNum = 1

Getting the first one (ordered decending) will get you the last one.

|||

Actually, I found an easier way to do it that seems to work.

select container.number, container.name,

(select top 1 qty_meas+qty_added from containerHistory where container.number = containerHistory .container_nbr

order by datetime desc) as balance,

(select top 1 datetime from containerHistory where tank.number = containerHistory .container_nbr

order by datetime desc) as LastReading

from container

This works well and runs fast. Do you see any problems with it?

|||

No, if that works for you, it may be faster/better. It really depends on how many of those subqueries you will need. The Row_number solution is really good for making sure that you get an entire row from a table. However, you want to get a single value from 2 different tables, well your way is probably best.

The row_number trick is going to be the best way to get the last (or first) full row in a set of rows.