Thread
-
BUG: in SELECT DISTINCT
Justin Long <justinlong@strategicnetwork.org> — 1999-10-05T14:28:40Z
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