| query [message #685539] |
Wed, 02 February 2022 09:15  |
 |
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  |
 |
Michel Cadot
Messages: 68418
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 |
Solomon Yakobson
Messages: 3213
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>
|
|
|
|
| Re: query [message #685560 is a reply to message #685545] |
Tue, 08 February 2022 03:05 |
Frank
Messages: 7897
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 |
Solomon Yakobson
Messages: 3213
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>
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
