Forum Stats

  • 3,824,983 Users
  • 2,260,449 Discussions
  • 7,896,370 Comments

Discussions

Hello! Filtering the dataset with last inserted values

User_CREPM
User_CREPM Member Posts: 7 Green Ribbon
edited May 10, 2022 1:46PM in SQL & PL/SQL

Hello!

I ask for question about sql . I have a sql command like that :

SELECT P.ID_PERMIS,

    P.PERMIS,

    H.ID_HISTORIQUE,

    H.DATEHISTORIQUE,

FROM EXPL_PERMIS P, EXPL_HISTORIQUEPERMIS H

WHERE P.ID_PERMIS=H.PERMIS_ID_PERMIS

ORDER BY ID_PERMIS, ID_HISTORIQUE;

and it return the following :

And I want to obtain the last ID_HISTORIQUE or DATEHISTORIQUE for every ID_PERMIS like that :


Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,030 Red Diamond
    edited May 10, 2022 1:42PM Answer ✓

    For all problems like this one, consider MATCH_RECOGNIZE.

    Since I don't have a sample version of your table, I'll use the scott.emp table (which you probably have on your system) to illustrate. Scott.emp contains:

    DEPTNO ENAME   HIREDATE        SAL JOB
    ------ -------- ----------- ------ ---------
        10 CLARK    09-Jun-1981   2450 MANAGER
        10 KING     17-Nov-1981   5000 PRESIDENT
        10 MILLER   23-Jan-1982   1300 CLERK
        20 SMITH    17-Dec-1980    800 CLERK
        20 JONES    02-Apr-1981   2975 MANAGER
        20 FORD     03-Dec-1981   3000 ANALYST
        20 SCOTT    19-Apr-1987   3000 ANALYST
        20 ADAMS    23-May-1987   1100 CLERK
        30 ALLEN    20-Feb-1981   1600 SALESMAN
        30 WARD     22-Feb-1981   1250 SALESMAN
        30 BLAKE    01-May-1981   2850 MANAGER
        30 TURNER   08-Sep-1981   1500 SALESMAN
        30 MARTIN   28-Sep-1981   1250 SALESMAN
        30 JAMES    03-Dec-1981    950 CLERK
    

    14 rows in all. If we want to display only one row per deptno, the row with the latest hiredate, like this:

    DEPTNO ENAME  HIREDATE       SAL JOB
    ------ ------ ----------- ------ ---------
        10 MILLER 23-Jan-1982   1300 CLERK
        20 ADAMS  23-May-1987   1100 CLERK
        30 JAMES  03-Dec-1981    950 CLERK
    

    then we can do it like this:

    SELECT   *
    FROM	 scott.emp
    MATCH_RECOGNIZE
         (
    	   PARTITION BY   deptno
    	   ORDER BY	  hiredate   DESC
    	   MEASURES	  FIRST (ename)	        AS ename
    	   ,		  FIRST (hiredate)	AS hiredate
    	   ,		  FIRST (sal)		AS sal
    	   ,		  FIRST (job)		AS job
    	   ONE ROW PER MATCH
    	   PATTERN        ( x* )
    	   DEFINE  	  x 	 AS 1 = 1
    	 )
    ORDER BY deptno -- or whatever you want
    ;
    


Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,030 Red Diamond

    Hi, @User_CREPM

    Welcome to the forum!

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. (Showing the results you DON'T want can be helpful, too, but always post the results you DO want.) Always post your complete Oracle version (e.g. 18.4.0.0.0).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,030 Red Diamond
    edited May 10, 2022 1:42PM Answer ✓

    For all problems like this one, consider MATCH_RECOGNIZE.

    Since I don't have a sample version of your table, I'll use the scott.emp table (which you probably have on your system) to illustrate. Scott.emp contains:

    DEPTNO ENAME   HIREDATE        SAL JOB
    ------ -------- ----------- ------ ---------
        10 CLARK    09-Jun-1981   2450 MANAGER
        10 KING     17-Nov-1981   5000 PRESIDENT
        10 MILLER   23-Jan-1982   1300 CLERK
        20 SMITH    17-Dec-1980    800 CLERK
        20 JONES    02-Apr-1981   2975 MANAGER
        20 FORD     03-Dec-1981   3000 ANALYST
        20 SCOTT    19-Apr-1987   3000 ANALYST
        20 ADAMS    23-May-1987   1100 CLERK
        30 ALLEN    20-Feb-1981   1600 SALESMAN
        30 WARD     22-Feb-1981   1250 SALESMAN
        30 BLAKE    01-May-1981   2850 MANAGER
        30 TURNER   08-Sep-1981   1500 SALESMAN
        30 MARTIN   28-Sep-1981   1250 SALESMAN
        30 JAMES    03-Dec-1981    950 CLERK
    

    14 rows in all. If we want to display only one row per deptno, the row with the latest hiredate, like this:

    DEPTNO ENAME  HIREDATE       SAL JOB
    ------ ------ ----------- ------ ---------
        10 MILLER 23-Jan-1982   1300 CLERK
        20 ADAMS  23-May-1987   1100 CLERK
        30 JAMES  03-Dec-1981    950 CLERK
    

    then we can do it like this:

    SELECT   *
    FROM	 scott.emp
    MATCH_RECOGNIZE
         (
    	   PARTITION BY   deptno
    	   ORDER BY	  hiredate   DESC
    	   MEASURES	  FIRST (ename)	        AS ename
    	   ,		  FIRST (hiredate)	AS hiredate
    	   ,		  FIRST (sal)		AS sal
    	   ,		  FIRST (job)		AS job
    	   ONE ROW PER MATCH
    	   PATTERN        ( x* )
    	   DEFINE  	  x 	 AS 1 = 1
    	 )
    ORDER BY deptno -- or whatever you want
    ;
    


  • User_CREPM
    User_CREPM Member Posts: 7 Green Ribbon

    Thank you very much Mr Kulash! It is exactly what I want!

  • mathguy
    mathguy Member Posts: 10,538 Blue Diamond

    Note though that DESC[ending] ordering in the ORDER BY clause of MATCH_RECOGNIZE is undocumented, and indeed it may lead to wrong output if your outer query has its own ORDER BY clause using the same column. It would be better to order by hiredate (not descending), and to "measure" the LAST values instead of FIRST.