Home » Other » Training & Certification » Help me on this query
icon9.gif  Help me on this query [message #266205] Mon, 10 September 2007 03:44 Go to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
Hi Experts,

I have a table like this.

Eno sal
N1 Low
N2 Medium
N3 High

the query should display like this.

High
Medim
Low

How to write the query for it. Please help me.

Thanks & Regards
Vidya

Re: Help me on this query [message #266208 is a reply to message #266205] Mon, 10 September 2007 03:51 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORDER BY eno DESC
Re: Help me on this query [message #266209 is a reply to message #266205] Mon, 10 September 2007 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is your problem?
Use order by.

Regards
Michel
Re: Help me on this query [message #266217 is a reply to message #266205] Mon, 10 September 2007 04:01 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I think he wants in select query high will come first after high the rows containing medium will come and then low.
Re: Help me on this query [message #266256 is a reply to message #266217] Mon, 10 September 2007 05:42 Go to previous messageGo to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
Yes exactly, I want the High row first, Medium as next and Low as final
Re: Help me on this query [message #266275 is a reply to message #266256] Mon, 10 September 2007 06:16 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Combine "order by" and decode.
Now you should be able to finish your homework
Re: Help me on this query [message #266452 is a reply to message #266205] Mon, 10 September 2007 23:31 Go to previous messageGo to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
I am not getting how to conbine order by and decode
Re: Help me on this query [message #266460 is a reply to message #266452] Mon, 10 September 2007 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far as we can see, you did nothing.
Post what you have, what you tried.

Regards
Michel
Re: Help me on this query [message #266461 is a reply to message #266460] Mon, 10 September 2007 23:39 Go to previous messageGo to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
I tried with order by but in that the first high is comming
but after that low is comming then medium
Re: Help me on this query [message #266474 is a reply to message #266461] Tue, 11 September 2007 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
We need to see a query you wrote and, as you've said, doesn't give the result you expected.
Re: Help me on this query [message #266479 is a reply to message #266474] Tue, 11 September 2007 00:18 Go to previous messageGo to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
I wrote like this

select sal
from emp
order by sal
Re: Help me on this query [message #266483 is a reply to message #266205] Tue, 11 September 2007 00:25 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I am newbie in sql,pl/sql.
I tried but use of decode failed.It can't match 'low' as 'low'
SQL> select * from test;

       ENO SAL
---------- ----------
         1 low
         2 medium
         3 high
         3 high
         1 low
        10 high
        10 low
         7 HIGH

8 rows selected.

SQL> select eno,decode(sal,'HIGH',1,'medium',2,'low',3, 0) test,sal from test;

       ENO       TEST SAL
---------- ---------- ----------
         1          0 low
         2          0 medium
         3          0 high
         3          0 high
         1          0 low
        10          0 high
        10          0 low
         7          0 HIGH

8 rows selected.
Re: Help me on this query [message #266484 is a reply to message #266479] Tue, 11 September 2007 00:27 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where's a DECODE in your query, combined with an ORDER BY? I'm afraid you aren't capable of figuring that out (yet), so - here's how it may be done (using sample data set from your initial post):
SQL> create table test (eno varchar2(2), sal varchar2(10));

Table created.

SQL> insert all
  2    into test values ('N1', 'Low')
  3    into test values ('N2', 'Medium')
  4    into test values ('N3', 'High')
  5  select * From dual;

3 rows created.

SQL> select * From test;

EN SAL
-- ----------
N1 Low
N2 Medium
N3 High
My initial response:
SQL> select sal
  2  from test
  3  order by eno desc;

SAL
----------
High
Medium
Low
Frank's suggestion:
SQL> select sal
  2  from test
  3  order by decode(sal, 'High', 1,
  4                       'Medium', 2,
  5                       'Low', 3
  6                 );

SAL
----------
High
Medium
Low
Re: Help me on this query [message #266485 is a reply to message #266483] Tue, 11 September 2007 00:29 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
@Arju:
What datatype did you use for the sal-column? CHAR?
Looks like you have some trailing spaces or something.

[Updated on: Tue, 11 September 2007 00:30]

Report message to a moderator

Re: Help me on this query [message #266488 is a reply to message #266484] Tue, 11 September 2007 00:35 Go to previous messageGo to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
Thanks a lot frank...
Re: Help me on this query [message #266489 is a reply to message #266205] Tue, 11 September 2007 00:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I tried both in character and varchar2 data type.First create table as char data type as 10 length and then converted to varchar2 data type. So it took and remain 10 as length.

SQL>  select length(sal) len , sal from test;

       LEN  SAL
-------------------------------------------
        10 low

        10 medium

        10 high
      .
      .
      .
8 rows selected.

SQL> desc test;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- 
 ENO                                                            NUMBER
 SAL                                                            VARCHAR2(100)



Oh sorry char padded spaces to length 10 and later when I alter table column to varchar2 it remain so.

Clear now.

[Updated on: Tue, 11 September 2007 00:48]

Report message to a moderator

Re: Help me on this query [message #266499 is a reply to message #266205] Tue, 11 September 2007 00:59 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

@vidya, Don't you do same mistake as mine if you have a char data type. In that case use trim. Laughing

SQL>  select eno,decode(trim(sal),'high',1,'medium',2,'low',3, 0) test,sal
 from test order by decode(trim(sal),'high',1,'medium',2,'low',3);

       ENO       TEST SAL
---------- ---------- ----------
         3          1 high
        10          1 high
         3          1 high
         2          2 medium
        10          3 low
         1          3 low
         1          3 low

7 rows selected.

Cool

[Updated on: Tue, 11 September 2007 01:00]

Report message to a moderator

Re: Help me on this query [message #266502 is a reply to message #266499] Tue, 11 September 2007 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I have an advice, it is never use CHAR, always use VARCHAR2 (unless you have a very good reason to do so, of course).

Regards
Michel
Re: Help me on this query [message #266510 is a reply to message #266502] Tue, 11 September 2007 01:28 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Tue, 11 September 2007 08:06
If I have an advice, it is never use CHAR, always use VARCHAR2 (unless you have a very good reason to do so, of course).

Regards
Michel


I have an even better advice: even when you have a very good reason to use char, still use varchar2!
Re: Help me on this query [message #266511 is a reply to message #266205] Tue, 11 September 2007 01:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Cool Cool Cool
Re: Help me on this query [message #267920 is a reply to message #266510] Sun, 16 September 2007 08:11 Go to previous message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Yes Frank.
I have seen some problems (ORA-6502) in 10g, if we use CHAR and min / max. (Reference: Metalink)

By
Vamsi
Previous Topic: getting rownumber
Next Topic: Looking for discount for 9i funda training
Goto Forum:
  


Current Time: Fri Mar 29 06:28:53 CDT 2024