I use oracle as a database. I want to get the courses of last three semesters ordered by semester.
This is an example of data:
semester course userId
10 a EM-1
8 b EM-1
9 c EM-1
9 h EM-1
2 d EM-1
2 v EM-1
2 b EM-1
3 c EM-1
3 a EM-1
3 J EM-1
3 K EM-1
3 M EM-1
The correct result should be:
2 d EM-1
2 v EM-1
2 b EM-1
3 c EM-1
3 a EM-1
3 J EM-1
3 K EM-1
3 M EM-1
8 b EM-1
I tried without success with:
SELECT * FROM
(
SELECT semester, course
, ROW_NUMBER() OVER (ORDER BY course) rno
FROM emp
ORDER BY course
)
WHERE rno <= 3
You should be using DENSE_RANK() on the semester:
WITH cte AS (
SELECT e.*, DENSE_RANK() OVER (ORDER BY semester) dr
FROM emp e
)
SELECT semester, course, userId
FROM cte
WHERE dr <= 3
ORDER BY semester;