Re: [GENERAL] What is *wrong* with this query???

[Tair Sabirgaliev]

On Sat, Nov 5, 2011 at 10:51 AM, Steve Murphy smur...@intorrent.com wrote:


 I give! I'm flummoxed!



 Here is what I have, 3 tables:



 schedule

 company    building   status0

 3 x   active

 4 x   active

 5 x   active

 3 x   active

 3 x   active

 3 x   active



 In the end, I want to replace the building id's above. They start out with
 the non-informative value of '1';



 company

 id  name   status

 3 x   active

 4     y   active

 5     z   active





 building

 id company   name

 1   3 A   active

 2   3 B   active

 3   3 C   active

 4   4 D   active

 5   4 E   active

 6   4 F   active

 7   5 G   active

 8   5 H   active

 9   5 I    active



 So, every company has 3 schedules. Of the 3, I'd like to select the one with
 the lowest id.



 I'm using postgresql 8.1. Yes, ancient, but I have no choice in this affair.



 select schedule.id as sched_id, bld.id as bid

     from

 schedule

     left join company on schedule.company = company.id

     left join (select * from building where building.company =
 company.id order by id limit 1)  as bld

     where

     schedule.status = 'active' and company.status = 'active' and
 bld.status = 'active';



 I get a syntax error on the the where.



 ERROR:  syntax error at or near where at character ….

 LINE 6: where

    ^



 If I leave out the where clause entirely, that's an error also,

 ERROR:  syntax error at or near ; at character ….

 LINE 5:  …  as bld ;

  ^  it's right under the semicolon



 So, it's expecting ***SOMETHING*** after the as bld, but it sure isn't
 going to tell me what.

 What am I missing?

left join (select * from building where building.company = company.id
order by id limit 1)  as bld on (company.id = bld.company)










-- 
с уважением,
Таир Сабыргалиев
ТОО BEE Software
Республика Казахстан, 01
г.Астана, ул.Сарайшык 34, ВП-27
Тел.: +7 (7172) 56-89-31
Сот.: +7 (702) 2173359
e-mail: tair.sabirgal...@bee.kz
Tair Sabirgaliev
BEE Software Ltd.
Republic of Kazakhstan, 01
Astana, Sarayshyk str. 34, sect. 27
Tel.: +7 (7172) 56-89-31
Mob.: +7 (702) 2173359
e-mail: tair.sabirgal...@bee.kz

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Distinct on a non-sort column

[Tair Sabirgaliev]

On Sun, Nov 6, 2011 at 12:39 AM, Cstdenis li...@on-track.ca wrote:
 I am trying to write a query that selects recent submissions (sorted by
 submission_date) but only selects the most recent one for each user_id.

 example query: select distinct on (user_id) * from stories order by
 date_submitted desc limit 10;

 However postgres will not allow me to filter out duplicate rows with
 distinct unless I sort on that column, which would product useless results
 for me. Group by seems to have similiar problems, plus the additional
 problem of wanting aggregate functions to be used.

 I even tried sorting in a subquery, but it still comes out sorted by
 user_id: select distinct on (user_id) * from stories where sid in (select
 sid from stories order by date_submitted desc limit 10);


 How can I work around this limitation to get the results sorted the way I
 want, then have the duplicates removed? I can't be the only one running into
 this limitation, there must be some workaround.


assuming date_submitted are unique for user_id:
select * from stories s, (select user_id, max(date_submitted) d from
stories group by user_id) ss where s.user_id = ss.user_id and
s.date_submitted = ss.date_submitted;


-- 
с уважением,
Таир Сабыргалиев
ТОО BEE Software
Республика Казахстан, 01
г.Астана, ул.Сарайшык 34, ВП-27
Тел.: +7 (7172) 56-89-31
Сот.: +7 (702) 2173359
e-mail: tair.sabirgal...@bee.kz
Tair Sabirgaliev
BEE Software Ltd.
Republic of Kazakhstan, 01
Astana, Sarayshyk str. 34, sect. 27
Tel.: +7 (7172) 56-89-31
Mob.: +7 (702) 2173359
e-mail: tair.sabirgal...@bee.kz

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general