-2

I have a table (myTab), which has 3 columns(id, marketValue, stock).

I want to group top n rows ordered by marketValue. I can think of this nested query.

SELECT AVG(marketValue), stock FROM (SELECT marketValue, stock FROM myTab
order by(marketValue) desc LIMIT(n)) GROUP BY stock.

Can we optimise it further? This is not to get top n rows of each group.

SAMPLE TABLE

enter image description here

If I group top 3 rows, the expected result should be :

enter image description here

Itisha
  • 30
  • 5
  • Please read [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results). – Tim Biegeleisen Sep 26 '19 at 04:36
  • @TimBiegeleisen I don't want for *each* group but group only top n rows. – Itisha Sep 26 '19 at 04:44
  • 1
    Possible duplicate of [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Gilles Gouaillardet Sep 26 '19 at 04:56
  • @Itisha share some sample records and expected resultset. – James Sep 26 '19 at 04:57
  • @James shared for your reference. – Itisha Sep 26 '19 at 05:19
  • 2
    [Stack Overflow Discourages screenshots of code, errors & data](https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors). It is likely, the question will be downvoted, for containing unnecessary screenshots. By using screenshots, you are discouraging anyone from assisting you. No one wants to retype your stuff, from a screenshot, and screenshots are often, not readable. – Trenton McKinney Sep 26 '19 at 05:35
  • Possible duplicate of [SQL - aggregate function - GROUP BY clause](https://stackoverflow.com/questions/13960671/sql-aggregate-function-group-by-clause) – er.irfankhan11 Sep 26 '19 at 05:42
  • Without an `ORDER BY` clause your query is meaningless as the rows it returns are indeterminate. – Nick Sep 26 '19 at 06:04
  • @Itisha I hope you have the best one. As Nick suggested you can use `order by` clause or try including `pk` inside the sub query ti avoid indeterminate rows. – James Sep 26 '19 at 06:20

1 Answers1

0

SQL tables represents unordered sets. So when we talked about first or last rows, We need to be deterministic with order of rows. Hence you need an order by clause in your inner query to get the top n rows. Rest looks fine - 

SELECT AVG(marketValue), stock
FROM (SELECT marketValue, stock
      FROM myTab
      ORDER BY id
      LIMIT(n)
) X
GROUP BY stock
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40