Home » SQL & PL/SQL » SQL & PL/SQL » Max value row and lookup value
Max value row and lookup value [message #683653] Wed, 10 February 2021 12:07 Go to next message
suji6281
Messages: 126
Registered: September 2014
Senior Member
Hi Team,

Can you please help me with the Query with the below criteria.

1) select highest version rows from hdr_tbl for each id
2) select corresponding rows from line_tbl
3) one of select field should be derived from other mapping table (lookup_tbl)

hdr_tbl:
id version name address1
1    1     John Texas
1    2     John Mexico
1    3     John NewYork
2    3     Mike Florida
2    4     Mike Alaska
line_tbl:
id  version qty amount  code
1     1      25 25000    A1
1     2      12 12000    A2
1     3      10 10000    A3
2     3      22 22000    A2
2     4      44 44000    A1 
lookup_tbl:
code   descr
A1     buyer
A2     seller
A3     customer
output:

id version name    qty     amount descr
1   3      John     10     10000  customer
2   4      Mike     44     44000  buyer

Thank you.

Regards
Suji


Re: Max value row and lookup value [message #683654 is a reply to message #683653] Wed, 10 February 2021 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68045
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 16 October 2018 10:35

As always you don't:
- format your post
- provide a test case
- give your Oracle version
- feedback in your previous topic
...
http://www.orafaq.com/forum/mv/msg/205605/675007/#msg_675007

Re: Max value row and lookup value [message #683655 is a reply to message #683653] Wed, 10 February 2021 12:42 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried?
Re: Max value row and lookup value [message #683657 is a reply to message #683655] Wed, 10 February 2021 17:22 Go to previous messageGo to next message
suji6281
Messages: 126
Registered: September 2014
Senior Member
Hi John,

I tried with below sql.

SELECT H.ID, H.VERSION, H.NAME, L.QTY, C.DECSR
FROM HDR_TBL H, LINE_TBL L LEFT OUTER JOIN LOOKUP_TBL
ON (L.CODE = C.CODE)
WHERE H.ID =L.ID AND
H.VERSION= (SELECT MAX(H1.VERSION) FROM HDR_TBL H1 WHERE H.ID =H1.ID)
AND H.VERSION = L.VERSION

This sql query works fine when you query for one ID.

Thank you.

Regards
Suji

Re: Max value row and lookup value [message #683658 is a reply to message #683654] Wed, 10 February 2021 17:24 Go to previous message
suji6281
Messages: 126
Registered: September 2014
Senior Member
Sure Michel, I will follow the guide lines and will provide feedback on previous responses. Thank you.
Previous Topic: Parse Json using Oracle SQL
Next Topic: date validation
Goto Forum:
  


Current Time: Sat Dec 04 03:04:20 CST 2021