SQLTeam.com | Weblogs | Forums

Need help in sql sorting

sql2008

#1

I have a difficulty in sorting the data,i am applying order by clause on two columns concatenated together and the results are displayed without considering the hyphen(-) symbol in resultset.
for example :
i have values in two column as
first Column second column
a a
-b a
c a

when apply order by as
select [first Column] + [second column]
order by [first Column] + [second column] desc

the result is shown as follows
aa
-ba
ca

here the hyphen symbol is neglected
but i want to consider the hyphen symbol
and need the result as

aa
ca
-ba

i tried to get any documents related to it on google but no help
can anyone can help me on this. i am using sql server 2008.

Thanks in advance


#2

This order by clause did it for me:

order by case when LEFT([first Column],1) = '-' then 1 end
    , [first Column], [second column]

#3

thank you gbritton for your reply.
But i need to concat two column values as one and then apply sorting on it,what you have suggested is for sorting on multiple columns separately.
And more ever my column contain russian characters and i tried with collate sql_latin1_general_cp1_ci_as to sort the values but no use.i am unable to figure out why does the hyphen is ignored during sorting.


#4

Actually, you don't need to concat the columns and sort on that. What you need is to produce the output in a certain order. The code I posted will do that. If you sort on concatenated columns, you may get different results. but with your example data, it doesn't matter. If you like, change

[first Column], [second column]

to

[first Column] + [second column]

in the code I posted and results are the same


#5

Or, even this would be sufficient.

ORDER BY firstColumn, secondColumn

In fact, this might be better to achieve the correct sorting if some of the values happen to be nulls.


#6

Presumably the concatenated string order does matter to the O/P ...

CREATE TABLE #TEMP
(
	[Sequence]	int,
	[FirstCol]	varchar(10),
	[SecondCol]	varchar(10)
)

INSERT INTO #TEMP
	SELECT 1, 'AA', 'AA' UNION ALL
	SELECT 2, 'AB', 'AA' UNION ALL
	SELECT 3, 'AB', 'BB' UNION ALL
	SELECT 4, 'ABA', 'AA'

SELECT	*
FROM	#TEMP
ORDER BY FirstCol, SecondCol

SELECT	*
FROM	#TEMP
ORDER BY FirstCol + SecondCol
GO
DROP TABLE #TEMP
GO

Definitely not the same outcome ...

Sequence    FirstCol   SecondCol  
----------- ---------- ---------- 
1           AA         AA
2           AB         AA
3           AB         BB
4           ABA        AA

Sequence    FirstCol   SecondCol  
----------- ---------- ---------- 
1           AA         AA
2           AB         AA
4           ABA        AA
3           AB         BB

#7

Good point Kristen.


#8

A pedantic point for sure ... :slight_smile:


#9

Not pedantic. It's actually what I wanted the OP to think about. Using the sample data, concatenation doesn't make a difference (except for that pesky leading hyphen -- whatever that is). of course, if your "real" data contains strings of length > 1, there are differences indeed. Consider a list of names:

Julian Elson
Julia Nelson

if you concatenate these, they are equivalent (except for case). However, most folks would want to see the names sorted either:

Julia Nelson
Julian Elson

(by first name)

or

Julian Elson
Julia Nelson

(by last name).

Rare indeed would be a requirement where you sorted by the concatenated names. I suspect many human readers would not be able to see any ordering at all were that used.


#10

I was assuming that the O/P wanted to sort by concatenated values specifically for the side-effect that that has. Might be that isn't the case though!! and ordering by the individual columns would produce the actual result required.

For sure sorting by individual columns will perform better than a concatenation ...


#11

Thank you all for your valuable answers and suggestions. :blush:

Sorting by individual columns is better than a concatenation but in my case i have to concatenate and then apply sorting...

The issue was with the collation which i used to sort concatenated column values.
Using ( Latin1_General_BIN ) resolved my issue. :blush:


#12

Good point james.It slipped out of my mind.
Thank you


#13

I am only happy to take all the credit :smiley: but it was Kristen who pointed out the flaw in my method.


#14

... I'm happy just to set them up :smiley: