ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] )

postgres=# create table student(id serial,name character varying,course character varying,score integer);
CREATE TABLE
postgres=# 
postgres=# \d student
    Table "public.student"
 Column | Type |   Modifiers   
--------+-------------------+----------------------------------------------
 id | integer  | not null default nextval('student_id_seq'::regclass)
 name | character varying | 
 course | character varying | 
 score | integer  | 

 insert into student (name,course,score) values('周潤發(fā)','語文',89);
 insert into student (name,course,score) values('周潤發(fā)','數(shù)學(xué)',99);
 insert into student (name,course,score) values('周潤發(fā)','外語',67);
 insert into student (name,course,score) values('周潤發(fā)','物理',77);
 insert into student (name,course,score) values('周潤發(fā)','化學(xué)',87);
 insert into student (name,course,score) values('周星馳','語文',91);
 insert into student (name,course,score) values('周星馳','數(shù)學(xué)',81);
 insert into student (name,course,score) values('周星馳','外語',88);
 insert into student (name,course,score) values('周星馳','物理',68);
 insert into student (name,course,score) values('周星馳','化學(xué)',83);
 insert into student (name,course,score) values('黎明','語文',85);
 insert into student (name,course,score) values('黎明','數(shù)學(xué)',65);
 insert into student (name,course,score) values('黎明','外語',95);
 insert into student (name,course,score) values('黎明','物理',90);
 insert into student (name,course,score) values('黎明','化學(xué)',78);

postgres=# select *,row_number() over(order by score desc)rn from student;
 id | name | course | score | rn 
----+--------+--------+-------+----
 2 | 周潤發(fā) | 數(shù)學(xué) | 99 | 1
 13 | 黎明 | 外語 | 95 | 2
 6 | 周星馳 | 語文 | 91 | 3
 14 | 黎明 | 物理 | 90 | 4
 1 | 周潤發(fā) | 語文 | 89 | 5
 8 | 周星馳 | 外語 | 88 | 6
 5 | 周潤發(fā) | 化學(xué) | 87 | 7
 11 | 黎明 | 語文 | 85 | 8
 10 | 周星馳 | 化學(xué) | 83 | 9
 7 | 周星馳 | 數(shù)學(xué) | 81 | 10
 15 | 黎明 | 化學(xué) | 78 | 11
 4 | 周潤發(fā) | 物理 | 77 | 12
 9 | 周星馳 | 物理 | 68 | 13
 3 | 周潤發(fā) | 外語 | 67 | 14
 12 | 黎明 | 數(shù)學(xué) | 65 | 15
(15 rows)

postgres=# select *,row_number() over(partition by course order by score desc)rn from student;
 id | name | course | score | rn 
----+--------+--------+-------+----
 5 | 周潤發(fā) | 化學(xué) | 87 | 1
 10 | 周星馳 | 化學(xué) | 83 | 2
 15 | 黎明 | 化學(xué) | 78 | 3
 13 | 黎明 | 外語 | 95 | 1
 8 | 周星馳 | 外語 | 88 | 2
 3 | 周潤發(fā) | 外語 | 67 | 3
 2 | 周潤發(fā) | 數(shù)學(xué) | 99 | 1
 7 | 周星馳 | 數(shù)學(xué) | 81 | 2
 12 | 黎明 | 數(shù)學(xué) | 65 | 3
 14 | 黎明 | 物理 | 90 | 1
 4 | 周潤發(fā) | 物理 | 77 | 2
 9 | 周星馳 | 物理 | 68 | 3
 6 | 周星馳 | 語文 | 91 | 1
 1 | 周潤發(fā) | 語文 | 89 | 2
 11 | 黎明 | 語文 | 85 | 3
(15 rows)

postgres=# select * from(select *,row_number() over(partition by course order by score desc)rn from student)t where rn=1;
 id | name | course | score | rn 
----+--------+--------+-------+----
 5 | 周潤發(fā) | 化學(xué) | 87 | 1
 13 | 黎明 | 外語 | 95 | 1
 2 | 周潤發(fā) | 數(shù)學(xué) | 99 | 1
 14 | 黎明 | 物理 | 90 | 1
 6 | 周星馳 | 語文 | 91 | 1
(5 rows)

postgres=# select * from(select *,row_number() over(partition by course order by score)rn from student)t where rn=1;
 id | name | course | score | rn 
----+--------+--------+-------+----
 15 | 黎明 | 化學(xué) | 78 | 1
 3 | 周潤發(fā) | 外語 | 67 | 1
 12 | 黎明 | 數(shù)學(xué) | 65 | 1
 9 | 周星馳 | 物理 | 68 | 1
 11 | 黎明 | 語文 | 85 | 1
(5 rows)

SELECT ROW_NUMBER
 () OVER ( ORDER BY starttime DESC ) "id",
 starttime AS "text",
 starttime 
FROM
 warning_products 
WHERE
 pid_model = '結(jié)果' 
 AND starttime IS NOT NULL 
GROUP BY
 starttime

 () OVER ( ORDER BY starttime ASC ) "id",

ROW_NUMBER () OVER() “id”