-1

I am struggling to find a correct way to query the following scenario. Consider the below table as example, where I want the row with index_no 124 for that dvpc and pid value of the columns. Please suggest.

Index_No    dvpc     pid     rate
123         123      30      0.01
124         123      30      0.02
Sachin Vaidya
  • 125
  • 3
  • 13
  • https://stackoverflow.com/questions/tagged/greatest-n-per-group –  Oct 31 '17 at 12:24
  • If there are multiple rows-per-group that have the maximum value then `RANK() OVER ( PARTITION BY dvpc, pid ORDER BY index_no DESC )` will give all those rows a rank of 1 - if you only want a single row then use `ROW_NUMBER` instead of `RANK`. – MT0 Oct 31 '17 at 12:49
  • Row_number it is.. don't know what happened but I just didn't strike me. thank you very much !! – Sachin Vaidya Oct 31 '17 at 13:28

2 Answers2

0

You could use max() and GROUP BY.

For example:

select
    dvpc,
    pid,
    max(index_no) as max_index
from
    your_table
group by
    dvpc,
    pid

If you require the rate for that index, then something like this would work

select
    t.*
from
    your_table t
inner join
(
select
    dvpc,
    pid,
    max(index_no) as max_index
from
    your_table
group by
    dvpc,
    pid
) s
on t.index_no = s.max_index

Hope this helps

Matthew Hart
  • 359
  • 2
  • 10
  • This would work but using `ROW_NUMBER` or `RANK` analytic functions would be much more efficient as it can be done without a self-join. – MT0 Oct 31 '17 at 12:50
0

Use ROW_NUMBER to assign a ranking on Index_No for each value of dvpc and pid, then filter to the ones with the highest rank.

SELECT Index_No, dvpc, pid, rate
FROM (SELECT Index_No, dvpc, pid, rate,
        ROW_NUMBER() OVER (PARTITION BY dvpc, pid ORDER BY Index_no DESC) rn
      FROM your_table) yt
WHERE rn = 1;
markusk
  • 6,477
  • 34
  • 39
  • Thanks for your response. I considered it earlier but somehow it is giving me duplicate rows even with the rank column. – Sachin Vaidya Oct 31 '17 at 12:45
  • I assumed you *wanted* duplicates if there were two rows with the same maximum value. If you don't, use `ROW_NUMBER` instead of `RANK`. I've updated my answer accordingly. – markusk Oct 31 '17 at 12:55