October 5, 2011 at 10:47 am
I have this script (which can be run as is in SSMS) that returns all the IDs, but only the most recent date of each. I had to struggle with it at first, but didn't think of derived tables, which is what I used. I actually have to do this with some pretty large tables over linked servers. I was just wondering if, first of all, I have done this correctly (didn't leave any subtle errors), and secondly if there is an easier, better, or alternative way.
USE [Sandbox]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
DROP TABLE [dbo].[Orders]
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[DayKey] [int] NULL,
[OrderName] [varchar](100) NULL,
[ItemID] [int] NULL,
[OrderDescription] [varchar](100) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Orders]([OrderID], [DayKey], [OrderName], [ItemID], [OrderDescription])
SELECT 101, 20110901, N'OrderName101', 1, N'OrderDesc101' UNION ALL
SELECT 102, 20110901, N'OrderName102', 2, N'OrderDesc102' UNION ALL
SELECT 103, 20110901, N'OrderName103', 3, N'OrderDesc103' UNION ALL
SELECT 104, 20110901, N'OrderName104', 2, N'OrderDesc104' UNION ALL
SELECT 105, 20110901, N'OrderName105', 1, N'OrderDesc105' UNION ALL
SELECT 101, 20110902, N'OrderName101', 1, N'OrderDesc101' UNION ALL
SELECT 102, 20110902, N'OrderName102', 2, N'OrderDesc102' UNION ALL
SELECT 103, 20110902, N'OrderName103', 3, N'OrderDesc103' UNION ALL
SELECT 104, 20110902, N'OrderName104', 2, N'OrderDesc104' UNION ALL
SELECT 105, 20110902, N'OrderName105', 1, N'OrderDesc105' UNION ALL
SELECT 101, 20110903, N'OrderName101', 1, N'OrderDesc101' UNION ALL
SELECT 102, 20110903, N'OrderName102', 2, N'OrderDesc102' UNION ALL
SELECT 103, 20110903, N'OrderName103', 3, N'OrderDesc103' UNION ALL
SELECT 104, 20110903, N'OrderName104', 2, N'OrderDesc104' UNION ALL
SELECT 105, 20110903, N'OrderName105', 1, N'OrderDesc105'
SELECT * FROM Orders
SELECT OrderID
, OrderName
, OrderDescription
FROM
(
SELECT Orders.OrderID
, MAX(Orders.DayKey) DayKey
, Orders.OrderName
, Orders.OrderDescription
FROM Orders
GROUP BY
OrderID
, OrderName
, OrderDescription
) DT
ORDER BY
OrderID
,DayKey
DROP TABLE Orders
GO
October 5, 2011 at 10:49 am
What do you need to do overall? My first impression is that you may be overthinking this...
October 5, 2011 at 11:08 am
I originally was pulling in an import table that had only yesterday's date (or the most recent date per id if there was nothing new yesterday) in it, so it was quite simple, just a straight select all columns. Then I had to change the table source, which now happens to be a historical staging table having many rows (all dates for all ids, but still only want the most recent of each. I may be over thinking it, but this is just a trimmed down example of a complex select and had at first tried other things like JOINs in the WHERE clause, etc., and realizing it was much simpler of a problem than I at first thought, and I got a mental block on it for a while. When I stepped away from it over the weekend, the cloud lifted and it seemed much more simple. I am just asking if I have missed anything obvious and oversimplified too much. It looks to me as if I have it correct. Thanks for looking.
October 5, 2011 at 11:13 am
Depending on wether you need more columns from the historical table you do one of these.
SELECT * FROM (
Select *, ROW_NUMBER() OVER (PARTITION BY Order, Date ORDER BY Date DESC) AS RN from hist
) dtAll
WHERE RN = 1
If you don't need the rest of the columns you can simply do SELECT Order, MAX(Date) FROM WHERE GROUP BY Order
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply