How can I use a Join from another table when im doing a Max to a column in ORACLE?-CodePudding

Getting This issue in which I'm using a Max to a Column, it returns me the number. (My tables have already the Constraints).

Actual Return

CVEANO	CVENUMERO	CVEACCION
2021	7	4
2021	1	3

What I Want to Return from TblACCION

CVEANO	CVENUMERO	CVEACCION	CVEACCION NAME Brought from tblACCION
2021	7	4	NAME FOR NUMBER 4
2021	1	3	NAME FOR NUMBER 3

My actual Query is

 SELECT
*
FROM
    (   
SELECT
    cveano,
    cvenumero,
    max(cveaccion) as ultima
FROM
         tblbitacoragf 
WHERE
    cveusuario = 1
    
GROUP BY
    cvenumero,
    cveano
    order by max(fechaaccion) desc
    )
WHERE ROWNUM <= 4

I've tried doing

INNER JOIN tblACCION ta USING (CVEACCION)

and in SELECT

ta.descripcion AS accion

but I guess there's something wrong, because it always asks me for

00000 - "missing right parenthesis"

and I did check for parenthesis, but there's no lack of parenthesis.

UPDATE

I've tried this 2.0 , nothing

CodePudding user response：

As I understood, you are looking for something like this.

SELECT 
  * 
FROM 
  (
    SELECT 
      t1.cveano, 
      t1.cvenumero, 
      max(t1.cveaccion) as ultima, 
      max(t2.cveaccionName) as cveaccionName 
    FROM 
      tblbitacoragf t1 
    INNER JOIN tblACCION t2 ON t1.cveaccion = t2.cveaccion 
    WHERE 
      t1.cveusuario = 1 
    GROUP BY 
      t1.cvenumero, 
      t1.cveano 
    order by 
      max(t1.fechaaccion) desc
  ) 
WHERE 
  ROWNUM <= 4

Also you can try this.

SELECT 
  abc.*, 
  xyz.cveaccionName 
FROM 
  (
    SELECT 
      cveano, 
      cvenumero, 
      max(cveaccion) as ultima 
    FROM 
      tblbitacoragf 
    WHERE 
      cveusuario = 1 
    GROUP BY 
      cvenumero, 
      cveano 
    order by 
      max(fechaaccion) desc
  ) abc 
INNER JOIN tblACCION xyz ON abc.ultima = xyz.cveaccion 
WHERE 
  ROWNUM <= 4