?
Solved

sql select order by priority

Posted on 2011-03-15
3
Medium Priority
?
1,405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
Alex Matzinger earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 the fundamental information of how to create a table.
Suggested Courses

777 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