Sorting with column in which there are empty cells

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sorting with column in which there are empty cells

Author

Topic

mjkarlin
Starting Member

3 Posts

Posted - 2009-05-17 : 09:37:09

I have a table with a column in which the data in any given row is either text or empty. I would like to sort the table by this column so that all of the rows are sorted in ascending order but I want all the rows where the column on which I am sorting is empty to be pushed to the end. It's easier to give an example.

Unsorted

Student Date Coach
James 5/15 Robin
Anna 5/19 Carl
Helen 6/1
Phil 5/14
Bobby 5/18 Simone

If I sort with ORDER BY Coach, I get

Helen 5/14
Phil 6/1
Anna 5/19 Carl
James 5/15 Robin
Bobby 5/18 Simone

If I sort by ORDER BY Coach desc, I get

Bobby 5/18 Simone
James 5/15 Robin
Anna 5/19 Carl
Helen 6/1
Phil 5/14

But what I want is

Anna 5/19 Carl
James 5/15 Robin
Bobby 5/18 Simone
Helen 5/14
Phil 6/1

That is, Helen and Phil don't have a coach, so they should be pushed to the end.

So how do I get what I want? Do I need two queries or is there some simpler way?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 09:38:51

[code]SELECT * FROM YourTable ORDER BY ISNULL(NULLIF(Coach,''),'ZZZZZZZ') ASC[/code]

mjkarlin
Starting Member

3 Posts

Posted - 2009-05-17 : 20:12:53

Thanks. It worked beautifully. One further question. Assume I have a cffunction which was called as follows:

<cfset studentList=student.StudentDates(orderBy=url.orderBy,filled="yes")>

where student is a CFC component and StudentDates is a cffunction that contains the query.

I can put the code you suggest into the function easily enough by making the url.orderBy equal special. But it would be more elegant, would it not, if the code was a parameter of the hyperlink that the user clicks on at the top of the column. Something like:

<a href="studentdates.cfm?orderBy= [whatever the code should be that will be passed to the fundtion]")>Coaches</a>

If I simply put your code in, I get an error. Just curious - I can live with what you have given me.

Thanks once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 10:28:33

sorry i dont know much about cfc functions

mjkarlin
Starting Member

3 Posts

Posted - 2009-05-19 : 12:56:56

Thanks anyway. I checked with my ColdFusion guru and he liked the way I had handled the problem. So we're good!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 13:01:31

great