Query the maximum minimum and detail of the same group

Database environment: oracle11g

The monitoring data of an existing monitoring station is as follows. Requirements: The maximum minimum and maximum minimum value of each station is queried, and the corresponding time is

If the maximum and minimum values are duplicated, only the data that is the latest time in the repeating data is displayed.

Implementation ideas:

1. According to the station and water level grouping, according to the latest time, there are repeated only the latest time data
2. The data row where the maximum and minimum values are obtained, and then the Union

Prepare a script to build a table, import data

Create TableDse_river_r (STCDvarchar2(8), TM Date,z Number(6,2))Insert  intoDse_river_rValues('80520508', To_date ('2013-07-01 09:00:00','YYYY-MM-DD Hh24:mi:ss'),10.02);Insert  intoDse_river_rValues('80520508', To_date ('2013-07-01 08:00:00','YYYY-MM-DD Hh24:mi:ss'),9.98);Insert  intoDse_river_rValues('80520508', To_date ('2013-07-01 07:00:00','YYYY-MM-DD Hh24:mi:ss'),10.02);Insert  intoDse_river_rValues('60407758', To_date ('2013-07-01 09:00:00','YYYY-MM-DD Hh24:mi:ss'),9.00);Insert  intoDse_river_rValues('60407758', To_date ('2013-07-01 08:00:00','YYYY-MM-DD Hh24:mi:ss'),10.00);Insert  intoDse_river_rValues('60407758', To_date ('2013-07-01 07:00:00','YYYY-MM-DD Hh24:mi:ss'),12.00);Insert  intoDse_river_rValues('60407725', To_date ('2013-07-01 09:00:00','YYYY-MM-DD Hh24:mi:ss'),9.42);Insert  intoDse_river_rValues('60407725', To_date ('2013-07-01 08:00:00','YYYY-MM-DD Hh24:mi:ss'),9.42);Insert  intoDse_river_rValues('60407725', To_date ('2013-07-01 07:00:00','YYYY-MM-DD Hh24:mi:ss'),9.42);Insert  intoDse_river_rValues('60507426', To_date ('2013-07-01 10:00:00','YYYY-MM-DD Hh24:mi:ss'),12.00);Insert  intoDse_river_rValues('60507426', To_date ('2013-07-01 09:00:00','YYYY-MM-DD Hh24:mi:ss'),8.32);Insert  intoDse_river_rValues('60507426', To_date ('2013-07-01 08:00:00','YYYY-MM-DD Hh24:mi:ss'),12.00);Insert  intoDse_river_rValues('60507426', To_date ('2013-07-01 07:00:00','YYYY-MM-DD Hh24:mi:ss'),9.10);

View Code

Now that we have the data, let's implement the query.

1. Filtering Duplicate data

 withx0 as(SELECTStcd, TM, Z, Row_number () Over(PARTITION bySTCD, ZORDER  byTmDESC)                    asRN fromdse_river_r), X1 as(SELECTStcd, TM, Z fromx0WHERERn= 1)

View Code

2.union Maximum, minimum data set for the row

SELECTStcd, TM, Z from(SELECTStcd, TM, Z,MAX(z) Over(PARTITION bySTCD) asZ_max fromx1)WHEREZ=Z_maxUNIONSELECTStcd, TM, Z from(SELECTStcd, TM, Z,MIN(z) Over(PARTITION bySTCD) asZ_min fromx1)WHEREZ=Z_min

View Code

Of course, we can also simplify the union operation a step further and remove the union operation directly

 withx0 as(SELECTStcd, TM, Z, Row_number () Over(PARTITION bySTCD, ZORDER  byTmDESC)                    asRN fromdse_river_r), X1 as(SELECTStcd, TM, Z,MAX(z) Over(PARTITION bySTCD) asZ_max,MIN(z) Over(PARTITION bySTCD) asZ_min fromx0WHERERn= 1)SELECTStcd, TM, Z fromX1WHEREZinch(Z_max, Z_min)

View Code

Okay, now, let's show you the query results.

(End of this article)

Query the maximum minimum and detail of the same group