[thelist] SQL Help needed

rudy r937 at interlog.com
Thu Jan 31 20:33:01 CST 2002


> If "paiduntil" is not null, I want these results displayed first and
> sorted by "lastloggedon", then I want the records where "paiduntil"
> is null ordered by "lastloggenon"
>
> SELECT * FROM TableA
>   order by paiduntil, lastloggedon
>
>  Obviously the above statement is wrong.

hi kevin

it's not obvious that it's wrong, in fact it would be fine if nulls sorted
up last, but i think most databases always sort nulls first

you did not say which database you're running, so naturally some of the
answers you've been getting have been guesses

i'm going to further assume that you want the non-null paiduntils sorted
first by paiduntil, and then by lastloggedon -- otherwise, the solutions
which follow need to be revised slightly

>     SELECT this, that, and, the_other
>          FROM TableA
>     ORDER BY paiduntil DESC, lastloggedon DESC

this isn't quite right, but it does put the null paiduntils first --
unfortunately, the order of the non-nulls is the reverse of what you want

>    SELECT a,b,c FROM tableA
>    WHERE paidunitl is not null
>    ORDER BY lastloggedon
>    UNION
>    SELECT a,b,c FROM tableA
>    WHERE paidunitl is null
>    ORDER BY lastloggedon

this won't work, because a query can have only one ORDER BY

in the above example, the two SELECTS are subqueries, and only the "outer"
query, a so-called fullselect, can have an ORDER BY -- it's confusing, i
know, because placed where it usually is, the ORDER BY looks like it
belongs to the second subselect

but it was a good guess, and shows that usually there are several ways to
look at a problem

>  SELECT
>    ordering = CASE
>     WHEN paiduntil IS NULL THEN 1
>      ELSE 0
>   END,
>  *
>  FROM TableA
>  ORDER BY 1, lastloggedon

this is actually very inventive, and it embodies the germ of another good
idea -- do something to detect the nulls, and write the ORDER BY to sort on
whatever you detected

too bad the syntax isn't cross-database (not every database supports CASE)

a more conventional way to handle nulls is with the COALESCE function

here's one way to do it --

   SELECT COALESCE(paiduntil, '2937-12-31') as sortfield
           , paiduntil, lastloggedon
        FROM TableA
     ORDER BY by sortfield, lastloggedon

this gives you exactly the sequence you want, in one fairly efficient pass
of the data, but it assumes there are no paiduntil dates higher than
december 31, 2937

it's an ugly kludge, but it works fine  ;o)

another solution is to combine the idea of the 0/1 flag with the idea of
the subqueries --

     SELECT 0, * FROM TableA WHERE paiduntil IS NOT NULL
  UNION ALL
     SELECT 1, * FROM TableA WHERE paiduntil IS NULL
  ORDER BY
         1, paiduntil, lastloggedon

as with joshua's example, the "ORDER BY 1" tells the database to order by
the first column, so all the not null paiduntils will come up first

note that you would simply ignore the first column in the result set, just
as you would ignore the sortfield column in my COALESCE example

one final word --

somewhere, in some database, i remember seeing some syntax that lets you
specify that nulls sort last, but i'll be darned if i can remember where it
was

it was NOT microsoft's "books online" -- i have the utmost respect for
microsoft's sql/server product, and believe it to be an extremely robust
and reliable database, but golly gee, their documentation sucks big time

i mean, not two minutes into a search for "nulls and order by" i'm hip-deep
in crap like this --

    DBPROP_INDEX_NULLCOLLATION

    Description: SQLOLEDB does not support this property.
    Attempts to set the property in CreateIndex cause a
    DB_S_ERRORSOCCURED return value. The dwStatus
    member of the property structure indicates
    DBPROPSTATUS_BADVALUE

i know this has something to do with null collations, but i have no idea
what

this is why you need a MCSE to run your sql/server shop, and why i'll never
be one

sorry for the rant


i see i've taken so long to not find what i was looking for, that you've
already gotten the answer you wanted from somebody else

oh well, at least i had fun with COALESCE again...


rudy









More information about the thelist mailing list