query to order by multiple columns

Question

query to order by multiple columns

sqlfriend

SSC Guru

Points: 52794
More actions
October 9, 2013 at 11:33 am

#395636

I have a query to pull the first contact of students.
The table has all contacts like parent/guardian, friends family, emergency contact etc.
I would like to the first priamry contact in this order,
1. initial contact, 2. same as student address and also have to be parents,
3. live with and also a parent, 4. parents 5. friends.
I don't know how to pull 2 and 3 . because it looks like it needs to concacenate the columns.
here is my initial query
Select Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc)
From Contacts
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

sqlfriends (10/9/2013)
I have a query to pull the first contact of students.
The table has all contacts like parent/guardian, friends family, emergency contact etc.
I would like to the first priamry contact in this order,
1. initial contact, 2. same as student address and also have to be parents,
3. live with and also a parent, 4. parents 5. friends.
I don't know how to pull 2 and 3 . because it looks like it needs to concacenate the columns.
here is my initial query
Select Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc)
From Contacts
Thanks

Can you post some details so we aren't guessing? You have been around long enough to know that you need to provide table details here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

sqlfriend

SSC Guru

Points: 52794

October 9, 2013 at 12:08 pm

#1657258

attached is the table script

LutzM SSC Guru Points: 107049 More actions · Answer 3

I'd question the initial query.

It seems like it's Oracle syntax. AFAIK, SQL Server doesn't support "Select Min(U2.Id) Keep (...)" syntax. For sure not SS2K8 (as indicated by the forum you posted in). Might be a task for OVER(PARTITION BY)...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 4

The table script provided even more indicate an Oracle DB. So I'm out.