pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVIEW / ORDER BY + UNION
5+ messages / 4 participants
[nested] [flat]
* VIEW / ORDER BY + UNION
@ 2005-02-17 15:46 WeiShang <thanks@verymuch.com>
0 siblings, 2 replies; 5+ messages in thread
From: WeiShang @ 2005-02-17 15:46 UTC (permalink / raw)
To: pgsql-sql
Hi, I have created a view like this :
CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where
t1.orderno=t2.orderno);
if I create a SQL statment:
(SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno)
UNION
(SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno)
UNION
(SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno);
Will the whole result will be sorted by the field orderno?
Thanks,
WeiShang
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: VIEW / ORDER BY + UNION
@ 2005-02-18 08:26 CoL <col@mportal.hu>
parent: WeiShang <thanks@verymuch.com>
1 sibling, 0 replies; 5+ messages in thread
From: CoL @ 2005-02-18 08:26 UTC (permalink / raw)
To: pgsql-sql
hi,
WeiShang wrote, On 2/17/2005 16:46:
> Hi, I have created a view like this :
>
> CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where
> t1.orderno=t2.orderno);
>
> if I create a SQL statment:
>
> (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno)
> UNION
> (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno)
> UNION
> (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno);
>
> Will the whole result will be sorted by the field orderno?
nothing says it will. you havet to sort the set of unions.
(select orderno ) union (select orderno ) order by orderno.
Not necessary now (no sense), to use order by in selects inside union.
C.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: VIEW / ORDER BY + UNION
@ 2005-02-23 17:19 Bruno Wolff III <bruno@wolff.to>
parent: WeiShang <thanks@verymuch.com>
1 sibling, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2005-02-23 17:19 UTC (permalink / raw)
To: WeiShang <thanks@verymuch.com>; +Cc: pgsql-sql
On Thu, Feb 17, 2005 at 23:46:59 +0800,
WeiShang <thanks@verymuch.com> wrote:
> Hi, I have created a view like this :
>
> CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where
> t1.orderno=t2.orderno);
>
> if I create a SQL statment:
>
> (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno)
> UNION
> (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno)
> UNION
> (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno);
>
> Will the whole result will be sorted by the field orderno?
If this isn't a made up example, you don't want to do this. You
should use IN or OR to select records corresponding to the days
of interest and then use ORDER BY to select the ordering.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: VIEW / ORDER BY + UNION
@ 2005-02-23 18:33 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 1 reply; 5+ messages in thread
From: KÖPFERL Robert @ 2005-02-23 18:33 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Otherwise you can treat this as a subselect and suround it with another
select.
Like
select * from (<here goes your ex.>) order by orderno;
C:\> -----Original Message-----
C:\> From: Bruno Wolff III [mailto:bruno@wolff.to]
C:\> Sent: Mittwoch, 23. Februar 2005 18:20
C:\> To: WeiShang
C:\> Cc: pgsql-sql@postgresql.org
C:\> Subject: Re: [SQL] VIEW / ORDER BY + UNION
C:\>
C:\>
C:\> On Thu, Feb 17, 2005 at 23:46:59 +0800,
C:\> WeiShang <thanks@verymuch.com> wrote:
C:\> > Hi, I have created a view like this :
C:\> >
C:\> > CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where
C:\> > t1.orderno=t2.orderno);
C:\> >
C:\> > if I create a SQL statment:
C:\> >
C:\> > (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno)
C:\> > UNION
C:\> > (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno)
C:\> > UNION
C:\> > (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno);
C:\> >
C:\> > Will the whole result will be sorted by the field orderno?
C:\>
C:\> If this isn't a made up example, you don't want to do this. You
C:\> should use IN or OR to select records corresponding to the days
C:\> of interest and then use ORDER BY to select the ordering.
C:\>
C:\> ---------------------------(end of
C:\> broadcast)---------------------------
C:\> TIP 7: don't forget to increase your free space map settings
C:\>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: VIEW / ORDER BY + UNION
@ 2005-02-23 19:44 Bruno Wolff III <bruno@wolff.to>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2005-02-23 19:44 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
On Wed, Feb 23, 2005 at 19:33:07 +0100,
KÖPFERL Robert <robert.koepferl@sonorys.at> wrote:
>
> Otherwise you can treat this as a subselect and suround it with another
> select.
>
> Like
> select * from (<here goes your ex.>) order by orderno;
That is still a bad idea in this case. He will end up scanning the table
three times to pick up the three days and there will be a sort for each
union to remove duplicates (which there shouldn't be if orderno is a
candidate key).
>
> C:\> -----Original Message-----
> C:\> From: Bruno Wolff III [mailto:bruno@wolff.to]
> C:\> Sent: Mittwoch, 23. Februar 2005 18:20
> C:\> To: WeiShang
> C:\> Cc: pgsql-sql@postgresql.org
> C:\> Subject: Re: [SQL] VIEW / ORDER BY + UNION
> C:\>
> C:\>
> C:\> On Thu, Feb 17, 2005 at 23:46:59 +0800,
> C:\> WeiShang <thanks@verymuch.com> wrote:
> C:\> > Hi, I have created a view like this :
> C:\> >
> C:\> > CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where
> C:\> > t1.orderno=t2.orderno);
> C:\> >
> C:\> > if I create a SQL statment:
> C:\> >
> C:\> > (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno)
> C:\> > UNION
> C:\> > (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno)
> C:\> > UNION
> C:\> > (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno);
> C:\> >
> C:\> > Will the whole result will be sorted by the field orderno?
> C:\>
> C:\> If this isn't a made up example, you don't want to do this. You
> C:\> should use IN or OR to select records corresponding to the days
> C:\> of interest and then use ORDER BY to select the ordering.
> C:\>
> C:\> ---------------------------(end of
> C:\> broadcast)---------------------------
> C:\> TIP 7: don't forget to increase your free space map settings
> C:\>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-02-23 19:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-17 15:46 VIEW / ORDER BY + UNION WeiShang <thanks@verymuch.com>
2005-02-18 08:26 ` CoL <col@mportal.hu>
2005-02-23 17:19 ` Bruno Wolff III <bruno@wolff.to>
2005-02-23 18:33 Re: VIEW / ORDER BY + UNION KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-02-23 19:44 ` Bruno Wolff III <bruno@wolff.to>
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