Обсуждение: VIEW / ORDER BY + UNION
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
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.
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:\>
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