double selection of max values in a table

[scarletAni]

I have a table JUNK such as
junk_id
junk_chk_date
junk_chk_nbr
junk_itm_no

I need to select the value of junk_itm_no base on max(junk_chk_date) and max(junk_chk_nbr)

Eg:
1 A001 1999-10-03 012
2 A001 1999-10-05 011
3 B001 1999-07-04 010
4 B001 1999-07-08 009

I want the result to be
4 B001 1999-07-08 009

I have already found a way to do this. I want to know if there are other ways. Please mail whatever is possible. I will give my answer in the end.

 

[dnoeth]

The latest junk_chk_date for the highest junk_chk_nbr?
Is the combination of junk_chk_date and junk_chk_nbr unique?
Just a single row?

select * from junk
qualify
rank() over (order by junk_chk_nbr desc,
junk_chk_date desc) = 1

Probably faster than nested subqueries with MAX

Dieter

 

[scarletAni]

It works !!
I had tried using the max .
There is a small correction to the above data.
Consider this data
1 A001 1999-10-03 012
1 A001 1999-10-05 011
1 B001 1999-07-04 010
1 B001 1999-07-08 009
2 A001 1999-10-03 012
2 A001 1999-10-05 011

Now I want to get the max(junk_chk_date and junk_chk_nbr) for each id.

I did a
Select junk_id, max(junk_chk_date || junk_chk_nbr) from junk
group by 1

Then I join this with the original table to get the itm_no.

After your suggestion I tried this and it works in 1 step. Thanx

select junk_id, junk_chk_date, junk_chk_nbr, junk_itm_no from dwtmp1.junk
group by 1
order by 1,2,3 desc
qualify
rank(chk_nbr , chk_dt) = 1