Home » SQL & PL/SQL » SQL & PL/SQL » Sorting UNION results (doesn't seem to work)
Sorting UNION results [message #283946] Wed, 28 November 2007 07:44 Go to next message
pc131
Messages: 36
Registered: May 2006
Member
Hi

I have situation like this:

select 'All Branches', to_char('0') from dual
union
select branch_name, to_char(branch_id) from branches
order by 1

this is to fill LOV with branch names (Algeria, Australia, Egypt, Romania etc) and add one position 'All Branches' on top on the list and rest in alphabetical order, but 'All branches' goes after Algeria and Australia. How can I force this select to put 'All Branches' before all other. Others are selected from table, 'All Branches' comes from dual. 'All branches' has id=0 as You can see. Others have different ids not equivalent to letters (Austraila has id=123, Egypt has id=109, Zimbabwe has id=198), they are mixed.

Any help appreciated.

Tom
Re: Sorting UNION results [message #283955 is a reply to message #283946] Wed, 28 November 2007 07:58 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Adding a dummy field in your SELECT-list could help you:


select 0,'All Branches', to_char('0') from dual
union
select 1,branch_name, to_char(branch_id) from branches
order by 1,2



HTH
Marc
Re: Sorting UNION results [message #283960 is a reply to message #283946] Wed, 28 November 2007 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use SQL*PLus, add a name to the first column and the statement "col first_col noprint" then you don't have the 0 and 1 in first column.

You can also prefix 'All Branches' with a blank or a null character: ' All branches' or chr(0)||'All Branches'.

Regards
Michel
Re: Sorting UNION results [message #283973 is a reply to message #283960] Wed, 28 November 2007 09:00 Go to previous messageGo to next message
pc131
Messages: 36
Registered: May 2006
Member
Thank You!

It worked adding dummy column at beginning!

I also know that adding space in front of 'All branches' as 'All branches' works but I needed more sophisticated method. Thanks guys anyway!

Michael Cadot:
Actualy adding null char to any char gives me null:
select chr(0)||'All Branches', to_char('0') from dual
results in null in first colmun and 0(zero) i second column
Re: Sorting UNION results [message #283975 is a reply to message #283973] Wed, 28 November 2007 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select chr(0)||'All Branches', to_char('0') from dual
  2  /
CHR(0)||'ALLB T
------------- -
 All Branches 0

1 row selected.

Regards
Michel
Re: Sorting UNION results [message #283978 is a reply to message #283975] Wed, 28 November 2007 09:08 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Maybe Michel should have emphasized again that his solution would only be appropriate within SQL*Plus (and maybe others tools)

Prefixing a string with a null-character could - actually would - have unexpected results when used in Java and/or PRO*C applications.
I can only speak of Java and/or PRO*C as I'm familiar with these two only.

Re: Sorting UNION results [message #283979 is a reply to message #283978] Wed, 28 November 2007 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, you're right.

@pc131, replace chr(0) by chr(1).

By the way, to_char('0') is not very smart.
It means convert the string '0' to the number 0 and convert it to the string '0'. Maybe just '0' is sufficient, isn't it?

Regards
Michel
Re: Sorting UNION results [message #283990 is a reply to message #283946] Wed, 28 November 2007 09:26 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Maybe a bit off-topic ...

From technical point of view both solutions are equal: they result both in the required result.

But I tend to prefer the dummy-column solution: it's more readable and clear.

In all cases one should document (by means of comment) why the query is implemented like this, so those who come after us can easily understand why.
(but that should be a general rule now would it Smile )

As I said: maybe a bit off-topic Wink
Re: Sorting UNION results [message #284006 is a reply to message #283990] Wed, 28 November 2007 10:19 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

But I tend to prefer the dummy-column solution: it's more readable and clear.

I agree and always it. Just wanted to give an alternative (that I don't use).

Regards
Michel
Previous Topic: find the dependencies of the table
Next Topic: dbms_stats
Goto Forum:
  


Current Time: Fri Dec 02 14:23:50 CST 2016

Total time taken to generate the page: 0.39415 seconds