SQL sort

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

Select allOpen 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

Select allOpen in new window

SOLUTION

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

Select allOpen 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)

Select allOpen in new window

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

Select allOpen in new window

Hey

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

Mike

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 

Select allOpen 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)

Select allOpen in new window

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 

Select allOpen in new window

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.

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

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')

Select allOpen 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

Select allOpen 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)

Select allOpen in new window

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).

Fascinating.  He copies my solution and gets all the points.

Not true.
This comment (#a42384022) completely answers the question way before your comment. A simple case statement.

Copied my methodology of using a table structure to assign the values rather than an inline CASE statement or other hack.

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

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.