Solved

sql select order by priority

Posted on 2011-03-15
3
1,263 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now