SQL isolate greatest values in a column

Question

I have the following output from a view:

xfer_id  client_plt_id xfer_doc_no
2255     80016616        KANORANJE1
2270     80016616        ORANJECFC1
2285     80016616        CFCFPT1
2285     80016623        CFCFPT1
2270     80016623        ORANCFC1
2255     80016623        KANORANJE1
2255     80016630        KANORANJE1
2270     80016630        ORANCFC1
2285     80016630        CFCFPT1

Where there is more than one client_plt_id with the same number I want to isolate the row with the highest xfer_id.

For client_plt_id = 80016616 that would be xfer_id = 2285 for example.

Any ideas on how I can do this?

Edit: I'm using MS-SQL 2008

Answer 1

You are looking for the Group By clause.

I'd give an example, but you did not specify what database you are using.

Answer 2

SELECT MAX(xfer_id), client_plt_id FROM table GROUP BY client_plt_id;

Answer 3

These queries both isolate the row with the highest xfer_id for each distinct client_plt_id

select xfer_id, client_plt_id, xfer_doc_no
from   tab t1
where  xfer_id = (
       select max(xfer_id)
       from   tab t2
       where  t2.client_plt_id = t1.client_plt_id
   )

or, for mysql this may be better performing:

select xfer_id, client_plt_id, xfer_doc_no
from   tab t1
inner join (
       select max(xfer_id), client_plt_id
       from   tab
       group by client_plt_id
       ) t2
on     t1.client_plt_id = t2.client_plt_id
and    t1.xfer_id = t2.xfer_id

For both these queries, you can simply add a WHERE clause to select on particualr client. Just append for example WHERE client_plt_id = 80016616.

If you simply want the one row with the highest xfer_id, regardless of client_plt_id, this is what you need:

select xfer_id, client_plt_id, xfer_doc_no
from   tab t1
where  xfer_id = (select max(xfer_id) from tab)

Answer 4

You can try something like this

DECLARE @TABLE TABLE(
        xfer_id  INT,
        client_plt_id INT, 
        xfer_doc_no VARCHAR(100)

)

INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2255,80016616,'KANORANJE1'
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2270,80016616,'ORANJECFC1' 
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2285,80016616,'CFCFPT1' 
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2285,80016623,'CFCFPT1' 
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2270,80016623,'ORANCFC1' 
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2255,80016623,'KANORANJE1' 
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2255,80016630,'KANORANJE1' 
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2270,80016630,'ORANCFC1' 
INSERT INTO @TABLE (xfer_id ,client_plt_id,xfer_doc_no) SELECT 2285,80016630,'CFCFPT1' 

SELECT  t.*
FROM    @TABLE t INNER JOIN
        (
            SELECT  client_plt_id,
                    MAX(xfer_id) Max_xfer_id
            FROM    @TABLE
            GROUP BY client_plt_id
        ) maxVals ON t.client_plt_id = maxVals.client_plt_id 
                AND t.xfer_id = maxVals.Max_xfer_id