Order by with case

Question

Post reply

Order by with case

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Man, you do really need to read the previous posts.

It's only going to work if you use dynamic sql.

Please note, if you run the following type of query :

SELECT Col1, Col2, Col3

FROM Table1

ORDER BY

CASE WHEN [something] THEN 1

WHEN [something else] THEN 2

ELSE 3

END

results will be always sorted randomly, as 1, 2, 3 will be taken as a constant value - not as a column indexes.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Eugene Elutin (9/13/2012)
Man, you do really need to read the previous posts.
It's only going to work if you use dynamic sql.
Please note, if you run the following type of query :
SELECT Col1, Col2, Col3
FROM Table1
ORDER BY
CASE WHEN [something] THEN 1
WHEN [something else] THEN 2
ELSE 3
END
results will be always sorted randomly, as 1, 2, 3 will be taken as a constant value - not as a column indexes.

He's changed tack, Eugene - and it will work too:

USE [Sandbox]

GO

-- set up some sample data;

DROP TABLE [dbo].[Stock]

CREATE TABLE [dbo].[Stock](

[clientcode] [varchar](10) NULL,

[productid] [varchar](10) NULL,

[pdate] [date] NULL,

[ptime] [varchar](20) NULL,

[pno] [numeric](18, 0) NULL,

[ptype] [varchar](1) NULL,

[qty] [int] NULL,

[rate] [numeric](18, 2) NULL,

[uniqno] [int] NULL

) ON [PRIMARY];

INSERT INTO [dbo].[Stock](

[clientcode],

[productid],

[pdate],

[ptime],

[pno],

[ptype],

[qty],

[rate],

[uniqno])

SELECT 'A001', 'P001', '2012-03-20', '09:25:19', 1231, 'B', 10, 115.20, 1 UNION ALL

SELECT 'A001', 'P001', '2012-03-20', '09:30:01', 1232, 'B', 20, 118.30, 2 UNION ALL

SELECT 'A001', 'P001', '2012-03-21', '10:10:10', 1231, 'B', 30, 200.30, 3 UNION ALL

SELECT 'A001', 'P001', '2012-03-20', '09:27:19', 1233, 'S', 5, 114.35, 4 UNION ALL

SELECT 'A001', 'P001', '2012-03-20', '10:10:12', 1234, 'S', 10, 116.25, 5 UNION ALL

SELECT 'A001', 'P001', '2012-03-21', '11:10:20', 1232, 'S', 25, 119.35, 6 UNION ALL

SELECT 'A002', 'P002', '2012-03-20', '09:26:20', 1235, 'S', 50, 200.25, 7 UNION ALL

SELECT 'A002', 'P001', '2012-03-20', '15:10:25', 1236, 'S', 30, 120.25, 8 UNION ALL

SELECT 'A001', 'P002', '2012-03-20', '16:10:25', 1237, 'S', 100, 130.25, 9 UNION ALL

SELECT 'A002', 'P002', '2012-03-21', '10:12:52', 1233, 'B', 100, 190.25, 10 UNION ALL

SELECT 'A001', 'P001', '2012-03-22', '10:10:12', 1234, 'S', 35, 116.25, 11;

-- Use an INT variable to choose a column to order by;

DECLARE @var INT = 3;

SELECT *

FROM [Sandbox].[dbo].[Stock]

CROSS APPLY (

SELECT OrderBy = CASE

WHEN @var = 1 THEN CAST(ClientCode AS VARCHAR(10))

WHEN @var = 2 THEN CAST(productid AS VARCHAR(10))

WHEN @var = 3 THEN CONVERT(VARCHAR(10),pdate)

ELSE CAST(pno AS VARCHAR(10))

END

) x

ORDER BY x.OrderBy;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rajawat.niranjan Old Hand Points: 373 More actions · Answer 3

ChrisM@Work (9/13/2012)
Eugene Elutin (9/13/2012)
Man, you do really need to read the previous posts.
It's only going to work if you use dynamic sql.
Please note, if you run the following type of query :
SELECT Col1, Col2, Col3
FROM Table1
ORDER BY
CASE WHEN [something] THEN 1
WHEN [something else] THEN 2
ELSE 3
END
results will be always sorted randomly, as 1, 2, 3 will be taken as a constant value - not as a column indexes.
He's changed tack, Eugene - and it will work too:
USE [Sandbox]
GO
-- set up some sample data;
DROP TABLE [dbo].[Stock]
CREATE TABLE [dbo].[Stock](
[clientcode] [varchar](10) NULL,
[productid] [varchar](10) NULL,
[pdate] [date] NULL,
[ptime] [varchar](20) NULL,
[pno] [numeric](18, 0) NULL,
[ptype] [varchar](1) NULL,
[qty] [int] NULL,
[rate] [numeric](18, 2) NULL,
[uniqno] [int] NULL
) ON [PRIMARY];
INSERT INTO [dbo].[Stock](
[clientcode],
[productid],
[pdate],
[ptime],
[pno],
[ptype],
[qty],
[rate],
[uniqno])
SELECT 'A001', 'P001', '2012-03-20', '09:25:19', 1231, 'B', 10, 115.20, 1 UNION ALL
SELECT 'A001', 'P001', '2012-03-20', '09:30:01', 1232, 'B', 20, 118.30, 2 UNION ALL
SELECT 'A001', 'P001', '2012-03-21', '10:10:10', 1231, 'B', 30, 200.30, 3 UNION ALL
SELECT 'A001', 'P001', '2012-03-20', '09:27:19', 1233, 'S', 5, 114.35, 4 UNION ALL
SELECT 'A001', 'P001', '2012-03-20', '10:10:12', 1234, 'S', 10, 116.25, 5 UNION ALL
SELECT 'A001', 'P001', '2012-03-21', '11:10:20', 1232, 'S', 25, 119.35, 6 UNION ALL
SELECT 'A002', 'P002', '2012-03-20', '09:26:20', 1235, 'S', 50, 200.25, 7 UNION ALL
SELECT 'A002', 'P001', '2012-03-20', '15:10:25', 1236, 'S', 30, 120.25, 8 UNION ALL
SELECT 'A001', 'P002', '2012-03-20', '16:10:25', 1237, 'S', 100, 130.25, 9 UNION ALL
SELECT 'A002', 'P002', '2012-03-21', '10:12:52', 1233, 'B', 100, 190.25, 10 UNION ALL
SELECT 'A001', 'P001', '2012-03-22', '10:10:12', 1234, 'S', 35, 116.25, 11;
-- Use an INT variable to choose a column to order by;
DECLARE @var INT = 3;
SELECT *
FROM [Sandbox].[dbo].[Stock]
CROSS APPLY (
SELECT OrderBy = CASE
WHEN @var = 1 THEN CAST(ClientCode AS VARCHAR(10))
WHEN @var = 2 THEN CAST(productid AS VARCHAR(10))
WHEN @var = 3 THEN CONVERT(VARCHAR(10),pdate)
ELSE CAST(pno AS VARCHAR(10))
END
) x
ORDER BY x.OrderBy;

@ChrisM@Work I tried your solution but don't know what is wrong with data type??

declare @var int =2

select ClientInternalID, ClientName, NULL ParentInternalID from TB_Client cl

CROSS APPLY (

SELECT OrderBy = CASE @var

WHEN 1 THEN CAST(ClientInternalID AS VARCHAR(10))

WHEN 2 THEN CAST(ClientName AS VARCHAR(10))

WHEN 3 THEN (select null)

WHEN 4 THEN CAST(ClientSequenceOrder AS VARCHAR(10))

END

) x

ORDER BY x.OrderBy;

still throws error:

Conversion failed when converting the varchar value 'EUROPEAN B' to data type int.

Any idea?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Try this:

declare @var int =2

select ClientInternalID, ClientName, NULL ParentInternalID

from TB_Client cl

CROSS APPLY (

SELECT OrderBy = CASE @var

WHEN 1 THEN CAST(ClientInternalID AS VARCHAR(10))

WHEN 2 THEN CAST(ClientName AS VARCHAR(10))

WHEN 4 THEN CAST(ClientSequenceOrder AS VARCHAR(10))

ELSE CAST(NULL AS VARCHAR(10))

END

) x

ORDER BY x.OrderBy;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

OK, I've missed the "change of concept" from OP.

However, I still advice to use dynamic SQL. Why? I kind of think that it will perform better here...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

rajawat.niranjan Old Hand Points: 373 More actions · Answer 6

Thanks @ChrisM@Work

It worked 🙂

But what was wrong was is null sort or something else?

rajawat.niranjan Old Hand Points: 373 More actions · Answer 7

ChrisM@Work (9/13/2012)
Try this:
declare @var int =2
select ClientInternalID, ClientName, NULL ParentInternalID
from TB_Client cl
CROSS APPLY (
SELECT OrderBy = CASE @var
WHEN 1 THEN CAST(ClientInternalID AS VARCHAR(10))
WHEN 2 THEN CAST(ClientName AS VARCHAR(10))
WHEN 4 THEN CAST(ClientSequenceOrder AS VARCHAR(10))
ELSE CAST(NULL AS VARCHAR(10))
END
) x
ORDER BY x.OrderBy;

Hi ChrisM@Work

Sorry I was wrong with this, your solution did't work!!!

It was sorting integer column as character

But This worked If I cast to SQL_VARIENT

declare @var int =4

select ClientInternalID, ClientName, NULL ParentInternalID from TB_Client cl

inner join TB_Contract co on cl.ClientContractID = co.ContractInternalID

CROSS APPLY

(SELECT OrderBy = CASE @var

WHEN 1 THEN CAST(ClientInternalID AS SQL_VARIANT)

WHEN 2 THEN CAST(ClientName AS SQL_VARIANT)

WHEN 4 THEN CAST(ClientSequenceOrder AS SQL_VARIANT)

ELSE CAST(NULL AS VARCHAR(10))END)

x

where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647' and co.ContractStatusID = 1 and ClientStatusID = 1 and ClientName like '%%'

ORDER BY x.OrderBy asc;

I don't know why case looks up to all data types while sorting??

It should look for case 2 when @var value is 2 but it tries to convert char values to integer if we cast any column to int.

But it worked fine if all columns are cast to SQL_VARIENT.

Any suggestions??

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

rajawat.niranjan (9/13/2012)
Thanks @ChrisM@Work
It worked 🙂
But what was wrong was is null sort or something else?

Each option in the CASE construct, including the ELSE, must have the same datatype - otherwise SQL Server will choose the highest precedence datatype according to precedence rules.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rajawat.niranjan Old Hand Points: 373 More actions · Answer 9

ChrisM@Work (9/14/2012)
rajawat.niranjan (9/13/2012)
Thanks @ChrisM@Work
It worked 🙂
But what was wrong was is null sort or something else?
Each option in the CASE construct, including the ELSE, must have the same datatype - otherwise SQL Server will choose the highest precedence datatype according to precedence rules.

Got it.

Thanks ChrisM@Work

🙂

28.kanikasoni Mr or Mrs. 500 Points: 526 More actions · Answer 10

28.kanikasoni

Mr or Mrs. 500

Points: 526

September 20, 2012 at 3:29 am

#1539947

Nice example of order by using case