Home » SQL & PL/SQL » SQL & PL/SQL » query (oracle11g)
query [message #685539] Wed, 02 February 2022 09:15 Go to next message
Norfree
Messages: 1
Registered: February 2022
Junior Member
TABLA A HISTORICOS TABLA B
Machine Piece Endtime Machine
T001 xxxxx 02/02/2022 T001
T003 xxxxx 01/02/2022 T002
T001 xxxxx 31/01/2022 T003
T002 xxxxx 01/02/2022 T004
T001 xxxxx 29/12/2021 T005
T002 xxxxx 30/01/2022 T006
T005 xxxxx 01/02/2022 T007
T006 xxxxx 01/02/2022 T008
T002 xxxxx 28/01/2022 T009
T003 xxxxx 01/02/2022 T010

OBTENER LOS 3 ĂšLTIMOS REGISTRO POR MAQUINA

Machina piece endtime
T001 xxxxx 02/02/2022
T001 xxxxx 31/01/2022
T001 xxxxx 29/12/2021
T002 xxxxx 30/01/2022
T002 xxxxx 01/02/2022
T002 xxxxx 28/01/2022
T003 xxxxx XXXXXX
T003 xxxxx XXXXXX
T003 xxxxx XXXXXX
Re: query [message #685540 is a reply to message #685539] Wed, 02 February 2022 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

For your specific question, have a look at ROW_NUMBER function.

For instance, with the standard EMP table, giving the last 3 employees hired in each department:
SQL> select deptno, empno, ename, job, sal, hiredate
  2  from ( select deptno, empno, ename, job, sal, hiredate,
  3                row_number() over (partition by deptno order by hiredate desc) rn
  4         from emp )
  5  where rn <= 3
  6  order by deptno, hiredate desc
  7  /
    DEPTNO      EMPNO ENAME      JOB              SAL HIREDATE
---------- ---------- ---------- --------- ---------- ----------
        10       7934 MILLER     CLERK           1300 23/01/1982
        10       7839 KING       PRESIDENT       5000 17/11/1981
        10       7782 CLARK      MANAGER         2450 09/06/1981
        20       7876 ADAMS      CLERK           1100 23/05/1987
        20       7788 SCOTT      ANALYST         3000 19/04/1987
        20       7902 FORD       ANALYST         3000 03/12/1981
        30       7900 JAMES      CLERK            950 03/12/1981
        30       7654 MARTIN     SALESMAN        1250 28/09/1981
        30       7844 TURNER     SALESMAN        1500 08/09/1981

9 rows selected.


Note: this is an English forum, please post in English. You can also post your question in our Spanish forum but as there are few people speaking Spanish here you will wait longer.

Re: query [message #685545 is a reply to message #685539] Fri, 04 February 2022 06:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3151
Registered: January 2010
Location: Connecticut, USA
Senior Member
Match recognize solution:

select  deptno, empno, ename, job, sal, hiredate
  from  emp
  match_recognize(
                  partition by deptno
                  order by hiredate desc
                  all rows per match
                  pattern(get1{3})
                  define get1 as(match_number() = 1)
                 )
/

    DEPTNO      EMPNO ENAME      JOB              SAL HIREDATE
---------- ---------- ---------- --------- ---------- --------
        10       7934 MILLER     CLERK           1300 23/01/82
        10       7839 KING       PRESIDENT       5000 17/11/81
        10       7782 CLARK      MANAGER         2450 09/06/81
        20       7876 ADAMS      CLERK           1100 23/05/87
        20       7788 SCOTT      ANALYST         3000 19/04/87
        20       7902 FORD       ANALYST         3000 03/12/81
        30       7900 JAMES      CLERK            950 03/12/81
        30       7654 MARTIN     SALESMAN        1250 28/09/81
        30       7844 TURNER     SALESMAN        1500 08/09/81

9 rows selected.

SQL>
SY.
Re: query [message #685560 is a reply to message #685545] Tue, 08 February 2022 03:05 Go to previous messageGo to next message
Frank
Messages: 7892
Registered: March 2000
Senior Member
Nice one with the match_recognize, but mind you that the pattern causes only departments that have at least 3 emps to be included in the resultset.
Re: query [message #685562 is a reply to message #685560] Tue, 08 February 2022 06:49 Go to previous message
Solomon Yakobson
Messages: 3151
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, "last 3" is open to interpretation. If OP needs up to 3 last:

select  deptno, empno, ename, job, sal, hiredate
  from  emp
  match_recognize(
                  partition by deptno
                  order by hiredate desc
                  all rows per match
                  pattern(get{1,3}) -- up to 3
                  define get as(match_number() = 1)
                 )
/

    DEPTNO      EMPNO ENAME      JOB              SAL HIREDATE
---------- ---------- ---------- --------- ---------- ---------
        10       7934 MILLER     CLERK           1300 23-JAN-82
        10       7839 KING       PRESIDENT       5000 17-NOV-81
        10       7782 CLARK      MANAGER         2450 09-JUN-81
        20       7876 ADAMS      CLERK           1100 23-MAY-87
        20       7788 SCOTT      ANALYST         3000 19-APR-87
        20       7902 FORD       ANALYST         3000 03-DEC-81
        30       7900 JAMES      CLERK            950 03-DEC-81
        30       7654 MARTIN     SALESMAN        1250 28-SEP-81
        30       7844 TURNER     SALESMAN        1500 08-SEP-81

9 rows selected.

SQL>
select  deptno, empno, ename, job, sal, hiredate
  from  emp
  match_recognize(
                  partition by deptno
                  order by hiredate desc
                  all rows per match
                  pattern(get{1,5}) -- up to 5
                  define get as(match_number() = 1)
                 )
/

    DEPTNO      EMPNO ENAME      JOB              SAL HIREDATE
---------- ---------- ---------- --------- ---------- ---------
        10       7934 MILLER     CLERK           1300 23-JAN-82
        10       7839 KING       PRESIDENT       5000 17-NOV-81
        10       7782 CLARK      MANAGER         2450 09-JUN-81
        20       7876 ADAMS      CLERK           1100 23-MAY-87
        20       7788 SCOTT      ANALYST         3000 19-APR-87
        20       7902 FORD       ANALYST         3000 03-DEC-81
        20       7566 JONES      MANAGER         2975 02-APR-81
        20       7369 SMITH      CLERK            800 17-DEC-80
        30       7900 JAMES      CLERK            950 03-DEC-81
        30       7654 MARTIN     SALESMAN        1250 28-SEP-81
        30       7844 TURNER     SALESMAN        1500 08-SEP-81
        30       7698 BLAKE      MANAGER         2850 01-MAY-81
        30       7521 WARD       SALESMAN        1250 22-FEB-81

13 rows selected.

SQL>
SY.
Previous Topic: record count between one hour
Next Topic: Putting a row with a particular value on top of a sort
Goto Forum:
  


Current Time: Sun Jun 26 02:51:19 CDT 2022