Home » SQL & PL/SQL » SQL & PL/SQL » Help Required for Order By Clause
Help Required for Order By Clause [message #409484] Mon, 22 June 2009 20:34 Go to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
Hi All,

I have a table testing_order_by with values like this
FLAG NAME       SR_ID                  
---- ---------- ------- 
Y    Beena      13                     
N    Priya      13                     
N    Asha       13                     
N    BeenaG     13                     
N    PriyaG     13                     
N    AshaG      13

The requirement is
1. The record with flag equals to Y should be displayed as the
first record.
2. All the names should be grouped together.

So the output should look like
FLAG NAME       SR_ID                  
---- ---------- -------
Y    Beena      13                     
N    BeenaG     13  
N    Asha       13                     
N    AshaG      13                                        
N    Priya      13                     
N    PriyaG     13 


I tried to do the same using the select statement
select * from testing_order_by where sr_id=13 order by flag desc, name

But the output displayed is like this
FLAG NAME       SR_ID                  
---- ---------- ---------------------- 
Y    Beena      13                     
N    Asha       13                     
N    AshaG      13                     
N    BeenaG     13                     
N    Priya      13                     
N    PriyaG     13 

In the above output all the names are grouped together except the record with name as 'Beena'

Please let me know the correct way of using the order by clause to display an output like this
FLAG NAME       SR_ID                  
---- ---------- -------
Y    Beena      13                     
N    BeenaG     13  
N    Asha       13                     
N    AshaG      13                                        
N    Priya      13                     
N    PriyaG     13

Thanks in advance,
Asha
Re: Help Required for Order By Clause [message #409487 is a reply to message #409484] Mon, 22 June 2009 20:46 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
FLAG NAME       SR_ID                  
---- ---------- -------
Y    Beena      13                     
N    BeenaG     13  
N    Asha       13                     
N    AshaG      13                                        
Y    Priya      13                     
N    PriyaG     13


What is the expected/desired results from input above?
Re: Help Required for Order By Clause [message #409488 is a reply to message #409487] Mon, 22 June 2009 21:09 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
The input looks like this
FLAG NAME       SR_ID                  
---- ---------- ------- 
Y    Beena      13                     
N    Priya      13                     
N    Asha       13                     
N    BeenaG     13                     
N    PriyaG     13                     
N    AshaG      13


The expected result is
FLAG NAME       SR_ID                  
---- ---------- -------
Y    Beena      13                     
N    BeenaG     13  
N    Asha       13                     
N    AshaG      13                                        
Y    Priya      13                     
N    PriyaG     13


The requirement is
1. The record with flag equals to Y should be displayed as the
first record.
2. All the names should be grouped together.

Thanks,
Asha

[Updated on: Mon, 22 June 2009 21:10]

Report message to a moderator

Re: Help Required for Order By Clause [message #409513 is a reply to message #409484] Tue, 23 June 2009 00:04 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi Asha,

Are you sure about your expected result because If I observe your Input,

N    Priya      13                     


And the desired output says,

Y    Priya      13 


Could you please post DDL and DML (Insert Statements)

Hint : I believe you can achieve this by Union/Union all, If you could post the DDL and DML may be we can give a try

Regards,
Ashoka BL
devanampriya@gmail.com
Re: Help Required for Order By Clause [message #409518 is a reply to message #409513] Tue, 23 June 2009 00:38 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
Apologize for not pasting the contents properly..

Following is the input
======================
FLAG NAME       SR_ID                  
---- ---------- ------- 
Y    Beena      13                     
N    Priya      13                     
N    Asha       13                     
N    BeenaG     13                     
N    PriyaG     13                     
N    AshaG      13


Following should be the output
==============================
FLAG NAME       SR_ID                  
---- ---------- -------
Y    Beena      13                     
N    BeenaG     13  
N    Asha       13                     
N    AshaG      13                                        
N    Priya      13                     
N    PriyaG     13



The requirement is
1. The record with flag equals to Y should be displayed as the
first record.
2. All the names should be grouped together.

Thanks,
Asha
Re: Help Required for Order By Clause [message #409519 is a reply to message #409518] Tue, 23 June 2009 00:40 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
I tried to do the same using the select statement
select * from testing_order_by where sr_id=13 order by flag desc, name

But the output displayed is like this
FLAG NAME       SR_ID                  
---- ---------- ----------
Y    Beena      13                     
N    Asha       13                     
N    AshaG      13                     
N    BeenaG     13                     
N    Priya      13                     
N    PriyaG     13 


Which is not the desired one.
Re: Help Required for Order By Clause [message #409527 is a reply to message #409484] Tue, 23 June 2009 00:51 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Post DDL and DML
Re: Help Required for Order By Clause [message #409539 is a reply to message #409527] Tue, 23 June 2009 01:27 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
Following are the DDL and DML statements

create table testing_order_by(flag varchar2(3),name varchar2(10),sr_id number)

insert into testing_order_by values('Y','Beena',13)
insert into testing_order_by values('N','Asha',13)
similarly other insert statements are executed

Thanks,
Asha
Re: Help Required for Order By Clause [message #409541 is a reply to message #409539] Tue, 23 June 2009 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
similarly other insert statements are executed

This is not a valid SQL statement.
The purpose of a test is to make us try some solutions (and understand you), why do we have to build a test for you to help you. Do it if you want help and do it completely and do it in the way it works with just a copy and paste in SQL*Plus.

Regards
Michel

[Updated on: Tue, 23 June 2009 01:31]

Report message to a moderator

Re: Help Required for Order By Clause [message #409581 is a reply to message #409541] Tue, 23 June 2009 03:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
create table test_230 (col_1 varchar2(1), col_2 varchar2(30),col_3 number);

insert into test_230 values ('Y',    'Beena' ,     13);
insert into test_230 values ('N',    'BeenaG',     13);  
insert into test_230 values ('N',    'Asha'  ,     13);                     
insert into test_230 values ('N',    'AshaG' ,     13);                                        
insert into test_230 values ('N',    'Priya' ,     13);                     
insert into test_230 values ('N',    'PriyaG',     13);
commit;

select col_1,col_2,col_3 
from   test_230
order by max(col_1) over (partition by rtrim(col_2,'G') order by null) desc
        ,col_2;
Re: Help Required for Order By Clause [message #409582 is a reply to message #409541] Tue, 23 June 2009 03:41 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
Really sorry for providing incomplete information.
This is a test table i have created.
I cannot display the contents of the actual table because of security reasons.

create table testing_order_by(flag varchar2(3),name varchar2(10),sr_id number)

insert into testing_order_by values('Y','Beena',13)
insert into testing_order_by values('N','Priya ',13)
insert into testing_order_by values('N','Asha',13)
insert into testing_order_by values('N','BeenaG',13)
insert into testing_order_by values('N','PriyaG',13)
insert into testing_order_by values('N','AshaG',13)

select * from testing_order_by where sr_id=13
FLAG NAME       SR_ID                  
---- ---------- ------- 
Y    Beena      13                     
N    Priya      13                     
N    Asha       13                     
N    BeenaG     13                     
N    PriyaG     13                     
N    AshaG      13


I have inserted the values in a particular fashion, because the actual table displays the rows in a way similar to the above one.
Re: Help Required for Order By Clause [message #410074 is a reply to message #409582] Thu, 25 June 2009 01:30 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
The insert statements are in the order i have update above.
Please let me know if there is a way to resolve this issue.

Thanks in advance,
Asha
Re: Help Required for Order By Clause [message #410087 is a reply to message #410074] Thu, 25 June 2009 02:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

The requirement is
1. The record with flag equals to Y should be displayed as the
first record.
2. All the names should be grouped together.



You will have to clarify that. WHEN should the names be grouped together, e.g. What is the rule for the grouping of names.

If you have, for exampe :

Y  Thomas
N  ThomasG
Y  Thomy
N  Thomtom
Y  Thomboy
N  Thomgirl
Y  Thomberbold
N  ThirstyGuy
Y  ThirstMangler
N  ThirdSpace
Y  ThiefCatcher


Then what should the order be?

EDIT: For your requirement so far, that is fulfilled by JRowbottom answer.

[Updated on: Thu, 25 June 2009 03:01]

Report message to a moderator

Re: Help Required for Order By Clause [message #410142 is a reply to message #410074] Thu, 25 June 2009 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
The insert statements are in the order i have update above.
oh dear - you do know that data in a table has no inherrent order, don't you....

Quote:
Please let me know if there is a way to resolve this issue.
Does my solution not acheive this?
Re: Help Required for Order By Clause [message #410416 is a reply to message #409484] Fri, 26 June 2009 18:24 Go to previous messageGo to next message
olddba
Messages: 5
Registered: June 2009
Location: Longview, Wa
Junior Member
How about this;

select a.flag,b.name
from
(select flag,name from test
order by flag desc) a,
(select flag,name from test
order by name) b
where a.name = b.name;
Re: Help Required for Order By Clause [message #410418 is a reply to message #409484] Fri, 26 June 2009 18:49 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
olddba,

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
icon7.gif  Re: Help Required for Order By Clause [message #412358 is a reply to message #409484] Wed, 08 July 2009 20:30 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
Quote:

create table test_230 (col_1 varchar2(1), col_2 varchar2(30),col_3 number);

insert into test_230 values ('Y', 'Beena' , 13);
insert into test_230 values ('N', 'BeenaG', 13);
insert into test_230 values ('N', 'Asha' , 13);
insert into test_230 values ('N', 'AshaG' , 13);
insert into test_230 values ('N', 'Priya' , 13);
insert into test_230 values ('N', 'PriyaG', 13);
commit;

select col_1,col_2,col_3
from test_230
order by max(col_1) over (partition by rtrim(col_2,'G') order by null) desc
,col_2;



Thanks a lot! Smile
This solution worked.
But can anyone explain the use of keywords over and partition.


Thanks,
Asha
Re: Help Required for Order By Clause [message #412359 is a reply to message #409484] Wed, 08 July 2009 20:45 Go to previous message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>But can anyone explain the use of keywords over and partition.
When all else fails you can Read The Fine Manual, yourself.

Or do you desire we read it to you?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref967
Previous Topic: Problem in spooling a file.
Next Topic: collection , records ,varray, nested tables
Goto Forum:
  


Current Time: Wed Dec 07 20:09:20 CST 2016

Total time taken to generate the page: 0.09020 seconds