Home » SQL & PL/SQL » SQL & PL/SQL » How to retrive it??
icon5.gif  How to retrive it?? [message #416255] Fri, 31 July 2009 06:44 Go to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
Hii,
I have 1 table with two columns locations and salary . There are 3 different location - loc-1, loc-2, loc-3. In almost 90 rows these 3 locations are randomly placed with different salary values. Now I want to retrieve only 9 rows from the data in fllowing way :

1)loc-1 sal-1
2)loc-1 sal-1
3)loc-1 sal-1
4)loc-2 sal-2
5)loc-2 sal-2
6)loc-2 sal-2
7)loc-3 sal-3
8)loc-3 sal-3
9)loc-3 sal-3

( sal1 - salary corresponds to loc-1
sal2 - salary corresponds to loc-2
sal3 - salary corresponds to loc-3)

and salary is arranged in descending order.

How can I retrive it??
Re: How to retrive it?? [message #416259 is a reply to message #416255] Fri, 31 July 2009 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're going to have to explain that, preferably with a proper test case (create table and insert statements), becuase I have no idea what logic you're trying to use here.
Re: How to retrive it?? [message #416264 is a reply to message #416255] Fri, 31 July 2009 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand you want the TOP-3 salaries of each location.
If so, just search for "top-n" or the like.

Regards
Michel
Re: How to retrive it?? [message #416305 is a reply to message #416264] Fri, 31 July 2009 09:48 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
Yes ... I want to have the top-3 salaries of each of the three locations...
I have also tried to use the top-n but the problem is that i am not getting how can I restrict the number of rows to 3 for each location, -- as there are almost 20-25 rows for each of the 3 locations , means almost 75 rows. I just want to retrieve 9 rows such that it gives following result.

---1 to 3 rows ---- location 1 details
---4 to 6 rows ---- location 2 details
---7 to 9 rows ---- location 3 details

Hope u got what i mean to say....
Re: How to retrive it?? [message #416309 is a reply to message #416305] Fri, 31 July 2009 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste what you already tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How to retrive it?? [message #416324 is a reply to message #416309] Fri, 31 July 2009 13:50 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
hii,
I got d answer ....I used this code..

      SELECT  loc, sal 
      FROM    (SELECT  loc, sal 
               FROM    locn
               ORDER   BY  sal DESC)
      WHERE  rownum <= 9
      ORDER  BY loc;
      


..but still this I got when no. of rows for each loc is equally distributed in the table..eg- loc1-20rows, loc2-20rows, loc3-20rows if i am increasing the number of rows with any one of them- eg..loc2-25rows , then I m getting answer with max no. of loc2...
I will appreciate any suggestion from your side..
Re: How to retrive it?? [message #416326 is a reply to message #416324] Fri, 31 July 2009 14:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
My suggestion would be to Michel's advice first and post a test case, what you actually did, and the required result.

Your last post is completely un-understandable. Don't EXPLAIN what you did, SHOW US what you did.



Re: How to retrive it?? [message #416330 is a reply to message #416324] Fri, 31 July 2009 15:23 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
rhl23 wrote on Fri, 31 July 2009 14:50

      SELECT  loc, sal 
      FROM    (SELECT  loc, sal 
               FROM    locn
               ORDER   BY  sal DESC)
      WHERE  rownum <= 9
      ORDER  BY loc;
      




As Michel said and Thomas reiterated, do it by Michel's method. I will tell oyu that the code above will not work. Yes, you can make it work by creating data to "fit" into the query, but the logic of this query will not give you the top 3 salaries by location.
Re: How to retrive it?? [message #416365 is a reply to message #416330] Sat, 01 August 2009 03:12 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
I have a simpe table locn with following columns :
-- loc (VARCHAR2(35) --
-- sal NUMBER(9,2) --
In loc column there are 3 loc - loc1, loc2, loc3 with their associated salary . All over 60 rows are their in the table such that each loc corresponding to 20-20-20 rows distributed randomly.
Now I want to retrieve 9 rows such that each 3 rows associated with each loc- 1,2,3, order by descending order of their salary.
I used the following code

      SELECT  loc, sal 
      FROM    (SELECT  loc, sal 
               FROM    locn
               ORDER   BY  sal DESC)
      WHERE  rownum <= 9
      ORDER  BY loc;
      


With this I got the required result , but then if m increasing the rows associaed any one loc- eg- loc2 - 25 rows in table, then result get change instead of 3 i m getting 4 rows of loc-2 , due to which the 1 row of other location get reduced.
Hope you all got what m actually looking for ...
Thank you all for your valuable suggestions. do reply me..
Re: How to retrive it?? [message #416366 is a reply to message #416365] Sat, 01 August 2009 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 31 July 2009 17:39
...
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel



Once more, your query is wrong.
If you get the correct result with your data then your data are biased in such way you unfortunatly get the expected result.

Quote:


[Updated on: Sat, 01 August 2009 03:29]

Report message to a moderator

Re: How to retrive it?? [message #416376 is a reply to message #416366] Sat, 01 August 2009 04:41 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
Michel Cadot wrote on Fri, 31 July 2009 17:39 
...
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel


kk as u suggested m giving more details ....
Create table code:

 
      CREATE TABLE locn (loc VARCHAR2(35), 
                          sal NUMBER); 
      


INSERT table code:

       INSERT INTO locn (loc, sal)
       VALUES(loc1/loc2/loc3, corresponding sal)
       

In the insert there are loc-1,2,3 are randomle inserted with their associated salaries..


Quote:
Once more, your query is wrong.
If you get the correct result with your data then your data are biased in such way you unfortunatly get the expected result.

So , please tell me whats wrong in my query ....thanks for reply...

[Updated on: Sat, 01 August 2009 09:15] by Moderator

Report message to a moderator

Re: How to retrive it?? [message #416388 is a reply to message #416376] Sat, 01 August 2009 06:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is not a working test case

ThomasG@test>       CREATE TABLE locn (loc VARCHAR2(35),
2                   sal NUMBER);

Table created.

ThomasG@test>        INSERT INTO locn (loc, sal)
2       VALUES(loc1/loc2/loc3, corresponding sal);
3
       VALUES(loc1/loc2/loc3, corresponding sal)
                                            *
ERROR at line 2:
ORA-00917: missing comma

ThomasG@test>                                                      


As to your problem, it can possibly be solved by the analytical rank() function. But that would depend on the four-digit version you still haven't told us despite having been asked at least four times.

[Updated on: Sat, 01 August 2009 06:55]

Report message to a moderator

Re: How to retrive it?? [message #416641 is a reply to message #416388] Mon, 03 August 2009 12:23 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
sorry for late reply ......
My oracle version is 9.2.0.1.0
Re: How to retrive it?? [message #416644 is a reply to message #416641] Mon, 03 August 2009 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is in red bold is not this.

Regards
Michel
Re: How to retrive it?? [message #416646 is a reply to message #416644] Mon, 03 August 2009 13:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. Then rank() is supported by 9.2.0.1
icon11.gif  Re: How to retrive it?? [message #416723 is a reply to message #416644] Tue, 04 August 2009 01:55 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
What is in red bold is not this.

hiii, I have posted the test case ....CREATE AND INSERT STATEMENT ALONGWITH THE RESULT I WANT ...please chk out my reply on 1st august....
and if you are asking me for the specification for each row ..then m sorry ...actually i had inserted it randomly so i didnt saved it.....I inserted all this data as I specified - 3 locations with different salaries... each location with 20 rows....
All this I can explain...please tell me what's wrong in my query???
Re: How to retrive it?? [message #416734 is a reply to message #416723] Tue, 04 August 2009 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have posted the test case

No you don't and ThomasG already said it.

Quote:
actually i had inserted it randomly so i didnt saved it.....

So post what you did to fill.
Or recreate a test case.
It does not matter.
Just post a test case.

Regards
Michel
Re: How to retrive it?? [message #417376 is a reply to message #416734] Fri, 07 August 2009 13:08 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
Sorry for late reply ...I am giving the table detail containing loc and salary .
LOC  SAL  
loc1  1500  
loc3  1700  
loc2  1300  
loc3  1000  
loc1  900  
loc2  1900  
loc1  1800  
loc2  2000  
loc3  1500  
loc2  1400  
loc3  2500  
loc1  2100  
loc3  800  
loc2  700   
loc1  850  
loc3  1550  
loc1  1650  
loc2  1450  
loc1  2450  
loc2  2450  
loc3  2650  
loc2  1450  
loc1  2850  
loc2  2800  
loc3  3000  
loc1  2000  
loc1  2200  
loc1  2900   
loc1  1200  
loc3  1200  
loc3  2200  
loc3  2550  
loc3  3550  
loc2  3550  
loc2  3150  
loc2  1150  
loc2  1150  
loc2  1150  
loc2  1050  
loc1  3550  
loc2  3750  
loc3  3650  

42 rows selected.


Please let me know how can I improve my query....
Re: How to retrive it?? [message #417377 is a reply to message #417376] Fri, 07 August 2009 13:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
We can forgive your late reply, but not that you still don't give us a test case.

Since I don't have the time to construct a test case myself, I can only tell you again to look into using the analytical rank() function.
Re: How to retrive it?? [message #417380 is a reply to message #417377] Fri, 07 August 2009 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ThomasG wrote on Fri, 07 August 2009 20:13
We can forgive your late reply, but not that you still don't give us a test case.

Since I don't have the time to construct a test case myself, I can only tell you again to look into using the analytical rank() function.

+1

Regards
Michel

Re: How to retrive it?? [message #417381 is a reply to message #417377] Fri, 07 August 2009 13:35 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
Quote:

Since I don't have the time to construct a test case myself, I can only tell you again to look into using the analytical rank() function.



Thanks thomas as per your suggestion I used analytical rank function & now I am getting perfect result... I code following query ....

SELECT * FROM (SELECT rank() over(partition by loc ORDER BY sal
               DESC) no, loc, sal  FROM locn)
WHERE  no <=3;


Still can you just brif me on what is the test case??I tried to get the meaning from the link provided by michel..but i think I am not able to understood...

[Updated on: Fri, 07 August 2009 13:52] by Moderator

Report message to a moderator

Re: How to retrive it?? [message #417382 is a reply to message #417381] Fri, 07 August 2009 13:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A test case is a script that

1) Creates the necessary tables
2) Fills them with test-data

so that we can run the query you have problems with by ourselves, and make the needed corrections to it.
Re: How to retrive it?? [message #417383 is a reply to message #417381] Fri, 07 August 2009 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Still can you just brif me on what is the test case??

Are you REALLY unable to understand the following?
Quote:
create table and insert statements


Regards
Michel
Re: How to retrive it?? [message #417384 is a reply to message #417382] Fri, 07 August 2009 13:55 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
okk
Thanks for your reply... Smile
Re: How to retrive it?? [message #417385 is a reply to message #417384] Fri, 07 August 2009 14:02 Go to previous messageGo to next message
rhl23
Messages: 25
Registered: July 2009
Location: MUM
Junior Member
Are you REALLY unable to understand the following?
Quote: 
create table and insert statements
Regards
Michel


Dear Michel ,
I know create and insert statement, but i was not aware of the word "test case".
Actually I had given the create table statement and as I told u i just randomly created the table so I can only able to forward you the insert syntax on 1st Aug.. u can chk over there...
So just to let you know what I inserted I code the table data over here....dats it..

Aneway thanks for your help & reply.... Smile
Re: How to retrive it?? [message #417389 is a reply to message #417385] Fri, 07 August 2009 15:33 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
rhl23 wrote on Fri, 07 August 2009 15:02

Actually I had given the create table statement and as I told u i just randomly created the table so I can only able to forward you the insert syntax on 1st Aug.. u can chk over there...
So just to let you know what I inserted I code the table data over here....dats it..



You never gave us INSERT statements. Or at least didn't give us valid ones. You said you created them randomly, ok, so give us a bunch of random INSERT statements, but give us VALID INSERT statements. Why is that so hard to understand?

Here is the syntax for the INSERT statement in Oracle:

http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems025.htm

And enough already with the ebonics.

[Updated on: Fri, 07 August 2009 15:34]

Report message to a moderator

Previous Topic: search string from csv file in blob column
Next Topic: regarding all_tab_columns
Goto Forum:
  


Current Time: Fri Dec 09 21:08:17 CST 2016

Total time taken to generate the page: 0.09262 seconds