Order By help needed [message #278020] |
Thu, 01 November 2007 08:34  |
matchey
Messages: 5 Registered: November 2007 Location: Bristol
|
Junior Member |
|
|
I have a SQL report extracting data from 2 tables. The results are odered by the date on table a. I would like to order the report primarily by date a on table a, but if date b is earlier than date a then 'squeeze' date b into the result set in the appropriate places.
See the below example:
select a.date_a, b.date_b
from table_a a,
table_b b
where a.id=b.id
order by a.date_a
Is there a way of doing this?
|
|
|
|
Re: Order By help needed [message #278023 is a reply to message #278021] |
Thu, 01 November 2007 08:56   |
matchey
Messages: 5 Registered: November 2007 Location: Bristol
|
Junior Member |
|
|
Sorry, but I should have given a better example - the field I am ordering by is actually a varchar2 field, not a date or number field, so using least does not affect the order.
Apologies.
Is there something similar to 'least' fo varchar2 fieds?
|
|
|
|
Re: Order By help needed [message #278033 is a reply to message #278023] |
Thu, 01 November 2007 10:10   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you have a way of determining which of your two fields is the one you want, then just plug that logic into a CASR statement and order by that CASE statement.
You aren't really keeping Dates in a Varchar2 are you?
|
|
|
Re: Order By help needed [message #278035 is a reply to message #278033] |
Thu, 01 November 2007 10:40   |
matchey
Messages: 5 Registered: November 2007 Location: Bristol
|
Junior Member |
|
|
No I'm not keeping dates in varchar2 columns! The date is just in the example code I gave, not in the real code.
i couldn't get the "least" to work properly, it spat the results out in a random alphatbetical order.
I just gave a bad example. See below for a better example to save confusion:
I have a SQL report extracting data from 2 tables. The results are ordered by "varchar2_a" on table a. I would like to order the report primarily by "varchar2_a" on table a, but if "varchar2_b" hass an earlier timestamp than "varchar2_b" then 'squeeze' "varchar2_b" into the result set in the appropriate places.
See the below example:
select a.varchar2_a, b.varchar2_b
from table_a a,
table_b b
where a.id=b.id
order by a.varchar2_a
|
|
|
Re: Order By help needed [message #278039 is a reply to message #278035] |
Thu, 01 November 2007 10:55   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm afraid I don't buy 'Random alphabetical order'
Can you cut and paste an example from SQL*PLus to show this.
I've got an example here showing LEAST bringing the data back in exactly the right order:with src as (select 'AAAA' col_1 from dual union all
select 'A' from dual union all
select 'BAA' from dual union all
select 'E' from dual union all
select 'EAAA' from dual)
select s1.col_1
,s2.col_1
,least(s1.col_1,s2.col_1) from src s1, src s2
order by least(s1.col_1,s2.col_1);
COL_1 COL_1_1 LEAST(S1.COL_1,S2.COL_1)
A BAA A
AAAA A A
EAAA A A
BAA A A
A EAAA A
A E A
A A A
E A A
A AAAA A
AAAA AAAA AAAA
BAA AAAA AAAA
AAAA EAAA AAAA
AAAA E AAAA
E AAAA AAAA
AAAA BAA AAAA
EAAA AAAA AAAA
E BAA BAA
BAA EAAA BAA
BAA E BAA
BAA BAA BAA
EAAA BAA BAA
EAAA E E
E E E
E EAAA E
EAAA EAAA EAAA
|
|
|
|
|
|
|