How to find this data with a fast query

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)

How to find this data with a fast query

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-01-27 : 11:19:14

Hello,

I have a table like this:

tbl_HistoryTankProducts
ID Tank DateTime Product Amount
1 Tank1 13:00 1 5
2 Tank1 13:00 2 5
3 Tank1 14:00 1 6
4 Tank1 14:00 2 6
5 Tank2 16:00 1 4
6 Tank2 16:00 2 4
7 Tank2 17:00 1 3

I need=>

vw_CurrentTankProducts
Tank Product Amount
Tank1 1 6
Tank1 2 6
Tank2 1 3

Please help? The history table is hugh so I would also like it to be fast. It is hard for me not to write this in a stored procedure but a view.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 11:27:56

select tank, product, amount = max(amont)
group by tank, product
order by tank, product

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-01-27 : 11:29:34

Sorry if that was not clear: the amount can also drop in time, so a max(amount) will not do it. The products themselves can also change in time.
I need the product ids and amounts at the max(datetime)s of all tanks.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-27 : 12:00:08

[code]
;WITH HTPOrder
AS
(
SELECT ID, Tank, [DateTime], Product, Amount
,ROW_NUMBER() OVER (PARTITION BY ID, Tank ORDER BY [DateTime] DESC) AS RowNum
FROM tbl_HistoryTankProducts
)
SELECT Tank, Product, Amount
FROM HTPOrder
WHERE RowNum = 1
[/code]

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-01-28 : 03:34:28

quote:
SELECT ID, Tank, [DateTime], Product, Amount
,ROW_NUMBER() OVER (PARTITION BY ID, Tank ORDER BY [DateTime] DESC) AS RowNum
FROM tbl_HistoryTankProducts

All RowNumbers are 1 with this query.

If I use this

SELECT 
ID_EquipProductHist
, Equip_ID
, TimeStamp_PLC
, Product_ID
, Product_Perc
, ROW_NUMBER() OVER (partition by Equip_Id, TimeStamp_PLC ORDER BY TimeStamp_PLC) AS RowNum
FROM tbl_EquipProductHist
where equip_id = 337
order by Equip_Id

I get this

5351	337	2010-06-24 13:58:22.503	9672	7.68287944793701	1
5352	337	2010-06-24 13:58:22.503	9728	13.1422958374023	2
5353	337	2010-06-24 13:58:22.503	9733	6.17041206359863	3
5354	337	2010-06-24 13:58:22.503	9734	9.09217643737793	4
5355	337	2010-06-24 13:58:22.503	9740	63.9122352600098	5
6469	337	2010-07-23 15:15:33.000	9672	7.68287944793701	1
6470	337	2010-07-23 15:15:33.000	9728	13.1422958374023	2
6471	337	2010-07-23 15:15:33.000	9733	6.17041206359863	3
6472	337	2010-07-23 15:15:33.000	9734	9.09217643737793	4
6473	337	2010-07-23 15:15:33.000	9740	63.9122352600098	5

I guess I need it to be this:

5351	337	2010-06-24 13:58:22.503	9672	7.68287944793701	1
5352	337	2010-06-24 13:58:22.503	9728	13.1422958374023	1
5353	337	2010-06-24 13:58:22.503	9733	6.17041206359863	1
5354	337	2010-06-24 13:58:22.503	9734	9.09217643737793	1
5355	337	2010-06-24 13:58:22.503	9740	63.9122352600098	1
6469	337	2010-07-23 15:15:33.000	9672	7.68287944793701	2
6470	337	2010-07-23 15:15:33.000	9728	13.1422958374023	2
6471	337	2010-07-23 15:15:33.000	9733	6.17041206359863	2
6472	337	2010-07-23 15:15:33.000	9734	9.09217643737793	2
6473	337	2010-07-23 15:15:33.000	9740	63.9122352600098	2
Where RowNum = 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-28 : 04:29:39

quote:

Originally posted by djorre
All RowNumbers are 1 with this query.

If I use this

SELECT 
ID_EquipProductHist
, Equip_ID
, TimeStamp_PLC
, Product_ID
, Product_Perc
, ROW_NUMBER() OVER (partition by Equip_Id, TimeStamp_PLC ORDER BY TimeStamp_PLC) AS RowNum
FROM tbl_EquipProductHist
where equip_id = 337
order by Equip_Id

KH
[spoiler]Time is always against us[/spoiler]

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-01-28 : 04:34:46

That gave me all ascending numbers 1 2 3 4 5 6 ....

But I found the solution, need to use Dense_Rank in stead of row_number

SELECT 
ID_EquipProductHist
, Equip_ID
, TimeStamp_PLC
, Product_ID
, Product_Perc
, Dense_RANK() OVER (partition by Equip_Id ORDER BY TimeStamp_PLC desc) AS DenseNum
FROM tbl_EquipProductHist

9533	337	2010-08-26 14:25:05.000	9672	7.68287944793701	1
9534	337	2010-08-26 14:25:05.000	9728	13.1422958374023	1
9535	337	2010-08-26 14:25:05.000	9733	6.17041206359863	1
9536	337	2010-08-26 14:25:05.000	9734	9.09217643737793	1
9537	337	2010-08-26 14:25:05.000	9740	63.9122352600098	1
8891	337	2010-08-17 17:13:34.500	9672	7.68287944793701	2
8892	337	2010-08-17 17:13:34.500	9728	13.1422958374023	
8893	337	2010-08-17 17:13:34.500	9733	6.17041206359863	2
8894	337	2010-08-17 17:13:34.500	9734	9.09217643737793	2
8895	337	2010-08-17 17:13:34.500	9740	63.9122352600098	2

Thanks to all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources