grup by query help needed - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

grup by query help needed

Author

Topic

patbe
Starting Member

9 Posts

Posted - 2004-03-17 : 06:00:12

I have the following query:
[query]
SELECT storage_tank.Storage_Tank_ID, max(customer.Customer_Name) AS Customer, max(pejl_placement.Placement_Name) AS Placement, max(level_logg.Level_Logg_Timestamp) AS Timestamp, max(level_logg.Level) AS Level, max(storage_tank.Full_Volume) AS F_Volume, max(pejl.PEJL_ID) AS Pejl, max(product.Product_Name) AS Product
FROM level_logg
INNER JOIN storage_tank ON level_logg.Storage_Tank_ID = storage_tank.Storage_Tank_ID
INNER JOIN pejl ON storage_tank.Pejl_ID = pejl.Pejl_ID
INNER JOIN pejl_placement ON pejl.Pejl_Placement_ID = pejl_placement.Pejl_Placement_ID
INNER JOIN customer ON pejl.Customer_Number = customer.Customer_Number
INNER JOIN product ON storage_tank.Product_ID = product.Product_ID
GROUP BY storage_tank.Storage_Tank_ID
[/query]

but now I need the level to be selected based on the timestamp value, any ideas?

.p

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-17 : 06:25:55

SELECT storage_tank.Storage_Tank_ID, max(customer.Customer_Name) AS Customer, max(pejl_placement.Placement_Name) AS Placement, max(level_logg.Level_Logg_Timestamp) AS Timestamp, max(level_logg.Level) AS Level, max(storage_tank.Full_Volume) AS F_Volume, max(pejl.PEJL_ID) AS Pejl, max(product.Product_Name) AS Product
FROM level_logg
INNER JOIN storage_tank ON level_logg.Storage_Tank_ID = storage_tank.Storage_Tank_ID
INNER JOIN pejl ON storage_tank.Pejl_ID = pejl.Pejl_ID
INNER JOIN pejl_placement ON pejl.Pejl_Placement_ID = pejl_placement.Pejl_Placement_ID
INNER JOIN customer ON pejl.Customer_Number = customer.Customer_Number
INNER JOIN product ON storage_tank.Product_ID = product.Product_ID
GROUP BY storage_tank.Storage_Tank_ID
HAVING max(level_logg.Level_Logg_Timestamp) > ?

Duane.

patbe
Starting Member

9 Posts

Posted - 2004-03-17 : 07:10:24

I dont know any value to but in a HAVING clause, maybe I need to be more clear, I want to get the lastest timestamp and get the level that corresponds to that timestamp and then group it by ID

.p

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-17 : 07:24:09

How about this then ?

--**********************************
SELECT storage_tank.Storage_Tank_ID, max(customer.Customer_Name) AS Customer, max(pejl_placement.Placement_Name) AS Placement, level_logg.Level_Logg_Timestamp AS Timestamp, max(level_logg.Level) AS Level, max(storage_tank.Full_Volume) AS F_Volume, max(pejl.PEJL_ID) AS Pejl, max(product.Product_Name) AS Product
FROM level_logg
INNER JOIN storage_tank ON level_logg.Storage_Tank_ID = storage_tank.Storage_Tank_ID
INNER JOIN pejl ON storage_tank.Pejl_ID = pejl.Pejl_ID
INNER JOIN pejl_placement ON pejl.Pejl_Placement_ID = pejl_placement.Pejl_Placement_ID
INNER JOIN customer ON pejl.Customer_Number = customer.Customer_Number
INNER JOIN product ON storage_tank.Product_ID = product.Product_ID
WHERE level_logg.Level_Logg_Timestamp = (SELECT MAX(a.level_logg_TimeStamp) FROM level_logg a where a.Storage_Tank_ID = level_logg.Storage_Tank_ID)
GROUP BY storage_tank.Storage_Tank_ID, level_logg.Level_Logg_Timestamp

--***************************************************

Duane.

patbe
Starting Member

9 Posts

Posted - 2004-03-17 : 07:54:01

NICE!!! you're the man, thank you very much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources