9

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 http://img708.imageshack.us/img708/1646/sampletableresult1.jpg

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
  • 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
7
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 
  • @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
1

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 
  • 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
1
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
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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