Order By Specific ID - 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 2005 Forums

Transact-SQL (2005)

Order By Specific ID

Author

Topic

dave2118
Starting Member

8 Posts

Posted - 2010-10-06 : 09:03:40

Hi!

I have a table, lets say there's 10 records, PK goes from 1 to 10. I'm displaying a gallery in a webpage from 1 to 10, how can I pull a query that would start at a specific record, say 6-10, then 1-5?

I'm basically passing in an ID that I want to show up first in the list. The remaining item's order isn't that relevant.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 09:09:30

Something like this?


DECLARE @foo TABLE (
	[fooID] INT IDENTITY(1,1) PRIMARY KEY
	, [val] CHAR(1)
	)
INSERT @foo
      SELECT 'a' AS [val]
UNION SELECT 'b'
UNION SELECT 'c'
UNION SELECT 'd'
UNION SELECT 'e'
UNION SELECT 'f'
UNION SELECT 'g'
UNION SELECT 'h'
UNION SELECT 'i'
UNION SELECT 'j'
ORDER BY [val]

-- Raw dump
SELECT * FROM @foo ORDER BY [fooID]

DECLARE @startPoint INT SET @startPoint = 7

-- Ordered after passed in value
SELECT * FROM @foo
ORDER BY
	CASE
		WHEN [fooID] >= @startPoint THEN 0
		ELSE 1
	END
	, [fooId]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-06 : 09:13:33

order by case when pkcol in (6,7,8,9,10) then 0 else 1 end,pkcol

Madhivanan

Failing to plan is Planning to fail

dave2118
Starting Member

8 Posts

Posted - 2010-10-06 : 09:20:03

Thanks madhivanan, that worked well.

I just did an

order by case when table.id = @id then 0 else 1 end

dave2118
Starting Member

8 Posts

Posted - 2010-10-06 : 09:20:26

Woops, you both posted the same thing, thanks both of you.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 09:26:00

I'll accept your thanks on behalf of myself and the (4 minutes late) madhivanan

Glad we could help

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources