pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[SQL] Subselect has too many or too few fields
2+ messages / 2 participants
[nested] [flat]
* [SQL] Subselect has too many or too few fields
@ 1999-03-25 16:52 Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 2+ messages in thread
From: Oleg Bartunov @ 1999-03-25 16:52 UTC (permalink / raw)
To: pgsql-sql
I have a problem with query I thought must works:
c_flats=> select source, max(up_date) as max_update, count(*) from flats where u
up_date in (select max(up_date) from flats group by source) group by source;
ERROR: parser: Subselect has too many or too few fields.
Internal subselect produces as expected:
c_flats=> select max(up_date) from flats group by source;
max
----------
15-03-1999
11-03-1999
24-03-1999
24-03-1999
24-03-1999
And I don't see a reason my query doesn't works.
If I substite the result of internal subselect I get what I need (there are
cyrillic characters in <source>)
c_flats=> select source, max(up_date) as max_update, count(*) from flats where u
p_date in ('15-03-1999','11-03-1999','24-03-1999','24-03-1999','24-03-1999') gro
up by source;
source |max_update|count
--------------------------+----------+-----
ИНКОМ 369-00-25, 365-10-38|15-03-1999| 674
Корнет 912-00-12 |11-03-1999| 47
МИАН 974-62-62 |24-03-1999| 393
МИЭЛЬ 217-30-20, 217-39-13|24-03-1999| 1
МЦБН <null> |24-03-1999| 141
(5 rows)
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 2+ messages in thread
* RE: [SQL] Subselect has too many or too few fields
@ 1999-03-25 21:39 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jackson, DeJuan @ 1999-03-25 21:39 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; pgsql-sql
What are the results without the subselect?
It looks like they will be what you are after.
The problem is that you are grouping by a column that isn't in your
specified result set. The way PostgreSQL deals with this little problem is
by adding it to the retrieved rows, then using it in the group by and
aggregating. Then just not displaying it on the print.
DEJ
> -----Original Message-----
> From: Oleg Bartunov [mailto:oleg@sai.msu.su]
> Sent: Thursday, March 25, 1999 10:53 AM
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] Subselect has too many or too few fields
>
>
> I have a problem with query I thought must works:
>
> c_flats=> select source, max(up_date) as max_update, count(*)
> from flats where u
> up_date in (select max(up_date) from flats group by source)
> group by source;
> ERROR: parser: Subselect has too many or too few fields.
>
> Internal subselect produces as expected:
> c_flats=> select max(up_date) from flats group by source;
> max
> ----------
> 15-03-1999
> 11-03-1999
> 24-03-1999
> 24-03-1999
> 24-03-1999
>
>
> And I don't see a reason my query doesn't works.
> If I substite the result of internal subselect I get what I
> need (there are
> cyrillic characters in <source>)
>
> c_flats=> select source, max(up_date) as max_update, count(*)
> from flats where u
> p_date in
> ('15-03-1999','11-03-1999','24-03-1999','24-03-1999','24-03-1999') gro
> up by source;
> source |max_update|count
> --------------------------+----------+-----
> ИНКОМ 369-00-25, 365-10-38|15-03-1999| 674
> Корнет 912-00-12 |11-03-1999| 47
> МИАН 974-62-62 |24-03-1999| 393
> МИЭЛЬ 217-30-20, 217-39-13|24-03-1999| 1
> МЦБН <null> |24-03-1999| 141
> (5 rows)
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-03-25 21:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-25 16:52 [SQL] Subselect has too many or too few fields Oleg Bartunov <oleg@sai.msu.su>
1999-03-25 21:39 RE: [SQL] Subselect has too many or too few fields Jackson, DeJuan <djackson@cpsgroup.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox