Queries per day per room no. 1, oracle query of SQL-CodePudding

Date room number other fields ID
The 2018-9-1 101 1... 11.
The 2018-9-1 101 2... 12
The 2018-9-1 101 3... 13
The 2018-9-1 102 1... 14
The 2018-9-1 102 2... 15
,,,,,,,,,,,,,
The 2018-9-2 102 1... 16
The 2018-9-2 102 2... 17
The 2018-9-2 102 3... 18
The 2018-9-2 103 1... 19
The 2018-9-2 103 2... 20
The 2018-9-2 103 3... 21
The 2018-9-2 103 4... 22
,,,,,,,,,
,,,,,,,,,

Results:
Date room number other fields ID
The 2018-9-1 101 1... 11.
The 2018-9-1 102 1... 14
The 2018-9-2 102 1... 16
The 2018-9-2 103 1... 19
,,,,,,,

Seek your advice ~ ~

CodePudding user response:

Min (to_number (ID)) over (partition by trunc (date), room)

CodePudding user response:

, the select * from (select date, room number, ID, row_number () over the order by (partition by date, room number asc) rn from Table) where rn=1

CodePudding user response:

Specific table name and field, you add
SQL implementation is as follows:

 
, the select date, room number, the other fields, ID 
The from (select t. *, row_number () over the order by (partition by date, room number) rn from table name t) 
Where an rn=1