how to get this solved? - 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

General SQL Server Forums

New to SQL Server Programming

how to get this solved?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2012-01-04 : 12:59:49

Hi there,

select top 10 SalesDate from table1 order by SalesDate desc

I need get the 10th SalesDate and put it in another statement like

delete from table1 where SalesDate < 10th_SalesDate

How can I solve this? Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 13:02:25

delete from table1 where SalesDate < (
select Top 1 SalesDate FROM (
select top 10 SalesDate from table1 order by SalesDate desc) AS XXX
)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 13:14:51

[code]
delete t
from (select row_number() over (order by SalesDate desc) as rn
from table1
)t
where rn>10
[/code]

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 13:35:52

quote:
Originally posted by visakh16

delete t
from (select row_number() over (order by SalesDate desc) as rn
from table1
)t
where rn>10

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

Is that correct?

Always take a backup first

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 13:41:46

I think they want to delete everything BELOW the 10th occurrence



CREATE TABLE #table1 (SalesDate date)
GO

INSERT INTO #table1 (SalesDate)
SELECT '1/1/2012' UNION ALL
SELECT '2/1/2012' UNION ALL
SELECT '3/1/2012' UNION ALL
SELECT '4/1/2012' UNION ALL
SELECT '5/1/2012' UNION ALL
SELECT '6/1/2012' UNION ALL
SELECT '7/1/2012' UNION ALL
SELECT '8/1/2012' UNION ALL
SELECT '9/1/2012' UNION ALL
SELECT '10/1/2012' UNION ALL
SELECT '11/1/2012' UNION ALL
SELECT '12/1/2012'
GO

SELECT * FROM #table1
GO

delete t
from (select row_number() over (order by SalesDate desc) as rn
from #table1 
)t
where rn>10
GO

SELECT * FROM #table1
GO

DROP TABLE #table1
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 13:45:19

Actually..the 10th Sales Date in which Direction?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2012-01-04 : 14:40:05

thanks gentlemen. I think this only returns top 1 of the series:

delete from table1 where SalesDate < (
select Top 1 SalesDate FROM (
select top 10 SalesDate from table1 order by SalesDate desc) AS XXX
)

and this one works:

delete t
from (select row_number() over (order by SalesDate desc) as rn
from table1
)t
where rn>10

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 14:53:06

of course it only returns 1...you wanted the 10th ONE..then delete everything less than that..unless I read that wrong

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2012-01-04 : 19:22:27

Yes sir, I need only one but not top one, I need the BOTTOM one. Your code returns 1st NOT 10th! Think about it:

1) You must use DESC to get top ones like "select top 10 SalesDate from table1 order by SalesDate desc";
2) after that this piece of codes "select Top 1 SalesDate FROM" returns TOP one from the above codes.
3) You use "delete from table1 where SalesDate < " to erase ...,

Actually if I have 20 SalesDate then your codes delete all rows except the ones with the latest date. This is not what I want. I want to delete all which ealier than the earliest from the top 10 latest date. Your code will work if we have Bottom in DML state to specify the LAST set of rows. then we can do:

delete from table1 where SalesDate < (
select BOTTOM 1 SalesDate FROM (
select top 10 SalesDate from table1 order by SalesDate desc) AS XXX
)

Just kidding. Maybe you think the 10th is the latest one, well, if that's the case then I can just do "delete from table1 where SalesDate < (select Max(SalesDate) from table1)" ... so I think using row_num as visakh16 instructed is the only way ...anyway I appreciate your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 09:50:30

quote:
Originally posted by X002548

I think they want to delete everything BELOW the 10th occurrence

CREATE TABLE #table1 (SalesDate date)
GO

INSERT INTO #table1 (SalesDate)
SELECT '1/1/2012' UNION ALL
SELECT '2/1/2012' UNION ALL
SELECT '3/1/2012' UNION ALL
SELECT '4/1/2012' UNION ALL
SELECT '5/1/2012' UNION ALL
SELECT '6/1/2012' UNION ALL
SELECT '7/1/2012' UNION ALL
SELECT '8/1/2012' UNION ALL
SELECT '9/1/2012' UNION ALL
SELECT '10/1/2012' UNION ALL
SELECT '11/1/2012' UNION ALL
SELECT '12/1/2012'
GO

SELECT * FROM #table1
GO

delete t
from (select row_number() over (order by SalesDate desc) as rn
from #table1
)t
where rn>10
GO

SELECT * FROM #table1
GO

DROP TABLE #table1
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

thats exactly what my query does. it numbers the records based on descending order of saledate and deletes everything > 10 (ie below 10th greatest date)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources