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 Go to next message
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 Go to previous messageGo to next message
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

Re: ORDER BY dilemma [message #324934 is a reply to message #324744] Wed, 04 June 2008 09:19 Go to previous message
DanR627
Messages: 7
Registered: February 2008
Location: New York
Junior Member
Thanks alot. I'm still pretty new to Oracle so I'm not familiar with some of these functions. Thanks again.
Previous Topic: analytical function help
Next Topic: Sql query
Goto Forum:
  


Current Time: Thu Dec 08 16:33:02 CST 2016

Total time taken to generate the page: 0.06417 seconds