On Mon, Jul 25, 2005 at 07:06:46PM -0300, Marc G. Fournier wrote: > > > On Mon, 25 Jul 2005, Jim C. Nasby wrote: > > >On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote: > >> > >>Just curious as to whether or not a warning or something should be issued > >>in a case like: > >> > >> SELECT c.* > >> FROM company c, company_summary cs > >> WHERE c.id = cs.id > >> AND cs.detail = 'test' > >>ORDER BY cs.fullname; > >> > >>Unless I'm missing something, the ORDER BY clause has no effect, but an > >>EXPLAIN shows it does take extra time, obviously ... > > > >Uh, I'd hope it had an effect. Note that RDBMSes have been moving > >towards allowing fields in ORDER BY that aren't in the SELECT list, > >though in the past it was common that anything in ORDER BY had to also > >be in SELECT. > > 'k, in the test case I've been working with, the query always returns 1 > row, so my test case wouldn't have shown a difference ... but, if it does > have an affect, how? The ORDER BY is on the final result set, and if > there is no cs.fullname in that result, what exactly is it ordering? decibel=# select usename, usesysid from pg_user; usename | usesysid ----------+---------- postgres | 1 decibel | 100 (2 rows)
decibel=# select usesysid from pg_user order by usename; usesysid ---------- 100 1 (2 rows) decibel=# explain analyze select usesysid from pg_user order by usename; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Sort (cost=1.02..1.02 rows=1 width=68) (actual time=0.182..0.183 rows=2 loops=1) Sort Key: pg_shadow.usename -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 width=68) (actual time=0.060..0.077 rows=2 loops=1) Total runtime: 0.518 ms (4 rows) decibel=# -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org