PostgreSQL query to return 10 highest numbers in ASC order

January 16, 2023

I found an issue in my query:

SELECT * FROM stocks_historic
WHERE ticker = x
ORDER BY YEAR ASC
LIMIT 10

Current table is this one:

stocks_historic (
    historic_id SMALLSERIAL UNIQUE,
    ticker VARCHAR(10) NOT NULL,
    year VARCHAR(5) NOT NULL,
    eps NUMERIC(6,2) NOT NULL,
    operatingCashFlow NUMERIC(12,4) NOT NULL,
    shares NUMERIC(12,4) NOT NULL,
    cash NUMERIC(12,4) NOT NULL,
    currentLiabilities NUMERIC(12,4) NOT NULL,
    currentAssets NUMERIC(12,4) NOT NULL,
    incomeBeforeTax NUMERIC(12,4) NOT NULL,
    incomeTaxExpense NUMERIC(12,4) NOT NULL,
    totalDebt NUMERIC(12,4) NOT NULL,
    revenue NUMERIC(12,4) NOT NULL,
    costOfGoodSold NUMERIC(12,4) NOT NULL,
    operatingIncome NUMERIC(12,4) NOT NULL,
    equity NUMERIC(12,4) NOT NULL,
    capitalExpenditures NUMERIC(12,4) NOT NULL,
    fcf NUMERIC(12,4) NOT NULL,
    PRIMARY KEY (ticker, year)
);

Expected result:

If I have 15 rows with the same ticker where year go from 2010 to 2025, I expect to get last 10 years in ASC order:

historic_id: 1,
year: 2015,
historic_id: 2,
year: 2016,
historic_id: 3,
year: 2017,
...

The result is that I get the first 10 numbers, but Since I want the highest 10, is not working.

I know I can achieve that changing ASC for DESC, but that would return me years in an undesired order. The frontend is expecting years in ASC order so I would have to change everything to fit it.

Is there any way to get those 10 rows ASC ordered?

Thanks, Ruben.

>Solution :

To return the last 10 years you need ORDER BY ... DESC. If you want the results to be in ascending order, you’ll have to reorder them, eg :

SELECT * 
FROM (SELECT * 
      FROM stocks_historic
      WHERE ticker = x
      ORDER BY YEAR DESC
      LIMIT 10)
ORDER BY YEAR ASC

While it’s possible to use other ranking functions to identify the last 10 rows, eg ROW_NUMBER() OVER(ORDER BY YEAR DESC) the generated execution plan will still have to order the data twice – once to get the ROW_NUMBER and once to reorder the results.