Thread: changing null-values' sort order

unfortunately, standard 'nulls first/last' isn't supported yet.
to change behaviour you should use following statement:
select v_date, v_userid from votes order by v_date is not null desc, v_date desc

or, something like this:
select v_date, v_userid from votes order by coalesce(v_date, XXX) desc
where XXX is substituted by the date value that is smaller than any
other used in your table

On 20/11/05, me@alternize.com <me@alternize.com> wrote:
>
> hi list
>
> coming from the MS-SQL Server world, we're migrating our applications slowly
> to pgsql 8.1. now we thumbled on a problem with different sort-order
> behaviours:
>
> we got a table with dated records:
> table: votes (v_userid, v_date)
>
> the v_date field can either be null or a date in the past. in mssql, sorting
> the table to list records in descending date-order
>
> select v_date, v_userid from votes order by v_date desc
>
> produces a list where first the records with dates are listed, and then the
> ones with nulls. in pgsql, the null-records are listed first before the
> date-records... how can this behaviour be changed?
>
> mssql:
> --------
> 2005-11-05  3
> 2005-11-01  4
> 2005-09-10  1
> null  2
> null  5
>
>
> pgsql:
> --------
>
> null  2
> null  52005-11-05  3
> 2005-11-01  4
> 2005-09-10  1
>
> thanks in advance,
> thomas


--
Best regards,
Nikolay