Sorting Issue - 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 2000 Forums

SQL Server Development (2000)

Sorting Issue

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-04-04 : 03:31:01

I have a recordset below, that i want to sort by a page, and it's parent, and then the other pages. For instance,

PageID PageName ParentID
------ --------------- --------
1 Accomodation 0
2 Reservations 0
3 Amenities 0
11 Imperial Suites 1
12 Sub Res Page 2

i want it to sort like so:

PageID PageName ParentID
------ --------------- --------
1 Accomodation 0
11 Imperial Suites 1
2 Reservations 0
12 Sub Res Page 2
3 Amenities 0

is there an easy way to do this?

cutever
Starting Member

32 Posts

Posted - 2002-04-04 : 03:36:55

You can use [Order By]

Example:
Select * from table_name
Where fieldname = 'xxx'
Order by PageID, ParentID

Ver

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-04 : 04:34:33

cutever...wrong.....order by pageid, parentid....will give the following resultset.

1 Accomodation 0
2 Reservations 0
3 Amenities 0
11 Imperial Suites 1
12 Sub Res Page 2

as the pageid field is unique, the parentid field won't count.
possibly the only way to do this is to 'invent/create' a temporary field in the result set, which will hold the pageid if the parentid is 0, else it will hold the parentid field....and then order by tempfield, pageid.

ie something along the lines of the following.

select pageid, pagename, parentid, (case parentid when 0 then pageid else parentid) as tempfield from tablename order by tempfield, pageid.

the syntax for the case statement may not be 100% right, but the technique should be.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources