query the rest but not the max id

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)

query the rest but not the max id

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-04-07 : 14:11:57

Hello All,

I have to query all the rows for a detail table except the maximum line item number. Dump this select in another table and then delete all of these rows from the original table. Keep only one row having max line item number.

Any idea on how to achieve this.

Thanks in advace
-S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-07 : 14:13:48

You'll need to show us DDL for the tables, sample data, and exactly what should be in your tables after the T-SQL code runs using that sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-04-07 : 14:25:54

Cleanup PPKManufactDetail table -
RandID ManufactID LineItemID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 1
7 4 1

And now I wanted to copy the only data where lineitemid is max for group by RandID and ManufactID to another table and then delete the same from this table.

So this table would end up having these records.

RandID ManufactID LineItemID
3 1 3
5 2 2
6 3 1
7 4 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 15:08:11

SELECT RandID, ManufactID, LineItemID
FROM (
SELECT RandID, ManufactID, LineItemID, ROW_NUMBER() OVER (PARTITION BY ManufactID ORDER BY LineItemID DESC) AS recID
FROM PPKManufactDetail) AS d
WHERE recID = 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources