CODEWITHSUNDEEP
sqloracle-databasegroup-bymaxgreatest-n-per-group
Harry
Harry

Reputation: 39

Finding the most recent date in SQL for a range of rows

I have a table of course work marks, with the table headings:

Module code, coursework numbers, student, date submitted, mark

Sample data in order of table headings:

Maths, 1, Parry, 12-JUN-92, 20

Maths, 2, Parry, 13-JUN-92, 20

Maths, 2, Parry, 15-JUN-92, 25

Expected data after query

Maths, 1, Parry, 12-JUN-92, 20

Maths, 2, Parry, 15-JUN-92, 25

Sometimes a student retakes an exam and they have an additional row for a piece of coursework. I need to try get only the latest coursework’s in a table. The following works when I isolate a particular student:

SELECT * 
FROM TABLE
WHERE NAME = ‘NAME’
AND DATE IN (SELECT MAX(DATE)
            FROM TABLE 
            WHERE NAME = ‘NAME’
            GROUP BY MODULE_CODE, COURSEWORK_NUMBER, STUDENT)

This provides the correct solution for that person, giving me the most recent dates for each row (each coursework) in the table. However, this:

 SELECT * 
FROM TABLE
AND DATE IN (SELECT MAX(DATE)
             FROM TABLE 
             GROUP BY MODULE_CODE, COURSEWORK_NUMBER, STUDENT)

Does not provide me with the same table but for every person who has attempted the coursework. Where am I going wrong? Sorry if the details are a bit sparse, but I’m worried about plagiarism.

Working with SQL plus

Upvotes: 0

Views: 136

Answers (3)

GMB
GMB

Reputation: 222392

This is a good spot to use Oracle keep syntax:

select
    module_code,
    course_work_number,
    student,
    max(date_submitted) date_submitted,
    max(mark) keep(dense_rank first order by date_submitted desc) mark
from mytable
group by module_code, course_work_number, student

Demo on DB Fiddle:

MODULE_CODE | COURSE_WORK_NUMBER | STUDENT | DATE_SUBMITTED | MARK
:---------- | -----------------: | :------ | :------------- | ---:
Maths       |                  1 | Parry   | 12-JUN-92      |   20
Maths       |                  2 | Parry   | 15-JUN-92      |   25

Upvotes: 2

CR7SMS
CR7SMS

Reputation: 2584

You could use the row_number function to solve this:

select x.*
(SELECT a.*,row_number() over(partition by name order by date desc) as row1
FROM TABLE a)x
where x.row1=1

The idea is to assign a row number based on the date and then select the cases where row number is 1. Hope this helps.

Upvotes: 0

Pan
Pan

Reputation: 341

You are looking for a groupwise maximum. See this article from MySQL: https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html I'm not sure about the correct syntax for Oracle, but it should be similar. At least the query structure should put you on the right path.

Upvotes: 1

Related Questions