Home » SQL & PL/SQL » SQL & PL/SQL » order by variable
order by variable [message #212845] Mon, 08 January 2007 09:12 Go to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi,

A website form shoots the following query to a oracle database and contains a variable ('dam').

SELECT   DISTINCT straatoff
FROM     spd_adres
WHERE    STRAATOFF LIKE UPPER('%'||REPLACE(TRIM('dam'),'*','%')||'&') 
OR       STRAATNEN LIKE UPPER('%'||REPLACE(TRIM('dam'),'*','%')||'%')

I want to order the result set like this:
- first all the rows starting with 'dam'
- then all other records

Can I use ORDER BY (how?) or do I need to rebuild my statement?
Re: order by variable [message #212856 is a reply to message #212845] Mon, 08 January 2007 10:22 Go to previous messageGo to next message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
SQL> SELECT * FROM str;

STR
-----
abcde
bcdef
cdefg
damab
damcd
damef
damgh
efghi
fghij
ghijk

10 rows selected

SQL> 
SQL> SELECT * FROM str
  2  ORDER BY CASE INSTR(str.str, 'dam')
  3           WHEN 1 THEN 0
  4           ELSE 1 END;

STR
-----
damcd
damgh
damef
damab
ghijk
efghi
fghij
cdefg
bcdef
abcde

10 rows selected


The CASE expression in the ORDER BY returns 0 if the string starts with 'dam' (INSTR evaluates to 1) otherwise it returns 1. Obviously, ORDERing on that expression will put strings starting with 'dam' before those not starting with 'dam'.
Re: order by variable [message #212881 is a reply to message #212845] Mon, 08 January 2007 13:58 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The requirements aren't completely clear, but if you need the 'dam' entries listed first and in alphabetical order and then all subsequent entries listed after the 'dam' entries in alphabetical order, then consider:


SQL> select * from dtest;

STRING
-----------
abcdef
efghij
damabc
damxyz
xyzabc
dammno

SQL> select * from dtest
  2	order by decode(instr(string,'dam'),1,'1'||string,string);

STRING
-----------
damabc
dammno
damxyz
abcdef
efghij
xyzabc
Re: order by variable [message #212912 is a reply to message #212845] Mon, 08 January 2007 16:25 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Your code has a logic problem. What happens if the string contains dam, but it is not first. Use the following, it will have an added effect of sorting the strings.

select * from dtest
order by decode(upper(string),1,3),'DAM','0'||string,'1'|string)
Re: order by variable [message #212918 is a reply to message #212912] Mon, 08 January 2007 17:14 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Bill B wrote on Mon, 08 January 2007 17:25
Your code has a logic problem. What happens if the string contains dam, but it is not first. Use the following, it will have an added effect of sorting the strings.

select * from dtest
order by decode(upper(string),1,3),'DAM','0'||string,'1'|string)


It's quite obvious that you didn't bother to test your "assertion" that the code "has a logic problem" ?? Honestly, if someone is going to claim someone else's code is FLAWED, then they should at least test their own code prior to posting !!! If you would have TESTED my code, you would have seen that it accounts for the string 'dam' NOT being at the beginning.

SQL> select * from dtest;

STRING
--------------------
abcdef
efghij
damabc
damxyz
xyzabc
dammno
zdamab

7 rows selected.

SQL> select * from dtest
  2     order by decode(instr(string,'dam'),1,'1'||string,string);

STRING
--------------------
damabc
dammno
damxyz
abcdef
efghij
xyzabc
zdamab

7 rows selected.

SQL>


You really should spend less time critiquing other's code and concentrate on the SQL Reference Guide. As shown below, your code won't even run let alone make any sense.

SQL> select * from dtest
  2  order by decode(upper(string),1,3),'DAM','0'||string,'1'|string);
order by decode(upper(string),1,3),'DAM','0'||string,'1'|string)
                                                        *
ERROR at line 2:
ORA-00996: the concatenate operator is ||, not |

Re: order by variable [message #212992 is a reply to message #212845] Tue, 09 January 2007 01:15 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
No need to get angry...
Thank you all for your assistance. Both codes below function properly, although I prefer to use the first one...
SELECT   DISTINCT a.straatoff
FROM     spd_adres a
WHERE    a.straatoff LIKE UPPER('%'||REPLACE(TRIM('dam'),'*','%')||'%') 
OR       a.straatnen LIKE UPPER('%'||REPLACE(TRIM('dam'),'*','%')||'%')
ORDER BY decode(instr(a.straatoff,UPPER('dam')),1,'1'||a.straatoff),a.straatoff

SELECT   DISTINCT a.straatoff
FROM     spd_adres a
WHERE    a.straatoff LIKE UPPER('%'||REPLACE(TRIM('dam'),'*','%')||'%') 
OR       a.straatnen LIKE UPPER('%'||REPLACE(TRIM('dam'),'*','%')||'%')
ORDER BY CASE instr(a.straatoff, UPPER('dam')) WHEN 1 THEN 0 ELSE 1 END
Re: order by variable [message #213010 is a reply to message #212845] Tue, 09 January 2007 02:30 Go to previous messageGo to next message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
Agreed, if you want additional alphabetic sorting then ebrian's solution is the way to go, although there is a minor bug in the code. If your string starts with 'dam' then 1 is prefixed, otherwise nothing is prefixed and then we sort on the results of that. However, if there is a string that would still come before '1dam...' then that will still come first. The answer is to prefix '2' if we don't start with 'dam':
[ Edit: you didn't see anything... </jedimindtrick> ]

SQL> select * from str
  2  order by decode(instr(str,'dam'),1,'1'||str,str);

STR
-----
1abcd
damab
damcd
damef
damgh
abcde
bcdef
cdefg
efghi
fghij
ghijk

11 rows selected

SQL> select * from str
  2  order by decode(instr(str,'dam'),1,'1'||str,2||str);

STR
-----
damab
damcd
damef
damgh
1abcd
abcde
bcdef
cdefg
efghi
fghij
ghijk

11 rows selected


[Edit: Which I've just noticed was also included in Bill B's code]

[Updated on: Tue, 09 January 2007 08:56]

Report message to a moderator

Re: order by variable [message #213062 is a reply to message #213010] Tue, 09 January 2007 05:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I concur Girafee. However, that's why sample data usually helps w/ providing a solution.
Re: order by variable [message #213105 is a reply to message #212845] Tue, 09 January 2007 08:55 Go to previous message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
Yes, and also if you attempt to generate some resultsets with example code, and make a mistake, then start again, it helps to NOT include the part where you messed up in your copying... Embarassed
Previous Topic: Required query analysis..
Next Topic: Column count (merged)
Goto Forum:
  


Current Time: Tue Dec 06 02:42:42 CST 2016

Total time taken to generate the page: 0.10172 seconds