OVER (PARTITION BY) mixing up rows

[Theruni Pethiyagoda]

Using

ROW_NUMBER() OVER ( PARTITION BY course ORDER BY mark ASC) AS RowAsc,

ROW_NUMBER() OVER ( PARTITION BY course ORDER BY mark DESC) AS RowDesc

In my clause I get the ascending column mixing up and so the middle rows dont match so I cant get the median. If I just check RowAsc on its own it is ordered but how do I stop it from mixing up when they are calculated?

Any help would be great because I have the right answers but I'm missing one course because its median isnt being calculated!

course | mark  | rowasc | rowdesc 

  48642 | 59.00 |     11 |      83

  48642 | 58.00 |      9 |      84

  48642 | 58.00 |      8 |      85

  48642 | 58.00 |     10 |      86

  48642 | 56.00 |      7 |      87

  48642 | 55.00 |      6 |      88

  48642 | 54.00 |      5 |      89

  48642 | 53.00 |      4 |      90

  48642 | 53.00 |      3 |      91

  48642 | 34.00 |      2 |      92

  48642 | 25.00 |      1 |      93

[Laura S]

You need to add student as a second parameter of the ORDER BY clause for both ascending and descending. See here - https://www.postgresql.org/docs/9.5/static/queries-order.html