[RESOLVED] postgresql, CASE and DISTINCT/GROP BY problem

kemu_

After gotting the query working right as in thread: http://phpbuilder.com/board/showthread.php?t=10314834
i now noticed that news which have more than one reply, will show up twice (or actually so many times as there is replies). Ive tried using different DISTINCT and GROUP BY sentances, but i just cant seem to get it right anyways. Any1 have ideas?

select 
    *, 
    case when 
        r.dt is null 
        then '1900-01-01'::date 
    else 
        r.dt 
    end as fakedate 
from 
    news n 
left join 
    replies r 
on 
    (r.news_id=n.id) 
order by 
    fakedate desc;

this is the basic query i used...

Lars_Berg

select 
    <columns>, 
    max(case when 
        r.dt is null 
        then '1900-01-01'::date 
    else 
        r.dt 
    end) as fakedate 
from 
    news n 
left join 
    replies r 
on 
    (r.news_id=n.id) 
  group by <columns>
order by 
    fakedate desc

In general, don't use select * but list the specific columns you need.

Also, using

select  *,expression from t

is not standard SQL so it will not work in all DBMS.

kemu_

After fighing with the problem for a week, i still havent gotten solution to the problem. Lars's idea didnt work..

Lars_Berg

What do you mean by

"ddin't work"

kemu_

If i put all the selectable columns to the group by sentance, it still gives double rows from news' that have more than one reply. It should be "grouped by" only my news id, but it doesnt allow it... select distinct doesnt work properly either...

Lars_Berg

Could you post your current query, give some sample data and the expected result.

kemu_

SELECT u.header,u.leipateksti,u.kokoteksti,u.adddate,u.lisattyklo,k.etunimi,k.sukunimi 
FROM news u 
INNER JOIN kayttaja k ON k.id=u.kayttajaid 
LEFT JOIN replies v ON v.uutisid=u.id 
group by u.otsikko,u.leipateksti,u.kokoteksti,u.adddate,u.lisattyklo,k.etunimi,k.sukunimi,v.adddate
ORDER BY 
CASE when v.adddate IS NOT NULL 
THEN v.adddate
ELSE u.adddate END 
DESC LIMIT 10

I changed some of the parameters to english so you would understand the main columns.

As for data, in news table
id,header,time
1,foo,01-01-2006
2,bar,02-12-2005
3,test,01-12.2005

in replies table
id,newsid,reply
1,1,blah
2,1,second reply
3,2,first reply to seoncd thread
4,2,second

kemu_

hmm.. i finally got it working... i hadn't understood lars' answer properly in the first place 🙂

thx 😃