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.

IN MSSQL 2005 I am writing one query with conditional sort and my problem is I do not know how I can sort condidional using two columns.

if i wrte code like this it is working normaly

select
*
from 
table
order by 
    case @pkr 
           when 'kol' then kol
           when 'nci' then nci
    end

I do not know how to make conditional ordering for two or more columns

select
*
from 
table
order by 
          case @pkr
                when 'KOL-NCI' then kol,nci
                when 'kol-MPCI' then kol,mpci
          end

There is an idea to make dynamic TSQL and use sp_executesql but I am still loking for better idea.

share|improve this question
    
possible duplicate of 'Conversion failed' error with ORDER BY CASE expression –  gbn Oct 27 '11 at 10:07
add comment

3 Answers

up vote 2 down vote accepted

I'll admit I've never had to do this before so there was a bit of head scratching involved. Simple example table to demonstrate:

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 CHAR(1)
)
GO

INSERT dbo.MyTable (col1, col2) VALUES (1, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'C')

Using an @SortStyle parameter to differentiate between sort orders, @SortStyle =1 will sort by col1 ASC, col2 DESC and @SortStyle=2 sort by col2 DESC, col1 ASC.

DECLARE @SortStyle INT
SET @SortStyle = 1

SELECT
    col1
    , col2
FROM
    dbo.MyTable
ORDER BY
    CASE
        WHEN @SortStyle = 1 THEN col1
    END ASC,
    CASE
        WHEN @SortStyle = 1 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col1
    END ASC

SET @SortStyle = 2

SELECT
    col1
    , col2
FROM
    dbo.MyTable
ORDER BY
    CASE
        WHEN @SortStyle = 1 THEN col1
    END ASC,
    CASE
        WHEN @SortStyle = 1 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col1
    END ASC

How do you ORDER BY a parameter covers the simpler case of sorting by just 1 column.

share|improve this answer
add comment

Assuming you have more cases (I added one), and all types are compatible

order by 
    case @pkr
        when 'KOL-NCI' then kol
        when 'kol-MPCI' then kol
        when 'foo-bar then foo
    end,
    case @pkr
        when 'KOL-NCI' then nci
        when 'kol-MPCI' then mpci
        when 'foo-bar then bar 
    end

It is isn't a multi-column sort: you have a primary sort, followed by a secondary sort. Just look at the sort dialogue box in Excel to see what I mean

share|improve this answer
add comment

With the example you give it is simple:

select *
from table
order by kol, case @pkr
                when 'KOL-NCI' then nci
                when 'kol-MPCI' then mpci
              end

There is an idea to make dynamic TSQL and use sp_executesql but I am still loking for better idea.

It is always nice to avoid dynamic SQL where possible

share|improve this answer
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.