Using subqueries to get latest value per type? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using subqueries to get latest value per type?

Author

Topic

skimmilkman
Starting Member

1 Post

Posted - 2008-05-07 : 23:27:53

Hopefully someone can suggest a better solution than what I'm currently hobbling along with.

Basically, I've got a table that gets rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest values of each particular item.

I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or some FANCY item.

But for the time being, I've got a less-elegant query that returns the correct data, just using subqueries.

Here's the T-SQL to run my scenario:


DECLARE @actionHistoryTable TABLE (
	itemID int 
	,actionType int 
	,actionValue nvarchar(50) NULL
	,actionTime datetime )
INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')
INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008')

-- JUST DISPLAY THE TABLE FOR EXAMPLE
SELECT * FROM @actionHistoryTable

-- THIS ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMID
SELECT aht.itemID
	,( SELECT TOP 1 aht2.actionValue 
		FROM @actionHistoryTable aht2
		WHERE aht.itemID = aht2.itemID 
			AND aht2.actionType = '1'
		ORDER BY aht2.actionTime DESC ) as 'latest type 1 value'
	,( SELECT TOP 1 aht2.actionValue
		FROM @actionHistoryTable aht2
		WHERE aht.itemID = aht2.itemID
			AND aht2.actionType = '2'
		ORDER BY aht2.actionTime DESC ) as 'latest type 2 value'
	,( SELECT TOP 1 aht2.actionValue
		FROM @actionHistoryTable aht2
		WHERE aht.itemID = aht2.itemID
			AND aht2.actionType = '3'
		ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'
FROM @actionHistoryTable aht
GROUP BY aht.itemID

Is there a better way?
-Steve

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-05-08 : 01:04:42

Try the following:


DECLARE @actionHistoryTable TABLE (
	itemID int 
	,actionType int 
	,actionValue nvarchar(50) NULL
	,actionTime datetime )
INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')
INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008')

--Display my pivot Results

SELECT *
FROM 
(	
   SELECT Grp_AHT.ItemID
	, AHT.ActionValue
	, CASE Grp_AHT.ActionType 
	WHEN 1 THEN 'latest Type 1 Value' 
	WHEN 2 THEN  'latest Type 2 Value' 
	WHEN 3 THEN 'Latest type 3 Value' END As [Latest Type]
   FROM (SELECT ItemID, ActionType, Max(ActionTime)MaxActionTime 
	FROM @actionHistoryTable AHT GROUP BY ItemID, ActionType) AS Grp_AHT
    INNER JOIN @actionHistoryTable AHT 
      ON AHT.ItemID = Grp_AHT.ItemID 
	AND AHT.ActionType = Grp_AHT.ActionType 
	AND AHT.ActionTime = Grp_AHT.MaxActionTime
) As MyResults

PIVOT 

(
   Max(ActionValue) 
   FOR [Latest Type] IN 
     ([latest Type 1 Value]
      ,[latest Type 2 Value]
      ,[Latest type 3 Value])
) As PvtResult

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-08 : 02:49:15

OMG! Respect to skimmilkman for providing sample data AND expected output in his very first post! I wish there were more people like you man...

I'm pretty sure peso will have a whiz at this when he get's here...he'll probably have it in 2 or 3 lines of code

--
Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-08 : 03:36:42

Here's my take on it

SELECT 
  dt.ItemID,
  'latest type 1 value' = MAX(CASE WHEN dt.ActionType = 1 THEN ActionValue ELSE NULL END),
  'latest type 2 value' = MAX(CASE WHEN dt.ActionType = 2 THEN ActionValue ELSE NULL END),
  'latest type 3 value' = MAX(CASE WHEN dt.ActionType = 3 THEN ActionValue ELSE NULL END)
FROM (
  SELECT 
    ItemID, 
    ActionType,
    MAX(ActionTime) AS ActionTime
  FROM @actionHistoryTable
  GROUP BY ItemID, ActionType
  ) AS dt
  INNER JOIN @actionHistoryTable a
    ON a.ItemID = dt.ItemID
      AND a.ActionType = dt.ActionType
      AND a.ActionTime = dt.ActionTime
GROUP BY dt.ItemID
ORDER BY dt.ItemID

--
Lumbago

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-05-08 : 08:28:10

Well I would like to second what lumbago mentioned about providing data and sample output. Providing this information is always a plus and allows for faster responses.

Dallr

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-08 : 09:35:50

This is my solution using the new row_number function of sql2005

select itemid,
max(case when actiontype=1 then actionvalue end)as 'latest type 1 value',
max(case when actiontype=2 then actionvalue end)as 'latest type 2 value',
max(case when actiontype=3 then actionvalue end)as 'latest type 3 value'
from
(
select itemid,actiontype,actionvalue,actiontime,row_number()
over (partition by itemid,actiontype order by actiontime desc)as rowid
from @actionHistoryTable
)t
where rowid=1 group by itemid

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-08 : 09:50:00

Cool! Mine seems to have the shortest execution plan (dunno if that counts

) but ayamas performs best, at least acording to my records here.

Query cost relative to the batch:
skimmilkman 43%
Dallr 19%
Lumbago 19%
ayamas 11%
(inserts 7%)

--
Lumbago

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-08 : 10:09:30

quote:
Originally posted by Lumbago

Cool! Mine seems to have the shortest execution plan (dunno if that counts ) but ayamas performs best, at least acording to my records here.

Query cost relative to the batch:
skimmilkman 43%
Dallr 19%
Lumbago 19%
ayamas 11%
(inserts 7%)

-
Lumbago

Good observation.I am honuored.

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-05-08 : 14:36:09

This should help our performance a bit.


DECLARE @actionHistoryTable TABLE (
	itemID int 
	,actionType int 
	,actionValue nvarchar(50) NULL
	,actionTime datetime )
INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')
INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008')

---- THIS ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMID
SELECT aht.itemID
	,( SELECT TOP 1 aht2.actionValue 
		FROM @actionHistoryTable aht2
		WHERE aht.itemID = aht2.itemID 
			AND aht2.actionType = '1'
		ORDER BY aht2.actionTime DESC ) as 'latest type 1 value'
	,( SELECT TOP 1 aht2.actionValue
		FROM @actionHistoryTable aht2
		WHERE aht.itemID = aht2.itemID
			AND aht2.actionType = '2'
		ORDER BY aht2.actionTime DESC ) as 'latest type 2 value'
	,( SELECT TOP 1 aht2.actionValue
		FROM @actionHistoryTable aht2
		WHERE aht.itemID = aht2.itemID
			AND aht2.actionType = '3'
		ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'
FROM @actionHistoryTable aht
GROUP BY aht.itemID;

--dane 
SELECT *
FROM 
(	
   SELECT Grp_AHT.ItemID
	, AHT.ActionValue
	, CASE Grp_AHT.ActionType 
	WHEN 1 THEN 'latest Type 1 Value' 
	WHEN 2 THEN  'latest Type 2 Value' 
	WHEN 3 THEN 'Latest type 3 Value' END As [Latest Type]
   FROM (SELECT ItemID, ActionType, Max(COALESCE (ActionTime,0))MaxActionTime 
	FROM @actionHistoryTable AHT GROUP BY ItemID, ActionType) AS Grp_AHT
    INNER JOIN @actionHistoryTable AHT 
      ON AHT.ItemID = Grp_AHT.ItemID 
	AND AHT.ActionType = Grp_AHT.ActionType 
	AND AHT.ActionTime = Grp_AHT.MaxActionTime
) As MyResults

PIVOT 

(
   Max(ActionValue) 
   FOR [Latest Type] IN 
     ([latest Type 1 Value]
      ,[latest Type 2 Value]
      ,[Latest type 3 Value])
) As PvtResult;


--
SELECT 
  dt.ItemID,
  'latest type 1 value' = MAX(CASE WHEN dt.ActionType = 1 THEN ActionValue ELSE NULL END),
  'latest type 2 value' = MAX(CASE WHEN dt.ActionType = 2 THEN ActionValue ELSE NULL END),
  'latest type 3 value' = MAX(CASE WHEN dt.ActionType = 3 THEN ActionValue ELSE NULL END)
FROM (
  SELECT 
    ItemID, 
    ActionType,
    MAX(COALESCE(ActionTime,0)) AS ActionTime
  FROM @actionHistoryTable
  GROUP BY ItemID, ActionType
  ) AS dt
  INNER JOIN @actionHistoryTable a
    ON a.ItemID = dt.ItemID
      AND a.ActionType = dt.ActionType
      AND a.ActionTime = dt.ActionTime
GROUP BY dt.ItemID
ORDER BY dt.ItemID;


 --This is my solution using the new row_number function of sql2005


select itemid,
max(case when actiontype=1 then actionvalue end)as 'latest type 1 value',
max(case when actiontype=2 then actionvalue end)as 'latest type 2 value',
max(case when actiontype=3 then actionvalue end)as 'latest type 3 value'
from
(
select itemid,actiontype,actionvalue,actiontime,row_number()
over (partition by itemid,actiontype order by actiontime desc)as rowid
from @actionHistoryTable
)t
where rowid=1 group by itemid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources