'alternative method to get maximum top 10 records

I was asked to write a query in an interview to find the highest top 10 transactions in the month of January, 2022 without using limit, top or rank function?

Could anyone help? You can assume any imaginary table.

I used this table structure for my query

cust_id cust_nm trans_dt trans_amt

Let me know if you have any questions.

sql


Solution 1:[1]

Use ROW_NUMBER() instead of RANK(). Or if that is also not allowed, this would be in my top 5 most stupid methods of getting a top n of something:

(using T-SQL as you tagged several RDBMSs)

WITH data AS
(
  -- get the transactions from january with the necessary joins here
), stupid_ranking_method AS (
   SELECT * 
        , MAX(amount) 
            OVER (ORDER BY amount DESC 
                  ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as ranking_helper
     FROM data
)
SELECT amount 
  FROM stupid_ranking_method
 WHERE ranking_helper = (SELECT MAX(amount) FROM stupid_ranking_method)

working demo on dbfiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MarcinJ

Related Questions