Sql Order By

[FreeriderUK]

Hi all,

I have a customer report I want to generate. I have a field for each customer called "sequence" in which I put a number (although some customers will have this field blank)

Using the SQL ORDER BY statement, how can I get the report to sort by "sequence" first, then have those without this value afterwards?

What I'm getting (understandably) is:

ORDER BY Sequence, Address

AAA -
BBB -
CCC -
FFF - 1
EEE - 2

But I want this:

FFF - 1
EEE - 2
AAA -
BBB -
CCC -

I'm sure I have done this before - maybe in Access report, but can't remember how.
Any ideas?

 

[jmcilhinney]

You'd have to fill the DataTable with two queries because NULL is less than anything else. Get the records where the column is not NULL first, then get the records where it is NULL.

 

[cjard]

Using the SQL ORDER BY statement, how can I get the report to sort by "sequence" first, then have those without this value afterwards?

use ISNULL to replace the null value with soemthing really huge:

ORDER BY ISNULL(sequence, 99999999)

All blanks will be replaced by 99999999, which should be numerically after everything else. Note you DO NOT SELECT this, just ORDER BY it. You can order by anything, whether you selected it or not

 

[FreeriderUK]

You'd have to fill the DataTable with two queries

Sounds reasonable, but how?

All blanks will be replaced by 99999999, which should be numerically after everything else.

Wouldn't that look a bit messy? - If I could get it to work that is! I get the error:
Wrong number of arguments used with function in query expression 'ISNULL(SequenceID, 99999999)'.

 

[cjard]

Wouldn't that look a bit messy?

No. Did you miss the part of my post where I said you arent selecting this value, just ordering by it?

SELECT
firstname
FROM
person
ORDER BY
birthday

All people appear in birthday order but birthday is nowhere in sight! YOU DONT HAVE TO SELECT SOMETHING JUST SO YOU CAN ORDER BY IT.

The query i gave will select blanks, but order them after everything else

Wrong number of arguments used with function in query expression 'ISNULL(SequenceID, 99999999)'.

Erm, well you dont say what database system youre using (which is always a big help) so I assumed it was SQL Server

Here is the help file for SQL Server's ISNULL:
http://technet.microsoft.com/en-us/library/ms184325.aspx

If you dont use SQL Server, you will have to find the equivalent in whatever DB you use. In oracle it is NVL. I dont know what it is elsewhere.

When posting database queries in future, please always include the name and version of your RDBMS, and also maybe consider putting the post in the dedicated database section

 

[FreeriderUK]

No. Did you miss the part of my post where I said you arent selecting this value, just ordering by it?

No, but when you say:

All blanks will be replaced by 99999999

I took it literally and thought I would get this:

FFF - 1
EEE - 2
AAA - 99999999
BBB - 99999999
CCC - 99999999

Erm, well you dont say what database system youre using (which is always a big help) so I assumed it was SQL Server

I'm sure I have done this before - maybe in Access report, but can't remember how.

Too subtle?

I'm using Access database as it's what I know.

 

[giadich]

You can break it into two queries and do Union All to combine the results together.

Although, it's not a preferred solution, it'll work just fine and get you up and running in no time while you search for the other solution.

 

[cjard]

No, but when you say:
I took it literally and thought I would get this:

FFF - 1
EEE - 2
AAA - 99999999
BBB - 99999999
CCC - 99999999

In databases, you get out what you put in the select list..
Anything else, anywhere else, behaves as it should for the relevant section where it is.. Meaning: you can order by something you dont select, and you can write whatever you want in the order by section; it's used to order the rows, not to display them

Think of SQL queries as commands that operate on one block of data, transforming it into another block:

SELECT
  (defines what goes to form the output block)
FROM
  (blocks of data go here as the input)
ORDER BY
  (commands go here to shape/order the data. they dont change what is output)

Too subtle?

Yes. Be more direct when dealing with nerds like myself

I'm using Access database as it's what I know.

OK, well I googled ISNULL in access and it merely says whether a field is null or not, true false. It doesnt replace the null with a value, unlike ISNULL in SQl Server or NVL in oracle

Hence our query becomes:

SELECT
  whatever
FROM
  wherever
ORDER BY
  IIF( ISNULL(field_might_be_null), 99999999, field_might_be_null)

Last line in english:

order by (if it IS null then put 99999, otherwise put the actual number itself)



Hope this helps your understanding of SQL!

 

[FreeriderUK]

Thanks cjard! It works!

Hope this helps your understanding of SQL!

It does - a little - but I have a long way to go!

I'm looking in to using SQL Server Express, so expect more questions...

Thanks again.

 

[Arg81]

personally I've said to people if they are wanting to do an application that uses a lot of queries, including parameterised and complex sorting ones, is to use SQL Server Express instead of Access. It's far more easier to understand and write the queries than having to try and search for the Access ones.