0

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Michael
  • 43
  • 6

1 Answers1

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228