BUG: in SELECT DISTINCT - Mailing list pgsql-bugs

This may be a bug:

select distinct on location nlid,location,postedon,title from newsletter
order by postedon,location desc limit 12

    does not actually return a list of entries that are ordered first by the
date they were posted, but distinct on the "location" variable.

The results of this select are as follows:

nlid|location       | postedon|title
----+---------------+---------+---------------------------------------------
--
   9|R-TIB          |932065380|Network Launch
  11|C-AFG          |932067219|Status 1999
  12|C-IND          |932067351|The causes of persecution
  15|C-NPL          |932067538|Church Growing Rapidly in Nepal
  17|C-CHN          |932067641|New information & opportunities
  18|C-ALB          |932067684|New articles about Albania
  19|C-ERI          |932067769|Welcome to Eritrea!
  20|C-RUS          |932067821|Is Russia Y2K ready?
  24|P-2021         |932069031|Welcome to the Uighur
  25|P-1675         |932069111|Welcome to the Nara
  26|P-1704         |932069160|Welcome to the Afar!
  33|F-2            |932498871|WELCOME TO THE NORTH AMERICAN CCMN FORUM
PAGE!
  42|F-4            |933013973|<B>A MILLENNIAL TRIBUTE TO JESUS (Issue
#1)</B>
  43|C-IND          |933017367|Conditions for 300 Million Indian Children
  44|R-TIB          |933017654|Macao - the City of God or the City of Sin?

Note that R-TIB occurrs both at the top & at the bottom, because the
"location" column is not the first in the order grouping. But, if you do it
this way:

select distinct on location nlid,location,postedon,title from newsletter
order by location,postedon desc limit 12

Thenw hat I get is this:

nlid|location       | postedon|title
----+---------------+-------------------------------------------------------
------------------------------------------
 150|A-             |936895571|Straight Line - #06
 163|C-AFG          |937321006|9/13 UN lashes Taliban over poor human rights
record
  18|C-ALB          |932067684|New articles about Albania
 122|C-BTN          |936018559|Dzalakha of Bhutan Untargeted from Joshua
Project 2000 List
  17|C-CHN          |932067641|New information & opportunities
  63|C-CUB          |933174509|Cuban Evangelical Celebration
  19|C-ERI          |932067769|Welcome to Eritrea!
  13|C-IND          |932067399|Can "Hindutva" survive the persecution of
Christians in India?
 113|C-IRN          |935081148|Welcome 8/19/99
  80|C-MNG          |933536089|Number of Believers Growing Rapidly in
Mongolia
  15|C-NPL          |932067538|Church Growing Rapidly in Nepal
  20|C-RUS          |932067821|Is Russia Y2K ready?
 173|C-UKR          |938458055|Welcome 27/09/99
 105|C-YEM          |934577345|FYI
  67|F-1            |933350785|Click on any document listed below or the
documents tab at the left for a ...

Note that here while they are unique, they are not ordered according to
postedon. I suppose that I could do a SELECT INTO but that eats up a lot of
time. I'm trying to dynamically generate this list for a web page... It'd be
nice if the DISTINCT ON routine would return a list of distinct rows
regardless of the order, somehow...


Never retreat. Never surrender. Never cut a deal with a dragon.
_______________________________________________________________
Justin Long                       Executive Producer
616 Station Square Ct             http://www.strategicnetwork.org
Chesapeake, VA 23320              Network for Strategic Missions
JustinLong@strategicnetwork.org    977 Centerville Trnpk CSB 317
757-226-5011                Va Beach, VA 23463, USA