How to get top 150 results based on a column data

Question

I have a TEMP_TABLE with the below data

SOURCE - Number
DESTINATION- Number
COUNT- Number (Total count of transactions)
SUM- Number (Total sum of transactions)

Sample data:

SOURCE    DESTINATION COUNT SUM
123123123 99009900    65    1000000
123123123 88880303    12    90000
191113111 98980101    277   5000000
191113111 77778585    5     20000
191113111 56789547    740   75000000

I'm trying to get Top 150 results per source number with the destination number based on the value of COUNT. If 123123123 has 200 rows in the table with multiple destinations, I trying to get the top 150 results of 123123123 and exclude the other 50 rows.

score 2 · Accepted Answer · answered Jan 10 '18 at 17:57

You can use the row_number window function to assign a number to each row (per source number) and then take only the top 150 for each one:
(Note: sum and count are reserved words in Oracle SQL. To avoid ugly escaping the example below renames them to sum_col and count_col respectively:

SELECT source, destination, count_col, sum_col
FROM   (SELECT source, destination, count_col, sum_col,
               ROW_NUMBER() OVER (PARTITION BY source
                                  ORDER BY count_col DESC) AS rn
        FROM   temp_table) t
WHERE  rn <= 150

How to get top 150 results based on a column data

1 Answers1