Help me with this single table

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

Transact-SQL (2008)

Help me with this single table

Author

Topic

crazycat503
Starting Member

16 Posts

Posted - 2012-01-27 : 04:16:21

I have a single table with 2 fields: id,mid. Id is autonumber while mid is group code.

sample data is

1 3
2 3
3 3
4 3
5 3
6 4
7 4
8 4
9 4
10 4
11 4

I want a single query that deletes 2 oldest rows for each group

After the query, the ff wld remain

3 3
4 3
5 3
8 4
9 4
10 4
11 4

I don't want to use triggers or so.

All guys!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-27 : 04:35:44

What should be the result if there are for example only two rows for a group in that table?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 08:34:13

[code]
DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY mid ORDER BY Id) AS Rn
FROM table
)t
WHERE Rn <=2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazycat503
Starting Member

16 Posts

Posted - 2012-02-01 : 05:03:05

Thanks i will try the code...and yes, if the row number is <=2, no effect on it.

All guys!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 09:30:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources