Order by, displaying results out of order.

Question

Post reply

Order by, displaying results out of order.

Jorge Lopes

Grasshopper

Points: 22
More actions
November 28, 2011 at 3:43 am

#260597

CREATE TABLE #desc
( NAME VARCHAR(100))
INSERT INTO #desc
SELECT 'Balanço - Activo não corrente' UNION ALL
SELECT 'Balanço - Autonomia Financeira' UNION ALL
SELECT 'Balanço ANO'
SELECT * FROM #desc ORDER BY NAME
DROP TABLE #desc
Can same one explain to me why I’m a gating this output
Balanço - Activo não corrente
Balanço ANO
Balanço - Autonomia Financeira
Instead of:
Balanço - Activo não corrente
Balanço - Autonomia Financeira
Balanço ANO
I’m executing this script in:
Microsoft SQL Server 2008 (SP2) - 10.0.4285.0 (X64)
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Please check your Server’s collation.

SQL Server Collation Fundamentals

http://msdn.microsoft.com/en-us/library/aa174903(v=SQL.80).aspx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Character position 9 of two of your strings is a hyphen. There are ways to get around this, for instance you could remove the hyphen and a white space using REPLACE.

Changing the collation won't put an alpha between two hyphens:-P

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DROP TABLE #desc

CREATE TABLE #desc

( NAME VARCHAR(100))

INSERT INTO #desc

SELECT 'Balanço- Activo não corrente' UNION ALL -- note changed position of hyphen

SELECT 'Balanço - Autonomia Financeira' UNION ALL

SELECT 'Balanço ANO'

SELECT

NAME,

REPLACE(REPLACE(Name,'- ',' '),' ',' ')

FROM #desc ORDER BY REPLACE(REPLACE(Name,'- ',' '),' ',' ')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

Balanço - Activo não corrente -- 1
Balanço ANO -- 2
Balanço - Autonomia Financeira -- 3

I didn't check the character position but based on you comment, it should appear in this order...

Either

1/3

3/1

2

OR

2

1/3

3/1

Jorge Lopes Grasshopper Points: 22 More actions · Answer 5

There is a slight problem in the third Select statement you have to put 2 spaces between Balanço and ANO

SELECT 'Balanço[space][space]ANO'

CREATE TABLE #desc

( NAME VARCHAR(100))

INSERT INTO #desc

SELECT 'Balanco - Activo nao corrente' UNION ALL

SELECT 'Balanco - Autonomia Financeira' UNION ALL

SELECT 'Balanco ANO'

SELECT * FROM #desc ORDER BY NAME

DROP TABLE #desc

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jorge Lopes (11/28/2011)
There is a slight problem in the third Select statement you have to put 2 spaces between Balanço and ANO
SELECT 'Balanço[space][space]ANO'
CREATE TABLE #desc
( NAME VARCHAR(100))
INSERT INTO #desc
SELECT 'Balanco - Activo nao corrente' UNION ALL
SELECT 'Balanco - Autonomia Financeira' UNION ALL
SELECT 'Balanco ANO'
SELECT * FROM #desc ORDER BY NAME
DROP TABLE #desc

It had to be that or a space missing on one of the other two rows. Have you tried using REPLACE() yet?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden