SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 ordering by certain keywords?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

detlion1643
Yak Posting Veteran

67 Posts
Posted - 03/13/2013 :  23:56:47  Show Profile  Reply with Quote
I have this query that is working great, but need to adapt the order of the rows returned. I currently have a simple order by statement that returns the rows in descending order based on 1 field. However, I need to some ordering based on keywords for another field.

Ex:

SELECT *
FROM Table
WHERE criteria=xxx
ORDER BY Field1 DESC


Now, I also would like to add something to this effect. Ordering the returned rows, by Field1 always displaying on top (there is only ever 1 of these per result set) and then all but that top row by Field2 based on keywords. Field2 can contain about 6 or 7 different keywords, but I can't just use ASC or DESC ordering.

Where Field2 = 'Keyword4' on top, Field2 = 'Keyword1' next, etc... I could prob use some UNIONS but I can't remember the code well enough for that, and really want to avoid re-writing the current query.

LoztInSpace
Aged Yak Warrior

876 Posts
Posted - 03/14/2013 :  06:17:28  Show Profile  Reply with Quote
Can you give some example input & output?
Go to Top of Page

detlion1643
Yak Posting Veteran

67 Posts
Posted - 03/14/2013 :  07:51:52  Show Profile  Reply with Quote
Let's say Field2 contains keywords like "Grand Parent", "Child", "Step Parent", "Parent", and "Sibling".

After the above query runs I will get a result set that contains the amount of rows with 1 specific row at top. All of the next rows need to be ordered by "Parent", then "Step Parent", then "Grand Parent", and so on...

Edit:
In theory it would be like this
SELECT TOP 1 *
FROM Table1
WHERE Field1 = criteria
ORDER BY Field3

+

SELECT *
FROM Table1
WHERE Field1 = criteria AND Field2 = 'Parent'

+

SELECT *
FROM Table1
WHERE Field1 = criteria AND Field2 = 'Step Parent'

... etc
Edited by - detlion1643 on 03/14/2013 07:55:14
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1420 Posts
Posted - 03/14/2013 :  08:05:02  Show Profile  Reply with Quote
May be this?
ORDER BY CASE WHEN Field2 THEN 'Parent' THEN 1 
              WHEN Field2 THEN 'Step Parent' THEN 2
              WHEN Field2 THEN 'Grand Parent' THEN 3
              ................
         END


--
Chandu
Go to Top of Page

detlion1643
Yak Posting Veteran

67 Posts
Posted - 03/14/2013 :  08:49:55  Show Profile  Reply with Quote
Initial testing seems to indicate that the above works.. I did have to put this before the initial ordering I did. I'll have to figure some more testing to really make sure. But thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000