Sorting in order of my choice
-
In the below code I want to sort by 'M' first and then sort by 'S' and then by other value.
create table name
(person_name varchar(200));insert into name values('Vadivel');
insert into name values('Muniyan');
insert into name values('Saravanan');
insert into name values('Raj');
insert into name values('Santhosh');What I tried so far mentioned below:
select person_name from name
order by
case
when substring(person_name,1,1)='M' then 1
when substring(person_name,1,1)='S' then 2
else 3
endDesired Output:
person_name Muniyan Saravanan Santhosh Vadivel Raj Is there any alternative or efficient query to write the above requirement.
Saravanan
-
You could make it more succinct by using LEFT, but it's probably as efficient as you're going to get. Are you suffering performance issues with the ORDER BY then?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Sunday, October 7, 2018 3:02 AM
Thanks thorn. This was one of the questions asked in interview. I don't about the performances. Can anyone kindly update me whether Left(column,1)
or substring(column,1,1) performs better?Saravanan
- saravanatn - Sunday, October 7, 2018 5:02 AM
Not sure there will be much (if any) difference. Best way to find out is try.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Few options, here are three of those
😎
USE TEEST;
GO
SET NOCOUNT ON;DECLARE @NAME TABLE
(
person_name VARCHAR(200) NOT NULL
,SORT_ORDER AS (CASE WHEN person_name LIKE 'M%' THEN 1 WHEN person_name LIKE 'S%' THEN 2 ELSE 3 END ) PERSISTED
);
-- CREATE NON CLUSTERED INDEX ON THE CALCULATED COLUMN THAT SUPPORTS THE SORT_ORDER
INSERT INTO @NAME (person_name)
VALUES
('Vadivel')
,('Muniyan')
,('Saravanan')
,('Raj')
,('Santhosh');
-- USING SORT_ORDER
SELECT
NM.person_name
FROM @NAME NM
ORDER BY NM.SORT_ORDER ASC
,NM.person_name ASC;-- USING CASE
SELECT
NM.person_name
FROM @NAME NM
ORDER BY
CASE
WHEN NM.person_name LIKE 'M%' THEN 1
WHEN NM.person_name LIKE 'S%' THEN 2
ELSE 3
END ASC
,NM.person_name ASC;-- USING LEFT
SELECT
NM.person_name
FROM @NAME NM
ORDER BY
CASE
WHEN LEFT(NM.person_name,1) = 'M' THEN 1
WHEN LEFT(NM.person_name,1) = 'S' THEN 2
ELSE 3
END ASC
,NM.person_name ASC;Strongly recommend avoiding functions on the column, puts the compute scalar operator in front of the sort, effectively a blocking operation in the execution plan.
- Eirikur Eiriksson - Sunday, October 7, 2018 6:55 AM
Thanks Eirikur. As suggested by you created CREATE NON CLUSTERED INDEX ON THE CALCULATED COLUMN THAT SUPPORTS THE SORT_ORDER.
CREATE TABLE PRODUCTNAME
(
person_name VARCHAR(200) CONSTRAINT UX_person_name UNIQUE NONCLUSTERED NOT NULL
,SORT_ORDER AS (CASE WHEN person_name LIKE 'M%' THEN 1 WHEN person_name LIKE 'S%' THEN 2 ELSE 3 END ) PERSISTED
);
Below is the performance result for 10000 records:
USING SORT_ORDER -->Table 'PRODUCTNAME'. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
USING CASE -->Table 'PRODUCTNAME'. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
USING LEFT -->Table 'PRODUCTNAME'. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Sorted order :
Using Left:Using Case:
Can anyone kindly help me to understand execution plan in general and also in this scenario which one is performing better?
Saravanan
- Thom A - Sunday, October 7, 2018 5:53 AM
Tried it Thorn.
Saravanan
-
In this particular case - we can shorten it to:
Order By
iif(left(person_name, 1) In ('M', 'S'), 0, 1)
, person_nameHowever - the results here do not match the requested results. The requested results show the S values sorted in descending order - but we don't have enough data to determine if all names should be sorted in descending order. I am assuming that is not what was actually requested - but if so, then we would need further information.
Should the sort be done by the first character putting M and S at top - with all others sorted by the first character in ascending order and the names sorted in descending order?
Order By
iif(left(person_name, 1) In ('M', 'S'), 0, 1)
, left(person_name, 1)
, person_name descMany possibilities - depending on what was actually requested.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams 3188 - Sunday, October 7, 2018 11:40 AM
Should the sort be done by the first character putting M and S at top- with all others sorted by the first character in ascending order and the names sorted in descending order? --> Yes Jeffery in this scenario I need name which starts with 'M' to be sorted first and 'S' to be sorted second and for 'Remaining alphabet' can be sorted with no specific order.Saravanan
- saravanatn - Sunday, October 7, 2018 12:00 PM
In your original post, the S values are sorted in descending order. Is that correct?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams 3188 - Sunday, October 7, 2018 12:09 PM
Yes your correct. I need order by person_name column as well.
Saravanan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply