December 12, 2009 at 6:54 am
Hello everyone. I'm new to the forum and a beginner in SQL too...
Hope someone have an answer for my question.
I have a table (example) with 2 columns
Tom 0
John 2
Paul 1
Jack 3
Tony 0
Rick 0
Joe 4
I wish to add to my asp page a sql query saying ORDER BY
but if I do it will come:
Tom 0
Tony 0
Rick 0
Paul 1
John 2
Jack 3
Joe 4
while I want it come as:
Paul 1
John 2
Jack 3
Joe 4
Tom 0
Tony 0
Rick 0
with 0 at the bottom, but still in an ASC way.
Is there a way to exclude 0 and put it at the end? I know there's a ORDER BY number IS NOT NULL but it's not my case since the value is not null, is 0.
I thank in advance anyone able to help 🙂
December 12, 2009 at 9:27 am
Here's what I'd do.
Write a query for everything but 0, so
select name, score, 'A' as 'OrderingField'
from Table
where score > 0
order by score
then union in the zeros.
select name, score, 'A' as 'OrderingField'
from Table
where score > 0
union
select name, score, 'B' as 'OrderingField'
from Table
where score = 0
order by OrderingField, score
And order by your "fake" field. Now be sure that you don't display the fake field.
December 12, 2009 at 9:57 am
use case in your order by clause
order by case when column =0 then 1 else 0 end asc, column
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply