ordering by certain keywords? - 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 2008 Forums

Transact-SQL (2008)

ordering by certain keywords?

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2013-03-13 : 23:56:47

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

940 Posts

Posted - 2013-03-14 : 06:17:28

Can you give some example input & output?

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2013-03-14 : 07:51:52

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 08:05:02

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

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2013-03-14 : 08:49:55

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources