The first group by syntax

SELECT * FROM STUDENT group by SEX; SELECT * FROM STUDENT group by SEX; Unfortunately, the execution failed, prompting: Not the GROUP BY expression! The reason is group by group query. The field after select clause must come from the group field after Group by. SQL: SELECT SSEX FROM STUDENT GROUP BY SEX;

This may not give you the results you want

Row_number () over()

introduce

Row_number () over(partition by col1 order by col2) group by col1, sort by col2 within each group, and calculate the number of rows within each group (unique row within each group).

sql

select rk.*.row_number(a)over(partition by riskcode order by versioncode desc) row_number
  from aa_riskpolicy rk
Copy the code

Query result:

Rank () over()

introduce

Rank () is a jump sort, and if there are two second places then there is a fourth place.

sql

select t.*.rank(a)over(partition by accno order by createDate) rank from Test t 
Copy the code

Query result:



Rank by time. If time is repeated, rank is repeated.

Dense_rank () over()

introduce

Dense_rank () is also sequential, with two second places still following the third.

sql

select t.*.dense_rank(a)over(partition by accno order by createDate) dense_rank from Test t
Copy the code

Query result:



You can see that two fields with the same CREATEDATE are two 2s followed by three.

After grouping and sorting, the top number of data records in each group were taken out respectively

The subquery is used to group and sort the data, and then filter the data that meets the conditions

SQL query the first three items in each group

select createDate, accno, money, row_number
  from (select t.*.row_number(a)over(partition by accno order by createDate) row_number
          from Test t) t1
 where row_number < 4
Copy the code

Query result: