September 17, 2007 at 12:00 pm
key dept date
12345 13 9/16/2007
12346 13 9/14/2007
12358 10 8/16/2007
12365 10 4/17/2007
12398 9 1/1/2007
12600 13 3/1/2007
13000 9 3/1/2007
I need a sql query that will return find the latest date in each dept
and return the key of that row.
select dept, max(date) from log group by dept
the above query returns the dept and date, but I need the key and can't figure out how
to get it.
September 17, 2007 at 12:16 pm
DECLARE @tbl TABLE(KeyID int, Dept int, Dt datetime)
INSERT INTO @tbl
SELECT 12345, 13, '9/16/2007'
UNION
SELECT 12346, 13, '9/14/2007'
UNION
SELECT 12358, 10, '8/16/2007'
UNION
SELECT 12365 , 10, '4/17/2007'
UNION
SELECT 12398, 9, '1/1/2007'
UNION
SELECT 12600, 13, '3/1/2007'
UNION
SELECT 13000, 9, '3/1/2007'
SELECT a.KeyID, a.dept, a.Dt
FROM @tbl a
INNER JOIN
(SELECT dept, MAX(dt) MaxDt FROM @tbl
GROUP BY dept) t ON a.dept = t.[dept] and a.Dt = t.maxDt
September 23, 2007 at 4:42 pm
This sort of situation is where the windowed functions get really handy. In this case, I'm referring to the RANK() function. Rank calculates a 1..N value for sets of rows ('windows') within a resultset based on a specified grouping. If you rank each row, grouped by department and order by date descending, then row #1 for each department will be the latest row, and will include the data you seek.
-- Stealing sample data from the previous poster and adding a row of my own to produce ties 🙂
DECLARE @tbl TABLE(KeyID int, Dept int, Dt datetime)
INSERT @tbl
SELECT 12345, 13, '9/16/2007' UNION ALL
SELECT 12346, 13, '9/14/2007' UNION ALL
SELECT 12358, 10, '8/16/2007' UNION ALL
SELECT 12365 , 10, '4/17/2007' UNION ALL
SELECT 12398, 9, '1/1/2007' UNION ALL
SELECT 12600, 13, '3/1/2007' UNION ALL
SELECT 12700, 13, '9/16/2007' UNION ALL
SELECT 13000, 9, '3/1/2007'
-- The RANK function as written below will return the position of each row by department,
-- ordered by the date column descending. I included the KeyID column in the RANK's ORDER BY
-- clause to break ties on the date column.
;WITH DeptStuff
AS (SELECT KeyID, dept, Dt,
RANK() OVER(PARTITION BY dept ORDER BY Dt DESC, KeyID) AS Pos
FROM @tbl
)
SELECT KeyID, dept, Dt, Pos
FROM DeptStuff
WHERE Pos = 1 -- comment out the WHERE clause to see what the CTE query produces
ORDER BY dept
Eddie Wuerch
MCM: SQL
November 6, 2007 at 2:54 pm
this is very useful, however i would like to adapt it to a subquery. shown below with bad syntax. any hints? thanks
SELECT
[HQID]
, ID
, [Description]
,[ItemLookupCode]
,[SupplierID]
,(select Suppliername from Supplier s where s.ID = SupplierID) as SupplierName
--could be more than one reordernumber per supplier per item:
,(
;with slr as
(select id, itemID, reorderNumber ron, cost c , supplierID,
rank() over(partition by itemID order by dbtimestamp DESC, id) as r
from supplierlist
)
select ron
from slr
where r = 1 and item.supplierID = slr.supplierID
order by itemID
) as reordernumber
FROM [TEVA].[dbo].[Item]
Incorrect syntax near ';'.
Incorrect syntax near ')'.
November 7, 2007 at 12:37 am
CTEs are defined before the SQL statement that uses them, not in it. something like this
;with MyCTE (col1, col2, col3) AS (
SELECT ... FROM aTable
)
SELECT [some fields] FROM MyCTE inner join SomeOtherTable on MyCTE.Col1 = SomeOtherTable.Col1
WHERE SomeConditions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply