Delete records more than selected count

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)

Delete records more than selected count

Author

Topic

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 05:46:50

i want a query which should delete the records from table if table records count increases the selected count.

for example
delete from table
where count(*) > 5

recent 5 records should be present and remaining should be deleted.

plz help on this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 05:57:18

How do you know which data are recent?

Madhivanan

Failing to plan is Planning to fail

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 05:59:56

i have a date colum in the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 06:24:01

delete from table where datecol not in (select top 5 datecol from table order by datecol desc)

Madhivanan

Failing to plan is Planning to fail

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 06:39:51

thanks it is working fine .. one more help.
i have a login details table. i have to delete the records based on group by userid.

for each userid recent 5 login details should be present remianing should be deleted.
plz help on this.

table columns : loginid,logintime

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 07:07:56

please any one help on this

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 08:36:32

please help on this

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 08:37:22

i have a login details table. i have to delete the records based on group by userid.

for each userid recent 5 login details should be present remianing should be deleted.
plz help on this.

table columns : loginid,logintime

plz help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 08:46:43

Try this

delete t from table t where logintime not in (select top 5 logintime from table where loginid=t.loginid order by logintime desc)

Madhivanan

Failing to plan is Planning to fail

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 08:57:02

madhavan .. thanks alot for your TIME.

if you dont mind i didnt understand your query ? can you explain it properly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 09:02:23

quote:
Originally posted by hanumanth

madhavan .. thanks alot for your TIME.

if you dont mind i didnt understand your query ? can you explain it properly

It does what you have asked for

Madhivanan

Failing to plan is Planning to fail

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 09:05:58

for testing purpose i changed as below

select * from table t where logintime not in (select top 5 logintime from table where loginid=t.loginid order by logintime desc)
-----
select * from table t where logintime in (select top 5 logintime from table where loginid=t.loginid order by logintime desc)

but im not getting the correct values

hanumanth
Starting Member

21 Posts

Posted - 2008-08-04 : 09:20:24

Madhavinan thanks alot it is working fine.
once again thanks alot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 10:58:08

quote:
Originally posted by hanumanth

Madhavinan thanks alot it is working fine.
once again thanks alot.

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources