Ms-access – Query best possible matches and order them

ms accessorder-byquery

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

Best Answer

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
Related Question