Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to write a query along these lines:

select * 
from tbl 
where 
       col1 = 1 
   and col2 = 2 
   and col3 = 3
order by
   ...
;

I want first all the results where all 3 WHERE conditions match (3/3), then all the results where any 2 conditions match (2/3), and finally the results where any 1 condition matches (1/3).

Each of these 3 result sets needs to be ordered by (col4, col5, col6).

Can I do that in a single query?

For example:

sample

Script to create test data:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
    DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    col1 INT
    , col2 INT 
    , col3 INT 
    , col4 INT 
    , col5 INT 
    , col6 INT 
)
GO

INSERT dbo.MyTable (col1, col2, col3, col4, col5, col6)
SELECT 1,2,3,2,1,1 UNION ALL 
SELECT 1,2,30,1,1,1 UNION ALL SELECT 1,20,30,1,1,1 UNION ALL 
SELECT 10,20,3,1,1,1 UNION ALL SELECT 10,2,30,1,1,1 UNION ALL
SELECT 10,2,3,1,1,1 UNION ALL SELECT 10,20,30,1,1,1 UNION ALL
SELECT 1,2,3,1,1,1 UNION ALL SELECT 1,2,3,1,2,2 UNION ALL
SELECT 1,2,3,1,2,3 UNION ALL SELECT 1,20,3,1,1,1
GO
share|improve this question
    
if possible without 3! join the sample have 3 first col, but in reality, its more than that –  forX Nov 2 '11 at 19:51
    
So you want to find all rows where all 3 conditions match, then where any 2 conditions match, then where any 1 condition matches and put all the results together, sorted by col4, 5, and 6. Is that correct? –  Nick Chammas Nov 2 '11 at 19:59
    
yeah, I know, I was searching another way, because its more than 3 col –  forX Nov 2 '11 at 20:07
1  
well, for the moment its access (I'm waiting to get my sql server db) –  forX Nov 2 '11 at 20:11
3  
If you'll eventually be using SQL Server, install the express edition. Clean upgrade path to a paid for version and no dealing with syntax idiosyncrasies in Access. –  Mark Storey-Smith Nov 2 '11 at 23:28
show 3 more comments

3 Answers

up vote 4 down vote accepted
SELECT col1, 
       col2,
       col3, 
       col4, 
       col5, 
       col6
FROM TableX 
WHERE col1 = 1 
   OR col2 = 2 
   OR col3 = 3 
ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) +
         (CASE WHEN col2 = 2 THEN 1 ELSE 0 END) +
         (CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC,
         col4, col5, col6 

or, for MS-Access:

ORDER BY IIF(col1 = 1,1,0) +
         IIF(col2 = 2,1,0) +
         IIF(col3 = 3,1,0) DESC,
         col4, col5, col6 
share|improve this answer
    
@Mark: What combinations you mean? Can you give an example? –  ypercube Nov 3 '11 at 11:37
    
@ypercube I concede, +1 :) –  Mark Storey-Smith Nov 3 '11 at 12:07
    
@Mark: thnx, I was starting to worry that I hadn't understood the question. –  ypercube Nov 3 '11 at 12:08
    
seems good, but I'm stock now with access db, and I try this, and I dont get the good result (SELECT col1, col2, col3, col4, col5, col6 FROM mytable WHERE col1 = 1 OR col2 = 2 OR col3 = 3 ORDER BY IIF(col1 = 1,1,0) +IIF(col2 = 1,1,0) +IIF(col3 = 1,1,0) desc,col4,col5,col6) –  forX Nov 3 '11 at 13:19
    
Small modification: IIF(col1 = 1,1,0) +IIF(col2 = 2,1,0) +IIF(col3 = 3,1,0) desc –  ypercube Nov 3 '11 at 13:33
show 3 more comments

Would this achieve what you want? Technicly the inline view isnt necessary as you could repeat the case statement in the order by.

select y.col1, 
       y.col2,
       y.col3, 
       y.col4, 
       y.col5, 
       y.col6
from (
      select col1, 
             col2, 
             col3, 
             case when col1 =1 and col2 = 2 and col3 = 3 then
                       1
                  when col1 = 1 and col2 = 2 and col3 <> 3 then
                       2
                  when col1 = 1 then
                       3
                  else 
                       4
             end x,
             col4, 
             col5, 
             col6
       from table
       )y
order by y.x, col4, col5, col6 
share|improve this answer
1  
What if col1 and col3 match? Or just col2? The OP is looking for all 3 match, any two match, any one matches. –  Nick Chammas Nov 2 '11 at 21:41
add comment
SELECT col1,
       col2,
       col3,
       col4,
       col5,
       col6
FROM TableName
WHERE col1 = 1
   OR col2 = 2
   OR col3 = 3
ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) DESC,
         (CASE WHEN col2 = 2 THEN 1 ELSE 0 END) DESC,
         (CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC,
         col4, col5, col6
share|improve this answer
    
No, this query will order the row with (col1,col2,col3) = (1,0,0) (1 match) before the row with (0,2,3) (2 matches). –  ypercube Nov 3 '11 at 11:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.