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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
mikeydkAuthor Commented:
Hey

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

Mike
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.