| select first row after appying order by for multiple records [message #638040] |
Mon, 01 June 2015 11:31  |
 |
knowit2015
Messages: 1
Registered: June 2015
|
Junior Member |
|
|
Could somebody help me with this query?
I have a scenario where i need to write a select query which retrieve value from 2 tables by looking at value from 3rd table after applying order by asc.
eg:
MyTable1
ID keyId flag
1 123 Y
2 456 Y
3 789 N
4 123 Y
MyTable2
keyId type val
123 A XYZ
456 B QWE
789 C RTY
123 D ASD
MyTable3
type rank
A 3
B 2
C 1
D 4
I need to write a select query which will return MyTable1.ID, MyTable2.val for all records in MyTable1 where flag=Y.
MyTable2.val should be selected by joining MyTable2.type=MyTable3.type order by MyTable3.rank asc and then selecting first row.
Also MyTable1.KeyId=MyTable2.KeyId
|
|
|
|
|
|
| Re: select first row after appying order by for multiple records [message #638042 is a reply to message #638040] |
Mon, 01 June 2015 11:39  |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
knowit2015 wrote on Mon, 01 June 2015 18:31...I need to write a select query which will return MyTable1.ID, MyTable2.val for all records in MyTable1 where flag=Y.
MyTable2.val should be selected by joining MyTable2.type=MyTable3.type order by MyTable3.rank asc and then selecting first row.
Also MyTable1.KeyId=MyTable2.KeyId
OK, you have all the elements, what prevent you from writing the query?
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your tables and data.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
