[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