SELECT all rows with max field1 for field 2

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)

SELECT all rows with max field1 for field 2

Author

Topic

tomlemes
Starting Member

2 Posts

Posted - 2008-03-19 : 10:15:00

I have a Log table which records items found on library shelves during a shelf scanning process. Each time a shelf is scanned all the items found are recorded in the log table and the ScanCount value is incremented (per shelf). This means that if I get the records with the highest available ScanCount value for each ShelfID, then I would have the most up to date picture of my library.

From this sample data I would like to return the latest rows (max scan count) for all shelves (which would be LogID: 7,8 (shelf A) 3,4 (Shelf B) 5,6 (Shelf C):


LogID    ShelfID    ScanCount    ItemName
1           A             1         Dave
2           A             1         Alan
3           B             1         Mike
4           B             1         Andy
5           C             1         Mary
6           C             1         Mark
7           A             2         Sam
8           A             2         Will

I'm not sure how to do this - presumably with an inner select? Could someone help? thanks

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-19 : 10:22:49

try this,

DECLARE @T TABLE (LogID INT, ShelfID VARCHAR(50), ScanCount INT, ItemName VARCHAR(50))

INSERT INTO @T
SELECT 1,'A',1, 'Dave' UNION ALL
SELECT 2,'A',1, 'Alan' UNION ALL
SELECT 3,'B',1, 'Mike' UNION ALL
SELECT 4,'B',1, 'Andy' UNION ALL
SELECT 5,'C',1, 'Mary' UNION ALL
SELECT 6,'C',1, 'Mark' UNION ALL
SELECT 7,'A',2, 'Sam' UNION ALL
SELECT 8,'A',2, 'Will'

SELECT T1.*
FROM @T T1
INNER JOIN (SELECT MAX(ScanCount) AS 'ScanCount', ShelfID
FROM @T
GROUP BY ShelfID) T2 ON T1.ScanCount = T2.ScanCount AND T1.ShelfID = T2.ShelfID

tomlemes
Starting Member

2 Posts

Posted - 2008-03-19 : 12:05:22

Thanks Peter, that did it perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources