return most recent records from table 3

[Amesville]

Hi, quick question, hopefully a simple answer...

I am tasked with returning a set of records from a table in which there can be several records for the same item with differing timestamps. I need to get the most recent record for each item from the table. I'm pretty sure a SELECT DISTINCT won't do the job here since by virtue of the timestamp, the records are unique. Likewise, I don't think TOP will work either. How can I get the data I'm looking for here? Thanks.

Craig

 

[Borvik]

What you'll want to use is a CTE expression. See the following as an example:

declare @T table (ID int, IdStamp datetime, Qty int)
insert into @T values
(1, '1/1/2015', 100),
(1, '1/2/2015', 200),
(3, '1/3/2015', 300),
(3, '1/4/2015', 98),
(3, '1/5/2015', 198),
(5, '1/6/2015', 175),
(6, '1/7/2015', 275),
(6, '1/8/2015', 375),
(6, '1/9/2015', 215)

;with cte as
(
  select
    ID,
    IdStamp,
    Qty,
    rank() over(partition by ID order by IdStamp DESC) as rn
  from @T
)  
select ID, IdStamp, Qty
from cte
where rn = 1

 

[TamarGranor]

If you have the timestamp in each record, you just need to do something like this:

SELECT field1, field2, ... 
  FROM YourTable 
    JOIN (SELECT ItemID, MAX(Timestamp) MaxTime
           FROM YourTable 
           GROUP BY ItemID) MaxItem
      ON YourTable.ItemID = MaxItem.ItemID
      AND YourTable.TimeStamp = MaxItem.MaxTime

Tamar

 

[Amesville]

Thanks Tamar, this sounds workable. I'll give it a try!

 

[Amesville]

Well, Tamar's answer proved to work very well, thanks. But now I have a new wrinkle on this that has me stumped...

I now need to add in a field from another table to the primary select statement. Ordinarily I would just set up all the fields with a tablename qualifier and then do a simple statement like X.Field = Y.Field in the where clause. But is I try to do that here it doesn't work, primarily because of the Join statement that restricts the record selection.

How can I get around this?

Thanks

Craig

 

[TamarGranor]

Use the JOIN syntax to add the new table.

SELECT field1, field2, ... , OtherTable.FieldName
  FROM YourTable 
    JOIN (SELECT ItemID, MAX(Timestamp) MaxTime
           FROM YourTable 
           GROUP BY ItemID) MaxItem
      ON YourTable.ItemID = MaxItem.ItemID
      AND YourTable.TimeStamp = MaxItem.MaxTime 
    JOIN OtherTable 
      ON YourTable.KeyField = OtherTable.KeyField

Tamar

 

[Borvik]

Tamar's may be working but I'll try to update my CTE solution to match.

;with cte as
(
  select
    field1,
    field2,
    ...,
    OtherTable.FieldName
    rank() over(partition by ID order by Timestamp DESC) as RankNumber
  from YourTable
  join OtherTable
    on YourTable.KeyField = OtherTable.KeyField
)  
select field1, field2, ..., FieldName
from cte
where RankNumber = 1

You could also just as easily make that last select something like this (assuming you don't care about receiving/viewing the RankNumber field:

select field1, field2, ..., FieldName from cte where RankNumber = 1

While I tested the code in my first post - this hasn't been tested yet.

 

[Amesville]

Thank you both very much! I will try it out first thing on Monday.

 

[Borvik]

Just noticed an mistake in my last post - that second code block should read something like:

select * from cte where RankNumber = 1

*Note* You must run both statements (the "with cte as" and "select ... from cte" statements) at the same time, as the second is dependent on the first.

 

[TamarGranor]

Borvik-just a couple of points. First, as I'm sure you know, in a case like this, a CTE and a derived table are essentially identical.

Second, while I love OVER (and in fact, recently finished writing a 5-part series about using OVER), it's just not necessary in this case where he already has a field that provides the ordering and he needs only a single aggregation.

I could go either way on the CTE vs. derived table here, but I think selected the MAX timestamp and using that results in a more readable (and thus, easier to maintain) query.

Tamar

 

[Borvik]

Ah - but the big difference is your derived table requires an unique or primary key to work properly. While it is good practice to have those type of columns (and any new table I create does have them), I have run into some instances where I just couldn't run that type of query because I couldn't join like you showed. CTEs avoids that problem.

 

[gmmastros]

If we're reviewing code....

There is a problem with both solutions presented here. Specifically, if there are multiple rows that match the max date, both queries will return multiple rows for that item.

I didn't mention anything earlier because Amesville (the OP) never mentioned that there may be duplicates, and he didn't say that duplicates on the max would be a problem.

For this type of query, I prefer the method suggested by Borvik because it is super easy to modify the code to accommodate the (possibly fictitious requirement) of just one row per id. In fact, changing the Rank keyword to Row_Number() would handle it.

declare @T table (ID int, IdStamp datetime, Qty int)
insert into @T values(1, '1/1/2015', 100)
insert into @T values(1, '1/2/2015', 200)
insert into @T values(3, '1/3/2015', 300)
insert into @T values(3, '1/4/2015', 98)
insert into @T values(3, '1/5/2015', 198)
insert into @T values(5, '1/6/2015', 175)
insert into @T values(6, '1/7/2015', 275)
insert into @T values(6, '1/8/2015', 375)
insert into @T values(6, '1/9/2015', 215)

insert into @T values(6, '1/9/2015', 400)


;with cte as
(
  select
    ID,
    IdStamp,
    Qty,
    rank() over(partition by ID order by IdStamp DESC) as rn
  from @T
)  
select ID, IdStamp, Qty
from cte
where rn = 1 

SELECT YourTable.Id, YourTable.idStamp, Qty
  FROM @T YourTable 
    JOIN (SELECT Id, MAX(IdStamp) IdStamp
           FROM @T YourTable 
           GROUP BY Id) MaxItem
      ON YourTable.Id = MaxItem.Id
      AND YourTable.IdStamp = MaxItem.IdStamp 

;with cte as
(
  select
    ID,
    IdStamp,
    Qty,
    Row_Number() over(partition by ID order by IdStamp DESC) as rn
  from @T
)  
select ID, IdStamp, Qty
from cte
where rn = 1

By using Row_Number, there is just one row in the output for each id (the column that is partitioned by). Since there are multiple matches, the last query (using Row_Number), returns just one row for id, and is relatively random which row.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Borvik]

Awesome George - that is indeed the one I was attempting to do, hopefully google searches will now make it easier to find your example.

 

[Amesville]

Thanks very much you guys, I will make a note of studying what you've written here as I'm sure it will come up again.

To clarify, the data reflects scans of cartons on a conveyor. They are scanned and weighed at the same time. If the carton weight is out of spec (something is missing) it's diverted and has to be re-scanned after being examined. The last scan (accepted, in theory) is the one they are looking to capture. So it's very unlikely there would be two scans with the same time.

Craig

 

[gmmastros]

So it's very unlikely there would be two scans with the same time.

This is exactly why I didn't post anything sooner. When writing SQL, it's important to know your data, which you apparently do Amesville.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom