SQL ORDER By - two fields

[lo5750ul]

I have a table with two columns of data (Data Type varchar and bit). I want to row sort the data by column two descending and then by column one ascending.

My SQL query thus far is:

SELECT *
FROM MyTable
ORDER BY column_two DESC, column_one

but the data is not sorted in column one.

I really need some help with this one.

Cheers.

 

[Descartes]

What do you mean it's not sorted in column_one? It sorts *first* by column_two, *then* by column_one. Both columns can't be sorted independently because you're sorting rows, not columns.

 

[Croda]

Ummm...unless I'm missing something, you can't sort by column_two in this case because you only have 2 columns. After ordering by column_one, there's nothing left to order. If you had a third column (column_three) you could

Order By column_one DESC, column_two

You can't sort by both columns if you only have 2 columns.

Imagine this.
Varchar Bit
A 1
L 0
D 1

Order By Column_one DESC gives:

L 0
D 1
A 1

You can't then go back and sort by Column_two because it would have to screw up your first sort on column_one.

Hope that made sense.

 

[Descartes]

<< Hope that made sense. >>



Yes, you said the same thing I did, but more verbosely

 

[L00PY]

Can I be picky and claim Descartes was right the first time, but wrong when he said Croda said the same thing?

You can a different result sorting the second column of a two column dataset.

Example:
A 0
A 1
D 1
L 0

Order By column_two DESC, column_one
A 1
D 1
A 0
L 0

Order By column_two DESC, column_one DESC
D 1
A 1
L 0
A 0


Something is still left to order after sorting the first column, it's just any subsequent ordering takes place after the initial ordering.

 

[Descartes]

<< Can I be picky and claim Descartes was right the first time, but wrong when he said Croda said the same thing? >>



Sure! Picky is good.

I still see that I said the same thing, but maybe that's all in my head. I simply tried to explain the fact that the second column in the order by cannot be sorted independently of the first.

 

[lo5750ul]

Hi guys,

Thank you for all your input. Let me tell you that none of you are right.

I want to sort a list like the one below so that is looks like the second one below. After doing quite a bit of research I found that the reason it won't work is because you can not do a sort properly with a bit Data Type. I changed my it to a TinyInt Data Type and my SQL worked.

B....1
B....0
A....1
C....1
D....0
C....0

B....0
C....0
D....0
A....1
B....1
C....1

 

[bevancoleman]

What SQL server are you using? It should tell you if it can't execute a SQL statment like that

 

[joohang]

I just played around with a sample table on my SQL 2000 Enterprise Manager.

Your original SQL statement seems to do exactly what you want. At least on my machine.

My data type was bit, fyi.