Limiting rows in SQL doesn't work properly

Question

I want to get the first 5 rows of every season in my select. I have 4 seasons: SUM, SPR, AUT, WIN. So there should be 20 rows in total. My select looks like this:

select *
from (
    select year, season, ROUND(avg(temperature),1) as avgTemp
    from temperature join month on temperature.MONTH = month.MONTH
    group by (season, year)
    order by season, avgTemp asc
) where rownum <= 5;

It works for just one season. The output is:

1993    AUT 8,7
2007    AUT 9,9
1996    AUT 10
1998    AUT 10
2008    AUT 10,5

But it should look like that:

 1996 SPR        9.6
  1991 SPR       10.3
  2006 SPR       10.3
  2004 SPR       10.6
  1995 SPR       10.6
  1996 SUM       18.9
  1993 SUM       19.1
  2007 SUM       19.5
  1998 SUM       19.5
  2000 SUM       19.6
  1993 AUT        8.7
  2007 AUT        9.9
  1998 AUT       10.0
  1996 AUT       10.0
  2008 AUT       10.5
  1996 WIN         .3
  1991 WIN        1.2
  2003 WIN        1.6
  2006 WIN        1.9
  2005 WIN        2.0

Do you know how to improve the select or do you have any other suggestions? Thanks in advance!

This query is called Top N Per Group and there are tons of examples on how to write this for SQL Server, those which use CTEs and row number and others which don't depending on your version of SQL Server. — Yuck
– Yuck, Commented Dec 4, 2016 at 22:11

Codo · Accepted Answer · 2016-12-04 22:15:14Z

2

You need to do it in three steps:

Group by season and year, calculating the average temperature
Assign a row number: it restart with each season and assigns in ascending order according to the average temperature
Select only the rows with a row number between 1 and 5

The SQL should look like this (untested):

select year, season, avg_temp
from (
    select year, season, avg_temp,
        row_number() over(partition by season order by avg_temp) rn
    from (
        select year, season, ROUND(avg(temperature),1) as avg_temp
        from temperature
        join month on temperature.MONTH = month.MONTH
        group by season, year
    )
)
where rn <= 5;

Update

For you special ordering by season, add this:

order by case season
        when 'SPR' then 1
        when 'SUM' then 2
        when 'AUT' then 3
        when 'WIN' then 4
    end, avg_temp;

edited Dec 4, 2016 at 22:15

answered Dec 4, 2016 at 22:09

Codo

79.7k18 gold badges176 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

mafioso Over a year ago

Thank you Codo, this one works! The last thing I want to know is how can I order by the season starting with SPR as above shown?

Codo Over a year ago

I've added how you can do the sorting, which I forgot in my original answer. As sorting alphabetically would result in the wrong order, you need to assign numbers to the seasons.

mafioso Over a year ago

Works like a charm!

mafioso Over a year ago

Apart from that, do you have any suggestions how to sort the values top-down per season. So that the highest avgTemp of these 5 rows is the first one and not the last one?

Codo Over a year ago

So you want the highest average temperatures for each season and you want them displayed top down in the output? If so, you need to add DESC after both ORDER BY clauses: order by avg_temp in over clause and ... end, avg_temp desc at the very end.

|

Matt · Accepted Answer · 2016-12-04 22:21:13Z

WITH cteAverageTempByYearBySeason AS (
    SELECT
       year
       ,season
       ,ROUND(AVG(temperature),1) as AvgTemp
    FROM
       Temperature t
       INNER JOIN Month m
       On t.MONTH = m.MONTH
    GROUP BY
       year
       ,season
)

, cteRowNumber AS (
    SELECT
       *
       ,ROW_NUMBER() OVER (PARTITION BY season ORDER BY AvgTemp ASC) as RowNumber
    FROM
       cteAverageTempByYearBySeason
)

SELECT *
FROM
    cteRowNumber
WHERE
    RowNumber <= 5

Here is an example. I broke out the derived tables into Common Table Expressions to make the logic more noticeable. You need to create a PARTITIONED ROW_NUMBER() not just use oracles special rownumber. The latter will only return the same as TOP/LIMIT 5 where as the former will allow you to identify 5 rows per season.

Edit added a neat trick for your order by so you don't have to write a case expression. This one utilizes your month number which I assume is what MONTH column is.

WITH cteAverageTempByYearBySeason AS (
    SELECT
       year
       ,season
       ,ROUND(AVG(temperature),1) as AvgTemp
       ,MAX(m.MONTH) as SeasonOrderBy
    FROM
       Temperature t
       INNER JOIN Month m
       On t.MONTH = m.MONTH
    GROUP BY
       year
       ,season
)

, cteRowNumber AS (
    SELECT
       *
       ,ROW_NUMBER() OVER (PARTITION BY season ORDER BY AvgTemp ASC) as RowNumber
    FROM
       cteAverageTempByYearBySeason
)

SELECT
    year
    ,season
    ,AVG
FROM
    cteRowNumber
WHERE
    RowNumber <= 5
ORDER BY
    SeasonOrderBy
    ,AvgTemp
    ,Year

Felix Pamittan · Accepted Answer · 2016-12-04 22:04:26Z

0

You need to use row_number to get 5 rows for each grouping:

select
    year,
    season,
    round(avg(temperature), 1) as avgTemp
from (
    select *,
        row_number() over(partition by season, year order by season, avgTemp) as rn
    from temperature t
    join month m
        on m.MONTH = t.MONTH
) a
where
    a.rn <= 1

answered Dec 4, 2016 at 22:04

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

1 Comment

mafioso Over a year ago

I get the following error: "FROM keyword not found where expected"

Collectives™ on Stack Overflow

Limiting rows in SQL doesn't work properly

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related