Home » SQL & PL/SQL » SQL & PL/SQL » Order By help needed
Order By help needed [message #278020] Thu, 01 November 2007 08:34 Go to next message
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 #278021 is a reply to message #278020] Thu, 01 November 2007 08:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try
Order by least(a.date_a,b.date_b)
Re: Order By help needed [message #278023 is a reply to message #278021] Thu, 01 November 2007 08:56 Go to previous messageGo to next message
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 #278024 is a reply to message #278020] Thu, 01 November 2007 09:04 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Why oh why oh why would you store a date in a varchar2 field, this just makes things so much more unwieldy.

Least will work on varchar2 fields, but will use ASCII sorting.

[Updated on: Thu, 01 November 2007 09:07]

Report message to a moderator

Re: Order By help needed [message #278033 is a reply to message #278023] Thu, 01 November 2007 10:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Order By help needed [message #278041 is a reply to message #278039] Thu, 01 November 2007 11:10 Go to previous messageGo to next message
matchey
Messages: 5
Registered: November 2007
Location: Bristol
Junior Member
see attached file for my code and the results whcih are not in the order I want.
  • Attachment: code.sql
    (Size: 3.74KB, Downloaded 172 times)
Re: Order By help needed [message #278043 is a reply to message #278041] Thu, 01 November 2007 11:22 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
try
ORDER BY vtc.vtc_cert_serial_no|| vvt.vet_test_certificate_srl_num

(Untested)
Re: Order By help needed [message #278045 is a reply to message #278041] Thu, 01 November 2007 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col col format a10
SQL> set null '<NULL>'
SQL> select least('A',null) col from dual;
COL
----------
<NULL>

1 row selected.

Does it help you to understand what happen?

Regards
Michel
Re: Order By help needed [message #278055 is a reply to message #278043] Thu, 01 November 2007 12:05 Go to previous message
matchey
Messages: 5
Registered: November 2007
Location: Bristol
Junior Member
Thanks the || on the order by works great.
Thnaks, for all your help.
Previous Topic: Need help with an SP
Next Topic: Row not found
Goto Forum:
  


Current Time: Fri Dec 09 08:09:05 CST 2016

Total time taken to generate the page: 0.10604 seconds