Oninit cdi / Archive
Talk to an expert →

UNION AND ORDER BY

Posted in 2010

Topics: SQL Development & Query Writing

DHARMENDRA SHARMA — — source: IIUG Forums & Mailing Lists

Hi all, I am running a SELECT statement using UNION on Informix 11.5. It works okay. I need to make a minor change in ORDER BY clause to use a subscript of a text column. Is it possible? I know I can handle it by dropping the UNION clause and use "OR " clause but just wondering whether or not it is possible to use a subscript in ORDER BY clause with UNION. Here is a sample of my SQL query.. SELECT col1, col2, col3 FROM table1 where (....a where clause .....) UNION SELECT col1, col2, col3 FROM table1 where (....where clause...) ORDER BY 2,3 --- what I am looking is.. ORDER BY col2[6], col3. Since I can not use column name in this ORDER BY clause with UNION, how can I use subscript syntax alongwith number? I hope I provided enough information for what I am looking for..if not, please let me know.. Regards, Dharmendra

↪ replying to DHARMENDRA SHARMA
Everett Mills — — source: IIUG Forums & Mailing Lists

I don't have a system to test it on at the moment, but this should work: SELECT col1, col2, col3, col2[6] FROM table1 where (....a where clause .....) UNION SELECT col1, col2, col3, col2[6] FROM table1 where (....where clause...) ORDER BY 4,3 --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > DHARMENDRA SHARMA > Sent: Friday, September 17, 2010 2:05 PM > To: ids@iiug.org > Subject: UNION AND ORDER BY [21344] > > Hi all, > > I am running a SELECT statement using UNION on Informix 11.5. > It works okay. I need to make a minor change in ORDER BY clause > to use a subscript of a text column. Is it possible? > I know I can handle it by dropping the UNION clause and use "OR " > clause but just wondering whether or not it is possible to use a > subscript in ORDER BY clause with UNION. > > Here is a sample of my SQL query.. > > SELECT col1, col2, col3 > FROM table1 where (....a where clause .....) > UNION > SELECT col1, col2, col3 > FROM table1 where (....where clause...) > ORDER BY 2,3> > --- what I am looking is.. > > ORDER BY col2[6], col3. > > Since I can not use column name in this ORDER BY clause with UNION, how > can I > use subscript syntax alongwith number? > > I hope I provided enough information for what I am looking for..if not, > please > let me know.. > > Regards, > > Dharmendra > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Everett Mills
dharmendra sharma — — source: IIUG Forums & Mailing Lists

Thanks EEM, Yeah, but this will force me to get only one character from this field. I need the complete string in my SELECT as this field has other important information for further use. I just need to consider the 6th position for sorting the result. I know there is another option to select addtional column as "SELECT col1, col2, col3, col3[6] " and use the addtional 4th column in the order by list to get both the results, full value from this column and subscript for order by, but this will force me to change the coding to declare and handle the additonal variable. I am trying to keep my changes at SQL query level (if possible) so I don't need to change further code ...without adding/removing column list. Regards, Dharmendra > To: ids@iiug.org > From: Everett.Mills@nationalbeef.com > Subject: RE: UNION AND ORDER BY [21345] > Date: Fri, 17 Sep 2010 15:09:33 -0400 > > I don't have a system to test it on at the moment, but this should work: > > SELECT col1, col2, col3, col2[6] > FROM table1 where (....a where clause .....) > UNION > SELECT col1, col2, col3, col2[6] > FROM table1 where (....where clause...) > ORDER BY 4,3> > --EEM > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > DHARMENDRA SHARMA > > Sent: Friday, September 17, 2010 2:05 PM > > To: ids@iiug.org > > Subject: UNION AND ORDER BY [21344] > > > > Hi all, > > > > I am running a SELECT statement using UNION on Informix 11.5. > > It works okay. I need to make a minor change in ORDER BY clause > > to use a subscript of a text column. Is it possible? > > I know I can handle it by dropping the UNION clause and use "OR " > > clause but just wondering whether or not it is possible to use a > > subscript in ORDER BY clause with UNION. > > > > Here is a sample of my SQL query.. > > > > SELECT col1, col2, col3 > > FROM table1 where (....a where clause .....) > > UNION > > SELECT col1, col2, col3 > > FROM table1 where (....where clause...) > > ORDER BY 2,3> > > > --- what I am looking is.. > > > > ORDER BY col2[6], col3. > > > > Since I can not use column name in this ORDER BY clause with UNION, how > > can I > > use subscript syntax alongwith number? > > > > I hope I provided enough information for what I am looking for..if not, > > please > > let me know.. > > > > Regards, > > > > Dharmendra > > > > > > *********************************************************************** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to dharmendra sharma
Everett Mills — — source: IIUG Forums & Mailing Lists

Try this, then: SELECT col1, col2, col3 FROM ( SELECT col1, col2, col3, col2[6] orderCol FROM table1 where (....a where clause .....) UNION SELECT col1, col2, col3, col2[6] FROM table1 where (....where clause...) ) AS fake_table ORDER BY orderCol, col3 --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > dharmendra sharma > Sent: Friday, September 17, 2010 2:18 PM > To: ids@iiug.org > Subject: RE: UNION AND ORDER BY [21346] > > Thanks EEM, > > Yeah, but this will force me to get only one character from this field. > I need > the complete string in my SELECT as this field has other important > information > for further use. > I just need to consider the 6th position for sorting the result. I know > there > is another option to select addtional column as "SELECT col1, col2, > col3, > col3[6] " and > use the addtional 4th column in the order by list to get both the > results, > full value from this column and subscript for order by, but this will > force me > to change the coding to > declare and handle the additonal variable. I am trying to keep my > changes at > SQL query level (if possible) so I don't need to change further code > ....without adding/removing column list. > > Regards, > > Dharmendra > > > To: ids@iiug.org > > From: Everett.Mills@nationalbeef.com > > Subject: RE: UNION AND ORDER BY [21345] > > Date: Fri, 17 Sep 2010 15:09:33 -0400 > > > > I don't have a system to test it on at the moment, but this should > work: > > > > SELECT col1, col2, col3, col2[6] > > FROM table1 where (....a where clause .....) > > UNION > > SELECT col1, col2, col3, col2[6] > > FROM table1 where (....where clause...) > > ORDER BY 4,3> > > > --EEM > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > > > DHARMENDRA SHARMA > > > Sent: Friday, September 17, 2010 2:05 PM > > > To: ids@iiug.org > > > Subject: UNION AND ORDER BY [21344] > > > > > > Hi all, > > > > > > I am running a SELECT statement using UNION on Informix 11.5. > > > It works okay. I need to make a minor change in ORDER BY clause > > > to use a subscript of a text column. Is it possible? > > > I know I can handle it by dropping the UNION clause and use "OR " > > > clause but just wondering whether or not it is possible to use a > > > subscript in ORDER BY clause with UNION. > > > > > > Here is a sample of my SQL query.. > > > > > > SELECT col1, col2, col3 > > > FROM table1 where (....a where clause .....) > > > UNION > > > SELECT col1, col2, col3 > > > FROM table1 where (....where clause...) > > > ORDER BY 2,3> > > > > > --- what I am looking is.. > > > > > > ORDER BY col2[6], col3. > > > > > > Since I can not use column name in this ORDER BY clause with UNION, > how > > > can I > > > use subscript syntax alongwith number? > > > > > > I hope I provided enough information for what I am looking for..if > not, > > > please > > > let me know.. > > > > > > Regards, > > > > > > Dharmendra > > > > > > > > > > *********************************************************************** > > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Everett Mills
dharmendra sharma — — source: IIUG Forums & Mailing Lists

Wow!! This is cool.....I am sure it will work. I will try and will let you know. Thanks! Regards, Dharmendra > To: ids@iiug.org > From: Everett.Mills@nationalbeef.com > Subject: RE: UNION AND ORDER BY [21347] > Date: Fri, 17 Sep 2010 15:25:04 -0400 > > Try this, then: > > SELECT col1, col2, col3 FROM > ( > SELECT col1, col2, col3, col2[6] orderCol > FROM table1 where (....a where clause .....) > UNION > SELECT col1, col2, col3, col2[6] > FROM table1 where (....where clause...) > ) AS fake_table > ORDER BY orderCol, col3> > --EEM > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > dharmendra sharma > > Sent: Friday, September 17, 2010 2:18 PM > > To: ids@iiug.org > > Subject: RE: UNION AND ORDER BY [21346] > > > > Thanks EEM, > > > > Yeah, but this will force me to get only one character from this field. > > I need > > the complete string in my SELECT as this field has other important > > information > > for further use. > > I just need to consider the 6th position for sorting the result. I know > > there > > is another option to select addtional column as "SELECT col1, col2, > > col3, > > col3[6] " and > > use the addtional 4th column in the order by list to get both the > > results, > > full value from this column and subscript for order by, but this will > > force me > > to change the coding to > > declare and handle the additonal variable. I am trying to keep my > > changes at > > SQL query level (if possible) so I don't need to change further code > > ....without adding/removing column list. > > > > Regards, > > > > Dharmendra > > > > > To: ids@iiug.org > > > From: Everett.Mills@nationalbeef.com > > > Subject: RE: UNION AND ORDER BY [21345] > > > Date: Fri, 17 Sep 2010 15:09:33 -0400 > > > > > > I don't have a system to test it on at the moment, but this should > > work: > > > > > > SELECT col1, col2, col3, col2[6] > > > FROM table1 where (....a where clause .....) > > > UNION > > > SELECT col1, col2, col3, col2[6] > > > FROM table1 where (....where clause...) > > > ORDER BY 4,3> > > > > > --EEM > > > > > > > -----Original Message----- > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of > > > > DHARMENDRA SHARMA > > > > Sent: Friday, September 17, 2010 2:05 PM > > > > To: ids@iiug.org > > > > Subject: UNION AND ORDER BY [21344] > > > > > > > > Hi all, > > > > > > > > I am running a SELECT statement using UNION on Informix 11.5. > > > > It works okay. I need to make a minor change in ORDER BY clause > > > > to use a subscript of a text column. Is it possible? > > > > I know I can handle it by dropping the UNION clause and use "OR " > > > > clause but just wondering whether or not it is possible to use a > > > > subscript in ORDER BY clause with UNION. > > > > > > > > Here is a sample of my SQL query.. > > > > > > > > SELECT col1, col2, col3 > > > > FROM table1 where (....a where clause .....) > > > > UNION > > > > SELECT col1, col2, col3 > > > > FROM table1 where (....where clause...) > > > > ORDER BY 2,3> > > > > > > > --- what I am looking is.. > > > > > > > > ORDER BY col2[6], col3. > > > > > > > > Since I can not use column name in this ORDER BY clause with UNION, > > how > > > > can I > > > > use subscript syntax alongwith number? > > > > > > > > I hope I provided enough information for what I am looking for..if > > not, > > > > please > > > > let me know.. > > > > > > > > Regards, > > > > > > > > Dharmendra > > > > > > > > > > > > > > *********************************************************************** > > > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > *********************************************************************** > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > *********************************************************************** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Related threads