Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY dilemma (10.2.0.2.0)
ORDER BY dilemma [message #324728] |
Tue, 03 June 2008 16:03  |
DanR627
Messages: 7 Registered: February 2008 Location: New York
|
Junior Member |
|
|
I'm trying to order the results of a query by different columns, based on the circumstance.
Here's part of the table:
CREATE TABLE temp_schedules
(schedule_id VARCHAR2(24),
start_time VARCHAR2(5),
double_header VARCHAR2(1),
tba VARCHAR2(1))
With the rows I'm working with:
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---BALTIMORE-2', '0000', '2', 'Y');
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---BALTIMORE-1', '1705', '1', 'N');
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---BOSTON----0', '1905', '0', 'N');
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---DETROIT---0', '1905', '0', 'N');
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---TORONTO---0', '1907', '0', 'N');
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---TEXAS-----0', '2005', '0', 'N');
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---MINNESOTA-0', '2010', '0', 'N');
INSERT INTO temp_schedules (schedule_id, start_time, double_header, tba)
VALUES ('20080923AL---SEATTLE---0', '2210', '0', 'N');
Basically, I need it to order by the start time of the games, unless it is the second part of a double header. The double_header column indicates whether the game is not part of a double header (0), the first game of a double header (1), or the second game in a double header (2). If there is a double header on the day, I need it to order by start_time, unless it's the second half of the double header, in which case it has to immediately follow the first game in the order.
select * from temp_schedules order by start_time
When you run this query, it gives you this:
20080923AL---BALTIMORE-2 0000 2 Y
20080923AL---BALTIMORE-1 1705 1 N
20080923AL---BOSTON----0 1905 0 N
20080923AL---DETROIT---0 1905 0 N
20080923AL---TORONTO---0 1907 0 N
20080923AL---TEXAS-----0 2005 0 N
20080923AL---MINNESOTA-0 2010 0 N
20080923AL---SEATTLE---0 2210 0 N
I need this:
20080923AL---BALTIMORE-1 1705 1 N
20080923AL---BALTIMORE-2 0000 2 Y
20080923AL---BOSTON----0 1905 0 N
20080923AL---DETROIT---0 1905 0 N
20080923AL---TORONTO---0 1907 0 N
20080923AL---TEXAS-----0 2005 0 N
20080923AL---MINNESOTA-0 2010 0 N
20080923AL---SEATTLE---0 2210 0 N
Thanks in advance for any help.
|
|
|
Re: ORDER BY dilemma [message #324744 is a reply to message #324728] |
Tue, 03 June 2008 20:46   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
Definitely not the best database design. Nevertheless, the following should work:
select schedule_id, start_time, double_header, tba
from (
select b.*
, row_number()
over (partition by fv order by schedule_id) rn
from (
select a.*
, first_value(start_time)
over (partition by regexp_replace(schedule_id, '[-]*\d$')
order by double_header) fv
from temp_schedules a) b )
order by fv, rn;
Edited to accommodate schedule_id's with the same start_time.
[Updated on: Tue, 03 June 2008 23:13] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 02:02:29 CST 2025
|