Order By on Query - 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

Transact-SQL (2000)

Order By on Query

Author

Topic

themark712
Starting Member

5 Posts

Posted - 2002-02-19 : 13:48:37

I would like to know if there is a way in SQL Server to order a select statement on a certain field, based on a format that I specify. Basically I would like to customize the Order By clause.

For example, I have a table that lists spouse and child info for a person based on their SSN. Basically there is a record for each child/spouse/dependent for that person. For example:

Record 1: SSN = "11111111", Counter = "1", Relationship = "Son"
Record 2: SSN = "11111111", Counter = "2", Relationship = "Spouse"
Record 1: SSN = "11111111", Counter = "3", Relationship = "Daughter"

I want my select query to return all records for a particular SSN ordered with all spouses first (the order of the remaining relationship values is irrelevant). Is this possible?

Thanks,
MJ

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-19 : 13:53:28

I thought I saw something like this in Ken Hendersons book, but I cant seem to find it now.

I think you can do something like this


SELECT * FROM people
ORDER BY CASE Relationship
     WHEN 'Spouse' THEN 0
     ELSE 1

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-19 : 13:53:55

select *
from tbl
where ssn = @ssn
order by case when Relationship = "Daughter" then 0 else 1 end

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-19 : 13:55:02

or Relationship = "Spouse"

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-19 : 13:57:13

You can't really tell SQL server which record you want to see first,
but you can specify ascending or descending order. In your case, providing that the example you posted is exactly what you have, it should work

SELECT *
FROM myTable
WHERE ssn=@ssn
ORDER BY relationship DESC

In descending order a spouse will be first, a son will follow and a daughter will be the last.

helena

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-19 : 14:06:12

In your case ORDER BY Relationship Desc will work but if you ever added the relationship say 'Step-son' the query will not work. If you use CASE relationship WHEN 'Spouse' then 0 else 1, the query will work fine until you rename 'Spouse'.

themark712
Starting Member

5 Posts

Posted - 2002-02-19 : 15:08:03

Thanks to everybody! The CASE statement worked! I am a first-time poster here, and I'm really impressed with the response. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources