force an alphabetical sort over this column

Question

is there a way to force an alpha sort over this "column1" except last which appears last? sorting by no other fields

column1

aa

bb

cc

zz

last

Answer 1

try

select column1 from 
(
select 'aa' column1 union
select 'bb' union
select 'cc' union
select 'zz' union
select 'last '
) tbl1
 order by case when column1='last' then 1 else 0 end 

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Hi, try this:

select column1
from table_name
order by case column1 when 'last' then 'ZZZZZZZ' else column1 end

David.

Answer 3

I am not certain

A) You want everything to be ordered alphnumerically, except the highest index/last element wich should be last in the result

B) You want every field to be ordered alphanumerically, except the the row containing 'last' as string, wich should be the last

Answer 4

try

select column1 from 
(
select 'aa' column1 union
select 'bb' union
select 'cc' union
select 'zz' union
select 'last '
) tbl1
 order by case when column1='last' then 1 else 0 end 

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Thsi one might fail:

DECLARE @TempTable TABLE (Value CHAR(4));
INSERT INTO @TempTable (Value) VALUES
	('AA'),
	('BB'),
	('CC'),
	('Last'),
	('EE'),
	('DD')

SELECT Value FROM @TempTable
 order by case when Value LIKE 'last' then 1 else 0 end 

Result:

Value
AA  
BB  
CC  
EE  
DD  
Last

Answer 5

Hi, try this:

select column1
from table_name
order by case column1 when 'last' then 'ZZZZZZZ' else column1 end

David.

This seems to be ASCII, not Alphanumeric sort:

http://www.dotnetperls.com/alphanumeric-sorting

DECLARE @TempTable TABLE (Value CHAR(4));

INSERT INTO @TempTable (Value) VALUES
	('100F'),
	('last'),
	('50F')

select Value
from @TempTable
order by case Value when 'last' then 'ZZZZZZZ' else Value end

But then again, Ascii sort might be what is intended.

Answer 6

@Christopher84

I am getting this result..

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 7

Exaclty what I got. The order:

CC
EE
DD

is not right. It should be:

CC
DD
EE

Answer 8

@Christopher84

My understanding of the request was, the OP want to place the records that contain the word 'Last' at the end of  the result set..

try this then..

declare @TempTable table(Value varchar(10))
INSERT INTO @TempTable (Value) VALUES
	('AA'),
	('BB'),
	('CC'),
	('Last'),
	('EE'),
	('DD')

SELECT Value FROM @TempTable
 order by value,case when Value= 'last' then 1 else 0 end 
 

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 9

Exaclty what I got. The order:

CC
EE
DD

is not right. It should be:

CC
DD
EE

Can you try this for your table

select col from @tbl  where col <> 'last'
union
select col from @tbl  where col = 'last'

Murali

Answer 10

If it is going to work under all circumstances, produce a real Alphanumeric sort (not ASCII sort) and put "specified string" always last it has to go against this:

DECLARE @TempTable TABLE (Value CHAR(4));

INSERT INTO @TempTable (Value) VALUES
	('last'),
	('EE'),
	('DD'),
	('last'),
	('CC'),
	('BB'),
	('AB13'),
	('AB2'),
	('AB1')

And produce this (I hope that is right):
AB1
AB2
AB13
BB
CC
DD
EE
last
last

Answer 11

SELECT    Value
FROM      @TempTable
order by  case when Value LIKE 'last' then 1 else 0 end,
          value

---

N 56°04'39.26"
E 12°55'05.63"

Answer 12

select col from @tbl  where col <> 'last'

union
select col from @tbl  where col = 'last'

Actually taht was my first idea. And when using UNIN ALL (remember, nothing was said about the value being unique), we can cleary seperate the 'last' from the rest:

DECLARE @TempTable TABLE (Value CHAR(4));

INSERT INTO @TempTable (Value) VALUES
	('last'),('EE'),('DD'),('last'),('CC'),('BB'),('AB13'),('AB2'),
	('AB1')

select Value from @TempTable  where Value != 'last'
UNION ALL
select Value from @TempTable where Value = 'last'

However, if I rewrite it to this:

select Value from @TempTable  where Value != 'last' ORDER BY Value
UNION ALL
select Value from @TempTable where Value = 'last'

I get "Syntax error near UNION". Placing a ORDER BY only on the second works. Appaerntly ORDER BY and UNION only work if the ORDER BY is on the last SELECT.

Now I considered putting the result of the first, ordered SELECT into a local table variable, then UNION that variable with the 2nd Select. But I can't union a Variable with anything or anything with a Variable.

Answer 13

Yes. You would get the syntax error when you have an Order by clause before the Union. THe order by clause should be in the statement after the last union. But that would not work for ur requirement. Probably you can rewrite the query like this.

select value from

(select value from @TempTable where value <> 'last' order by value) a

union all

select value from @TempTable where value = 'last'

I have not tested it. But i believe that this should work

Answer 14

It isn't exaclty my requirement as I am not the OP, but I do want to figure out how to do something as simple as appending two result sets. Something so simple, I can't figure out a single reason it does not work in SQL. Must be a Microsoft thing.

As for paranthesis, they don't help here either:

(select Value from @TempTable  where Value != 'last' ORDER BY Value)
--and
(select Value from @TempTable  where Value != 'last')
UNION ALL
select Value from @TempTable where Value = 'last'

work of course. But:

(select Value from @TempTable  where Value != 'last' ORDER BY Value)
UNION ALL
select Value from @TempTable where Value = 'last'

Gives me "wrong syntax near order, expected ')', UNION or EXCEPT.

Answer 15

I believe we are getting the syntax error since we are using brackets around the main query. The brackets should be used only for the sub query...