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.
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.
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
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
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.
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 ...
but it was Kristen who pointed out the flaw in my method.