SQL sort

Hey

I would like to sort the SQL query as shown below (first all with DDD, then AAA and then BBB)

Is it possible?

"select * from hardware where status = 1 and (ref = 'BBB' or ref= 'AAA' or ref = 'DDD')"

PC4 DDD
PC5 DDD
PC6 AAA
PC7 AAA
PC1 BBB
PC2 BBB
PC3 BBB
LVL 1
mikeydkAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
>>How is CROSS APPLY fundamentally any different than a JOIN?
One is that the APPLY operators are designed to use against table valued functions (tvfs) and Joins is used only for tables. The cross apply works in a correlated way that a join cannot do.

>>Other than being slightly less efficient
As far as I know with this the user should not face any performance issues. I request author to compare the last two solutions and decide based on their performances which one suits him.

>>.(unless SQL implicitly changes the CROSS APPLY to a join on its own).
Yes SQL Server will internally convert this into a Inner loop join.
0
 
Pawan KumarDatabase ExpertCommented:
Please use this -

select * from hardware where status = 1 and (ref = 'BBB' or ref= 'AAA' or ref = 'DDD')
ORDER BY SUBSTRING(ref,CHARINDEX(' ',ref,0),LEN(ref)) DESC

Open in new window


SAMPLE TESTING

CREATE TABLE testSORT
(
	ref  VARCHAR(20)
)
GO

INSERT INTO testSORT VALUES 
('PC4 DDD'),
('PC5 DDD'),
('PC6 AAA'),
('PC7 AAA'),
('PC1 BBB'),
('PC2 BBB'),
('PC3 BBB')
GO

Open in new window


SOLUTION
SELECT * FROM testSORT 
ORDER BY SUBSTRING(ref,CHARINDEX(' ',ref,0),LEN(ref)) DESC

Open in new window


OUTPUT
/*------------------------
SELECT * FROM testSORT 
ORDER BY SUBSTRING(ref,CHARINDEX(' ',ref,0),LEN(ref)) DESC
------------------------*/
ref
--------------------
PC4 DDD
PC5 DDD
PC1 BBB
PC2 BBB
PC3 BBB
PC6 AAA
PC7 AAA

(7 row(s) affected)

Open in new window

0
 
Ryan ChongCommented:
or:
select * from hardware
where status = 1 and (ref = 'BBB' or ref= 'AAA' or ref = 'DDD')
order by case when ref = 'DDD' then 0 else 1 end, ref, 1

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mikeydkAuthor Commented:
Hey

Order --- DDD, then AAA and then BBB.. (not alfa)

Mike
0
 
Pawan KumarDatabase ExpertCommented:
Please use this -

SELECT * FROM testSORT 
ORDER BY CASE WHEN ref = 'DDD' THEN 1 
			  WHEN ref= 'AAA' THEN 2
			  WHEN ref= 'BBB' THEN 3 END 

Open in new window


OUTPUT

/*------------------------

SELECT * FROM testSORT 
ORDER BY CASE WHEN ref = 'DDD' THEN 1 
			  WHEN ref= 'AAA' THEN 2
			  WHEN ref= 'BBB' THEN 3 END 
------------------------*/
ref
--------------------
PC4 DDD
PC5 DDD
PC6 AAA
PC7 AAA
PC1 BBB
PC2 BBB
PC3 BBB

(7 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
For your query you need

select * from hardware where status = 1 and (ref = 'BBB' or ref= 'AAA' or ref = 'DDD')
ORDER BY CASE WHEN ref = 'DDD' THEN 1 
			  WHEN ref= 'AAA' THEN 2
			  WHEN ref= 'BBB' THEN 3 END 

Open in new window

0
 
Adam MurrayCommented:
Specify a section or articulation on which to sort the question result set. A sort section can be determined as a name or segment nom de plume, or a nonnegative whole number speaking to the position of the segment in the select rundown.

Various sort segments can be indicated. Section names must be one of a kind. The succession of the sort sections in the ORDER BY condition characterizes the association of the arranged outcome set. That is, the outcome set is arranged by the principal section and after that that requested rundown is arranged by the second segment, et cetera.

The segment names referenced in the ORDER BY proviso must compare to either a section in the select rundown or to a segment characterized in a table determined in the FROM condition with no ambiguities.
0
 
Scott PletcherSenior DBACommented:
For performance reasons, and ease of use and maintenance, create a table that specifies the sort order.  I've done it inline below, but if this is a common report, you'd probably want to create a permanent table to store the sort order:

select h.*
from hardware h
inner join (
    select 'AAA' as ref, 2 as sort_order
    union all
    select 'BBB', 3
    union all
    select 'DDD', 1
) as sort on sort.ref = h.ref
where h.status = 1 and (h.ref = 'BBB' or h.ref= 'AAA' or h.ref = 'DDD')
order by sort.sort_order
0
 
Pawan KumarDatabase ExpertCommented:
Please try one more full tested solution. This approach (CROSS APPLY With VALUES clause) will give you excellent performance.

DATA GENERATION

CREATE TABLE hardware
(
	  ID INT
	,[Status] SMALLINT
	,vals VARCHAR(10)
	,ref VARCHAR(10)
)
GO

INSERT INTO hardware VALUES
(1,1,'PC5 DDD','DDD'),
(8,1,'PC3 BBB','BBB'),
(3,1,'PC4 DDD','DDD'),
(4,1,'PC6 AAA','AAA'),
(5,1,'PC7 AAA','AAA'),
(6,1,'PC1 BBB','BBB'),
(7,1,'PC2 BBB','BBB'),
(9,2,'Pawan','R')

Open in new window


SOLUTION
SELECT h.* FROM hardware h
CROSS APPLY ( VALUES ('AAA',2),('BBB',3),('DDD',1)) as rt(ref,orders)
WHERE [status] = 1 AND rt.ref = h.ref
ORDER BY rt.orders

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
ID          Status vals       ref
----------- ------ ---------- ----------
1           1      PC5 DDD    DDD
3           1      PC4 DDD    DDD
4           1      PC6 AAA    AAA
5           1      PC7 AAA    AAA
8           1      PC3 BBB    BBB
6           1      PC1 BBB    BBB
7           1      PC2 BBB    BBB

(7 row(s) affected)

Open in new window

0
 
Scott PletcherSenior DBACommented:
How is CROSS APPLY fundamentally any different than a JOIN?  Other than being slightly less efficient (unless SQL implicitly changes the CROSS APPLY to a join on its own).
0
 
Scott PletcherSenior DBACommented:
Fascinating.  He copies my solution and gets all the points.
0
 
Pawan KumarDatabase ExpertCommented:
Not true.
This comment (#a42384022) completely answers the question way before your comment. A simple case statement.
0
 
Scott PletcherSenior DBACommented:
Copied my methodology of using a table structure to assign the values rather than an inline CASE statement or other hack.
0
 
Pawan KumarDatabase ExpertCommented:
NOT TRUE.

See this comment (#a42384022) . This was posted way before your comment. So this is not related to your code. There was no table involved here.

select * from hardware where status = 1 and (ref = 'BBB' or ref= 'AAA' or ref = 'DDD')
ORDER BY CASE WHEN ref = 'DDD' THEN 1
                    WHEN ref= 'AAA' THEN 2
                    WHEN ref= 'BBB' THEN 3 END
0
 
Scott PletcherSenior DBACommented:
No table, yes, THAT is the point.  None of your original code used any type of table / lookup structure, the sort order was only hard-coded, and thus much harder to change than making it a column name.

In my code:
inner join (
    select 'AAA' as ref, 2 as sort_order
    union all
    select 'BBB', 3
    union all
    select 'DDD', 1

You final "solution" changed to also using a column for the order, unlike all your earlier code:
CROSS APPLY ( VALUES ('AAA',2),('BBB',3),('DDD',1)) as rt(ref,orders)

If I had used a CROSS APPLY, you'd have used a JOIN so you could copy it while still "not copying" :-).

Doesn't matter, whatever.  I hope the original poster thinks the final answer was useful, that's all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.