Order By Issues... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order By Issues...

Author

Topic

funnymanb
Starting Member

3 Posts

Posted - 2008-01-28 : 14:42:46

I am having an issue getting some info to order the way I need it to...

I enter my select statement:
select routing_label_id , route_endpoint1, route_endpoint2 from routing_label_routes where routing_label_id in ('NRL_PIT_CAMBRIDGE','NRL_SF_MABEL','NRL_SF_MABEL_WRLS','NRL_SF_TRILEVEL','NRL_SF_GLOBODYNE','NRL_SF_JOHNQ','NRL_SF_ATBVZOFFNET');

and I know it is going to return the results in ASC of the 1st criteria specified (routing_label_id ),
like this:
ROUTING_LABEL_ID ROUTE_ENDPOINT1 ROUTE_ENDPOINT2
----------------------- -------------------------- -------------------
NRL_PIT_CAMBRIDGE CAMBRIDGE_6057 GNIPITMG1
NRL_PIT_CAMBRIDGE CAMBRIDGE_6057 GNIPITMG2
NRL_PIT_CAMBRIDGE CAMBRIDGE_6057 GNIPITMG3
NRL_PIT_CAMBRIDGE CAMBRIDGE_6057 GNIPITMG4
NRL_PIT_CAMBRIDGE CAMBRIDGE_6070 GNIPITMG1
NRL_PIT_CAMBRIDGE CAMBRIDGE_6070 GNIPITMG2
NRL_PIT_CAMBRIDGE CAMBRIDGE_6070 GNIPITMG3
NRL_PIT_CAMBRIDGE CAMBRIDGE_6063 GNIPITMG3
NRL_SF_ATBVZOFFNET ATBVZOFFNET_999 GNISFMG1
NRL_SF_MABEL MABEL2147_6159 GNISFMG4
NRL_SF_MABEL MABEL2147_6159 GNISFMG5

ROUTING_LABEL_ID ROUTE_ENDPOINT1 ROUTE_ENDPOINT2
----------------------- -------------------------- -------------------NRL_SF_MABEL_WRLS MABEL2147_6161 GNISFMG6
NRL_SF_GLOBODYNE GLOBOGT1_6166 GNISFMG1
NRL_SF_GLOBODYNE GLOBOGT1_6166 GNISFMG2
NRL_SF_TRILEVEL TRILVLG0C_6163 GNISFMG6
NRL_SF_JOHNQ JOHNQ00T_6158 GNISFMG6

16 rows selected.

however, I would like the results displayed in the order I listed the targets, i.g.
1. 'NRL_PIT_CAMBRIDGE'
2. 'NRL_SF_MABEL'
3. 'NRL_SF_MABEL_WRLS'
4. 'NRL_SF_TRILEVEL'

Is this possible?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 14:58:13

You could join to your list as a derived table adding a column to order on.


SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
FROM routing_label_routes r
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id UNION ALL
       SELECT 1, 'NRL_SF_MABEL' UNION ALL
       SELECT 2, 'NRL_SF_MABEL_WRLS' UNION ALL
       SELECT 3, 'NRL_SF_TRILEVEL' UNION ALL
       SELECT 4, 'NRL_SF_GLOBODYNE' UNION ALL
       SELECT 5, 'NRL_SF_JOHNQ' UNION ALL
       SELECT 6, 'NRL_SF_ATBVZOFFNET' ) id_list ON r.routing_label_id = id_list.routing_label_id
ORDER BY id_list.order_id

funnymanb
Starting Member

3 Posts

Posted - 2008-01-28 : 15:08:53

Thanks for the suggestion, I tried and got the following error:

SQL> SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
2 FROM routing_label_routes r
3 JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id UNION ALL
4 SELECT 1, 'NRL_SF_MABEL' UNION ALL
5 SELECT 2, 'NRL_SF_MABEL_WRLS' UNION ALL
SELECT 3, 'NRL_SF_TRILEVEL' UNION ALL
SELECT 4, 'NRL_SF_GLOBODYNE' UNION ALL
6 7 8 SELECT 5, 'NRL_SF_JOHNQ' UNION ALL
9 SELECT 6, 'NRL_SF_ATBVZOFFNET' ) id_list ON r.routing_label_id = id_list.routing_label_id
10 ORDER BY id_list.order_id;
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBIRDGE' AS routing_label_id UNION ALL
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 15:18:02

ORA error???

Anyhow.. on oracle...this should work

SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
FROM routing_label_routes r
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id from test UNION ALL
SELECT 1 AS order_id, 'NRL_SF_MABEL'AS routing_label_id from test UNION ALL
SELECT 2 AS order_id, 'NRL_SF_MABEL_WRLS' AS routing_label_id from test UNION ALL
SELECT 3 AS order_id, 'NRL_SF_TRILEVEL' AS routing_label_id from test UNION ALL
SELECT 4 AS order_id, 'NRL_SF_GLOBODYNE' AS routing_label_id from test UNION ALL
SELECT 5 AS order_id, 'NRL_SF_JOHNQ' AS routing_label_id from test UNION ALL
SELECT 6 AS order_id, 'NRL_SF_ATBVZOFFNET' AS routing_label_id from test ) id_list ON r.routing_label_id = id_list.routing_label_id
ORDER BY id_list.order_id

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 15:19:26

This works fine on my machine:

DECLARE @routing_label_routes TABLE ( routing_label_id VARCHAR(25), route_endpoint1 INT, route_endpoint2 INT )
INSERT @routing_label_routes ( routing_label_id, route_endpoint1, route_endpoint2 )
SELECT 'NRL_PIT_CAMBRIDGE', 1, 1 UNION ALL
SELECT 'NRL_SF_MABEL', 1, 1 UNION ALL
SELECT 'NRL_PIT_TRILEVEL', 1, 1 UNION ALL
SELECT 'NRL_SF_MABEL_WRLS', 1, 1 UNION ALL
SELECT 'NRL_SF_TRILEVEL', 1, 1 UNION ALL
SELECT 'NRL_SF_GLOBODYNE', 1, 1 UNION ALL
SELECT 'NRL_PIT_GLOBODYNE', 1, 1 UNION ALL
SELECT 'NRL_SF_JOHNQ', 1, 1 UNION ALL
SELECT 'NRL_SF_ATBVZOFFNET', 1, 1

SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
FROM @routing_label_routes r
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id UNION ALL
       SELECT 1, 'NRL_SF_MABEL' UNION ALL
       SELECT 2, 'NRL_SF_MABEL_WRLS' UNION ALL
       SELECT 3, 'NRL_SF_TRILEVEL' UNION ALL
       SELECT 4, 'NRL_SF_GLOBODYNE' UNION ALL
       SELECT 5, 'NRL_SF_JOHNQ' UNION ALL
       SELECT 6, 'NRL_SF_ATBVZOFFNET' ) id_list ON r.routing_label_id = id_list.routing_label_id
ORDER BY id_list.order_id

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 15:20:18

quote:
Originally posted by sakets_2000

ORA error???

Anyhow.. on oracle...this should work

SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
FROM routing_label_routes r
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id from test UNION ALL
SELECT 1 AS order_id, 'NRL_SF_MABEL'AS routing_label_id from test UNION ALL
SELECT 2 AS order_id, 'NRL_SF_MABEL_WRLS' AS routing_label_id from test UNION ALL
SELECT 3 AS order_id, 'NRL_SF_TRILEVEL' AS routing_label_id from test UNION ALL
SELECT 4 AS order_id, 'NRL_SF_GLOBODYNE' AS routing_label_id from test UNION ALL
SELECT 5 AS order_id, 'NRL_SF_JOHNQ' AS routing_label_id from test UNION ALL
SELECT 6 AS order_id, 'NRL_SF_ATBVZOFFNET' AS routing_label_id from test ) id_list ON r.routing_label_id = id_list.routing_label_id
ORDER BY id_list.order_id

Ah, oracle... missed that one.

funnymanb
Starting Member

3 Posts

Posted - 2008-01-28 : 15:31:15

I have turned to the right people, this has worked! Thank you all for your help, just saved about a thousand man hours...

Probably should have advised that the environ was Oracle...my apologies, I'm just getting started in this. Thanks again.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 15:50:30

quote:
Originally posted by funnymanb

I have turned to the right people, this has worked! Thank you all for your help, just saved about a thousand man hours...

Probably should have advised that the environ was Oracle...my apologies, I'm just getting started in this. Thanks again.

Not a problem. Glad we could help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 04:17:25

quote:
Originally posted by sakets_2000

ORA error???

Anyhow.. on oracle...this should work

SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
FROM routing_label_routes r
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id from test UNION ALL
SELECT 1 AS order_id, 'NRL_SF_MABEL'AS routing_label_id from test UNION ALL
SELECT 2 AS order_id, 'NRL_SF_MABEL_WRLS' AS routing_label_id from test UNION ALL
SELECT 3 AS order_id, 'NRL_SF_TRILEVEL' AS routing_label_id from test UNION ALL
SELECT 4 AS order_id, 'NRL_SF_GLOBODYNE' AS routing_label_id from test UNION ALL
SELECT 5 AS order_id, 'NRL_SF_JOHNQ' AS routing_label_id from test UNION ALL
SELECT 6 AS order_id, 'NRL_SF_ATBVZOFFNET' AS routing_label_id from test ) id_list ON r.routing_label_id = id_list.routing_label_id
ORDER BY id_list.order_id

Does it mean you need to specify alias name for all Selects specified in UNION ALL. If so, I wonder what would be the result of this

Select a,b from
(
select 1 as a, 'test' as b union all
select 1 as op, 'test' as yub union all
select 1 as da, 'test' as iop union all
select 1 as asd, 'test' as dfb
) t

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-29 : 11:54:21

quote:
Originally posted by madhivanan

quote:
Originally posted by sakets_2000

ORA error???

Anyhow.. on oracle...this should work

SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
FROM routing_label_routes r
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id from test UNION ALL
SELECT 1 AS order_id, 'NRL_SF_MABEL'AS routing_label_id from test UNION ALL
SELECT 2 AS order_id, 'NRL_SF_MABEL_WRLS' AS routing_label_id from test UNION ALL
SELECT 3 AS order_id, 'NRL_SF_TRILEVEL' AS routing_label_id from test UNION ALL
SELECT 4 AS order_id, 'NRL_SF_GLOBODYNE' AS routing_label_id from test UNION ALL
SELECT 5 AS order_id, 'NRL_SF_JOHNQ' AS routing_label_id from test UNION ALL
SELECT 6 AS order_id, 'NRL_SF_ATBVZOFFNET' AS routing_label_id from test ) id_list ON r.routing_label_id = id_list.routing_label_id
ORDER BY id_list.order_id

Does it mean you need to specify alias name for all Selects specified in UNION ALL. If so, I wonder what would be the result of this

Select a,b from
(
select 1 as a, 'test' as b union all
select 1 as op, 'test' as yub union all
select 1 as da, 'test' as iop union all
select 1 as asd, 'test' as dfb
) t

Madhivanan

Failing to plan is Planning to fail

wasn't cos of the aliases, was cos oracle doesn't like this-->
select 1 union all select 2

instead it expects -->
select 1 from test union all select 2 from test

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-30 : 00:44:57

quote:
Originally posted by sakets_2000

quote:
Originally posted by madhivanan

quote:
Originally posted by sakets_2000

ORA error???

Anyhow.. on oracle...this should work

SELECT r.routing_label_id , r.route_endpoint1, r.route_endpoint2
FROM routing_label_routes r
JOIN ( SELECT 0 AS order_id, 'NRL_PIT_CAMBRIDGE' AS routing_label_id from test UNION ALL
SELECT 1 AS order_id, 'NRL_SF_MABEL'AS routing_label_id from test UNION ALL
SELECT 2 AS order_id, 'NRL_SF_MABEL_WRLS' AS routing_label_id from test UNION ALL
SELECT 3 AS order_id, 'NRL_SF_TRILEVEL' AS routing_label_id from test UNION ALL
SELECT 4 AS order_id, 'NRL_SF_GLOBODYNE' AS routing_label_id from test UNION ALL
SELECT 5 AS order_id, 'NRL_SF_JOHNQ' AS routing_label_id from test UNION ALL
SELECT 6 AS order_id, 'NRL_SF_ATBVZOFFNET' AS routing_label_id from test ) id_list ON r.routing_label_id = id_list.routing_label_id
ORDER BY id_list.order_id

Does it mean you need to specify alias name for all Selects specified in UNION ALL. If so, I wonder what would be the result of this

Select a,b from
(
select 1 as a, 'test' as b union all
select 1 as op, 'test' as yub union all
select 1 as da, 'test' as iop union all
select 1 as asd, 'test' as dfb
) t

Madhivanan

Failing to plan is Planning to fail

wasn't cos of the aliases, was cos oracle doesn't like this-->
select 1 union all select 2

instead it expects -->
select 1 from test union all select 2 from test

Strange

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources