Search code examples
sqlpostgresqlgreatest-n-per-group

How to get the top row for each studid?

Is it possible to get the following result?

Note: 'S' for summer class.

|    sy   |sem|  studid |studmajor|
|2013-2014| 2 |2012-5037| COM SCI |
|2013-2014| 1 |2012-5037| COM SCI |
|2012-2013| 2 |2012-5037|   ENGG  |
|2012-2013| 1 |2012-5037|   BA    |
|2011-2012| S |2010-2011|   IT    |
|2011-2012| 2 |2010-2011|   IT    |
|2011-2012| 1 |2010-2011|   IT    |
|2010-2011| 2 |2010-2011|   IT    |
|2010-2011| 1 |2010-2011| PUP/CBP |

to

|    sy   |sem|  studid |studmajor|
|2013-2014| 2 |2012-5037| COM SCI |
|2011-2012| S |2010-2011|   IT    |

if can't, maybe the one below?

|    sy   |sem|  studid |studmajor|
|2013-2014| 2 |2012-5037| COM SCI |
|2013-2014| 1 |2012-5037| COM SCI |
|2011-2012| S |2010-2011|   IT    |
|2011-2012| 2 |2010-2011|   IT    |
|2011-2012| 1 |2010-2011|   IT    |
|2010-2011| 2 |2010-2011|   IT    |

I don't want to see the previous major he/she attended, I only want to see his/her current one.

This is my query so far:

select studmajor,sy,sem,studid from semester_student ORDER BY sy DESC,sem DESC
Solution

  • Use DISTINCT ON with the right ORDER BY defining which row to choose from each set:

    SELECT DISTINCT ON (studid) *
FROM   semester_student
ORDER  BY studid DESC, sy DESC, sem DESC;

    See: