PostgreSQL query to return 10 highest numbers in ASC order-CodePudding

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.

CodePudding user response：

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) z
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.

CodePudding user response：

If you order by year ascending

ORDER BY year ASC

and then take the first ten rows, you get the first ten years, of course.

If you ordered by year descending

ORDER BY year DESC

you'd get the last years.

If you want the last ten years shown in ascending order, then get that result first, then sort again:

SELECT *
FROM
(
  SELECT *
  FROM stocks_historic
  WHERE ticker = x
  ORDER BY year DESC
  FETCH FIRST 10 ROWS
) last_ten_years
ORDER BY year ASC;

CodePudding user response：

    SELECT * FROM stocks_historic
    WHERE ticker = x
    ORDER BY YEAR ASC
    fetch first 10 rows only

CodePudding user response：

You can do a subquery for getting the data, and an outer query to order it:

select *
from
    (SELECT * 
    FROM stocks_historic
    WHERE ticker = x
    ORDER BY YEAR desc
    LIMIT 10) temp
order by year asc