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
CodePudding user response:
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: