How to get max results from SQL Query?-CodePudding

I have a very simple query and I'm stucked trying to filter only the MAX(b.id) :

SELECT 
A.id,
b.id
from emp A
JOIN ACS B
ON B.id = A.id
and A.id =1553

In table B, we have three lines for the A.ID. I need to get only the higher one. I tried to use "MAX(B.ID)" but it didn't worked.

These are the results:

[
     {
          "rownumber": 1,
          "A.id": 1553,
          "b.id": 749
     },
     {
          "rownumber": 2,
          "A.id": 1553,
          "b.id": 4356
     },
     {
          "rownumber": 3,
          "A.id": 1553,
          "b.id": 4661
     }
]

I just need to return the MAX(b.ID). In this case, I need only:

     {
          "rownumber": 3,
          "emp_id": 1553,
          "id": 4661
     }

I'll remove the A.id from filter selection and for each line in A I need only the MAX from B.

Thanks in advance!

What is the best way to do it?

CodePudding user response：

A simple approach is to use RANK().

Try this:

select *
from
(
SELECT 
  A.id,
  b.id,
  RANK()OVER(ORDER BY b.ID desc) as rnk
from emp A
JOIN ACS B
  ON B.id = A.id
  and A.id =1553
) as r
where rnk=1

CodePudding user response：

As you only want the id from ACS you can use a correlated subquery

select e.id, (select Max(id) from ACS a where a.id = e.id) id
from emp e
where e.id = 1553;