Solved

sql select order by priority

Posted on 2011-03-15
3
1,284 Views
Last Modified: 2012-05-11
Hi I have a table

id | custname | catagory | keyword
1     jack         |  cat1       | bikes
2     bill           |  cat1       | cars
3     frank       |  cat2       | bikes
4     john        |  cat1       | cars
5     joe          |  cat1       | cars

I need to select the top 4 based on priority of keyword.
If keyword has bikes then select (cat does not matter)
If there are 4 with the keyword bikes that is ok
If there are only 2 then the rest should be added by cat

My result should be
id | custname | catagory | keyword
1     jack         |  cat1       | bikes
3     frank       |  cat2       | bikes
2     bill           |  cat1       | cars
4     john        |  cat1       | cars

My statement look something like this: but does not order properly

SELECT TOP 4 id,custname,catagory,keyword
WHERE (keyword = 'bikes' or catagory='cat1')
 ORDER BY keyword, catagory, id,custname


Thanks In Advance
0
Comment
Question by:doctor069
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
Alex Matzinger earned 500 total points
ID: 35142707
Select TOP 4 id,custname,catagory,keyword 
FROM 
  (Select * 
  From table
  Where keyword = 'bikes'
  ORDER BY keyword, catagory, id,custname

  Union

  Select *
  From Table
  Where keyword != 'bikes'
 ORDER BY keyword, catagory, id,custname) temp

Open in new window

0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35142717
That will select all of the rows with bike and order them, and then put the rest of the rows underneath them, and then select the top 4 rows from that unioned table.  It should be what you need.
0
 

Author Closing Comment

by:doctor069
ID: 35142810
That did it! Thanks for your help
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question