How to take a grouped maximum record

Group Max Record

Like what Number of ordinal names 1 A 20 2 A 10 1 B 20 2 B 40 3 B 10 1 C 20 2 C 40

Sub-query:

SELECT * FROM table where (ordinal, name) in (select Max (ordinal), name from table group by name)

Analytic functions:

Select serial number, name, quantity from
(select serial number, name, quantity
, Row_number () over (partition by name order BY ordinal desc) rn
Form Tab_name)
where rn=1

Or

Select serial number, name, quantity from
(select serial number, name, quantity
, Max (ordinal) over (partition by name) RN
Form Tab_name)

where rn= sequence number

Note: Max's field can only be a number type field, and if it is of type date, an error is indicated. The date type can be done with the row_number () above.

Oracle group FETCH first record ID apply_id
1 1
2 1
3 1
4 2
5 2
6 3
7 3
8 3

Remove
ID apply_id
3 1
5 2
8 3

Select Alx_a.id
From
(select Id,apply_id,rownum rid from table) Alx_a,
(select Id,apply_id,rownum rid from table) Alx_b
where alx_a.apply_id = alx_b.apply_id and alx_a.id <= alx_b.id
GROUP BY alx_a.id,alx_a.apply_id
Having count (*) = 1 Group and fetch the top N records in each group

There is a data table Exam_result (transcript) in Oracle,

One of the records in the table describes "the results of a certain student's test in one class."

CREATE TABLE Exam_result
(
ID Number (TEN) not NULL,--primary key
CLASSID Number (TEN) not NULL,--class ID, associated to class table
USERID Number (TEN) not NULL,--user ID, associated to user table
Examid Number (TEN) not NULL,--quiz ID, associated to quiz sheet
Result Number (3)--Score
)

Now requires statistics to complete the first 3 grades of the test paper ID

That is completed the first 3 of the test paper ID 1, completed the test paper ID 2 of the first 3, completed the test paper ID 3 of the top 3

SELECT * FROM (
Select
E.classid,
E.userid,
E.examid,
E.result,
Row_number () over (partition by E.examid order by E.examid, E.result Desc) rn
From Exam_result E
where E.examid in (all-in-a-
) where RN <= 3

How to take a grouped maximum record