Home » SQL & PL/SQL » SQL & PL/SQL » Merge the Result of two Queries
Merge the Result of two Queries [message #210168] Tue, 19 December 2006 15:13 Go to next message
galaxy
Messages: 62
Registered: October 2005
Member
Hello,

I have a table which is like this

Car Typ Time
BMW 330 13:00
BMW 335 11:00
BMW 320 16:00
Audi A4 12:00
Audi A5 16:00
Audi A6 15:00

I want to create an SQL statemnet which should show me the following result (Show me the testdrives splited by 13:00 o'clock)


BMW 335 11:00
BMW 330 13:00
Audi A4 12:00

BMW 320 16:00
Audi A6 15:00
Audi A5 16:00

So I want to have 2 "Groups" one before 13:00 and one after.

I tried it with this statement:

select car, type, time form cars where time <= 13:00
order by car,time
union
select car, type, time form cars where time > 13:00
order by car,time

(I know that time>13:00 would not work like this. This is only to show wat I want)
Each of those selects shows me the correct part(so in the correct order), But when I run them both together the ones form before 13:00 and after 13:00 where mixed. So no split than at 13:00
The output looks like this:(not what I want)

BMW 335 11:00
BMW 330 13:00
BMW 320 16:00
Audi A4 12:00
Audi A6 15:00
Audi A5 16:00


I want to have the output of the Query like I explained before

Can somebody please help me?
Re: Merge the Result of two Queries [message #210185 is a reply to message #210168] Tue, 19 December 2006 16:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Basically, your query is OK. What you have to do is to order data ONLY at the end:

select car, type, time form cars where time <= 13:00
union
select car, type, time form cars where time > 13:00
order by time;
Re: Merge the Result of two Queries [message #210244 is a reply to message #210185] Wed, 20 December 2006 00:37 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
then there would not really be a need for the union, would there?
just ordering a select * would do the same...

I think you need a dummy column to order by:
SQL> create table faq
  2  ( car varchar2(10)
  3  , type varchar2(10)
  4  , time date)
  5  /

Table created.

SQL> insert into faq
  2  values ('BMW', '335', to_date('11:00', 'hh24:mi'));

1 row created.

SQL> insert into faq
  2  values ('BMW', '330', to_date('13:00', 'hh24:mi'));

1 row created.

SQL> insert into faq
  2  values ('BMW', '320', to_date('16:00', 'hh24:mi'));

1 row created.

SQL> insert into faq
  2  values ('Audi', 'A4', to_date('12:00', 'hh24:mi'));

1 row created.

SQL> insert into faq
  2  values ('Audi', 'A6', to_date('15:00', 'hh24:mi'));

1 row created.

SQL> insert into faq
  2  values ('Audi', 'A5', to_date('16:00', 'hh24:mi'));

1 row created.

SQL> 
SQL> select car
  2  ,	    type
  3  ,	    to_char(time, 'hh24:mi')
  4  from   faq
  5  order  by case when to_date(to_char(time, 'hh24:mi'), 'hh24:mi') <= to_date('13:00', 'hh24:mi') then 1 else 2 end
  6  ,	    car desc
  7  ;

CAR        TYPE       TO_CH
---------- ---------- -----
BMW        335        11:00
BMW        330        13:00
Audi       A4         12:00
BMW        320        16:00
Audi       A6         15:00
Audi       A5         16:00

6 rows selected.


[Edit: I had to use <= instead of <]

[Updated on: Wed, 20 December 2006 00:51]

Report message to a moderator

Previous Topic: how to retrieve sysdate in oracle?senthil
Next Topic: Opposite of TRUNC
Goto Forum:
  


Current Time: Fri Dec 09 01:49:34 CST 2016

Total time taken to generate the page: 0.10077 seconds