Select Bottom 16 - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Select Bottom 16

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-05-04 : 04:28:01

How can I select the bottom 16.

I want to run an update query that selects the last 16 records. These are used to in a game schedule table creating game time for the same time but pushed on by one day;
As shown here:
INSERT INTO GameSchedule
(StartTime,EndTime, GameType)
SELECT TOP (16) DATEADD(dd, 1, StartTime) AS NewStartTime, DATEADD(dd, 1, EndTime) AS NewEndTime, GameType
FROM GameSchedule AS GameSchedule_1
ORDER BY GameID

I know I can change the order to DESC but I want to keep the automated transaction number in the table following a continuous sequence. So by reversing the order I will end up with the last game of the day having a lower transaction number.

Hopefully that all makes sense.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-04 : 05:19:51

Try this:

INSERT INTO GameSchedule(StartTime,EndTime, GameType)

select NewStartTime,NewEndTime,GameType
from
(
	SELECT 
	DATEADD(dd, 1, StartTime) AS NewStartTime, 
	DATEADD(dd, 1, EndTime) AS NewEndTime, 
	GameType,
	ROW_NUMBER() OVER (order by GameID DESC) as rnum
	FROM GameSchedule AS GameSchedule_1
)dt
where rnum <= 16
order by rnum DESC

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-04 : 05:30:33

[code];WITH cteSource(GameID, StartTime, EndTime, GameType)
AS (
SELECT TOP(16) GameID,
DATEADD(DAY, 1, StartTime) AS StartTime,
DATEADD(DAY, 1, EndTime) AS EndTime,
GameType
FROM dbo.GameSchedule
ORDER BY GameID DESC
)
INSERT dbo.GameSchedule
(
StartTime,
EndTime,
GameType
)
SELECT StartTime,
EndTime,
GameType
FROM cteSource
ORDER BY GameID[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-04 : 06:46:47

Also refer this
http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-05-06 : 04:53:37

Thank guys sorry for the slow reply I have only just got around to testing it. All replies are excellent and really helpful.
Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources