how to get biggest result from a sql result in postgresql

Question

I am using postgresql 8.3 and I have a simple sql query:

SELECT a.id,a.bpm_process_instance_id,a.actor_id 
  FROM bpm_task_instance a
 WHERE a.bpm_process_instance_id IN 
(
   SELECT bpm_process_instance_id 
         FROM incident_info 
        WHERE status = 12
          AND registrant = 23
)

so, I got a result set like this:

id    instance_id  actor_id
150     53            24
147     53            26
148     53            25
161     57            26
160     57            26
158     57            24
165     58            23
166     58            24
167     58            24

now, I want to get the max id by instance_id, and the result is like blew

id    instance_id  actor_id
150     53            24
161     57            26
167     58            23

how could I get the result ? I use the following sql, but get an error.

ERROR: relation "x" does not exist

SELECT * 
  FROM (SELECT a.id,a.bpm_process_instance_id,a.actor_id 
          FROM bpm_task_instance a
         WHERE a.bpm_process_instance_id IN
            (
               SELECT bpm_process_instance_id 
                     FROM incident_info
                        WHERE status = 12
                      AND registrant = 23
            ) 
     ) AS x
 WHERE x.id = (
       SELECT max(id)
             FROM x 
            WHERE bpm_process_instance_id = x.bpm_process_instance_id
          )

anyone who can help me , thanks a lot!

Answer 1

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE the_table
        ( id INTEGER NOT NULL
        , instance_id INTEGER NOT NULL
        , actor_id INTEGER NOT NULL
        );
INSERT INTO the_table(id, instance_id, actor_id) VALUES
(150,53,24) ,(147,53,26) ,(148,53,25)
,(161,57,26) ,(160,57,26) ,(158,57,24)
,(165,58,23) ,(166,58,24) ,(167,58,24)
        ;

SELECT id, instance_id, actor_id
FROM the_table dt
WHERE NOT EXISTS (
        SELECT *
        FROM the_table nx
        WHERE nx.instance_id = dt.instance_id
        AND nx.id > dt.id
        );

Result (note: the last row differs!):

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 9
 id  | instance_id | actor_id 
-----+-------------+----------
 150 |          53 |       24
 161 |          57 |       26
 167 |          58 |       24
(3 rows)

UPDATE: this is the query including the other subquery and the missing table, and the original (ugly) column names, all packed into a CTE:

WITH zcte AS (
        SELECT ti.id AS id
                , ti.bpm_process_instance_id AS instance_id
                , ti.actor_id AS actor_id
        FROM bpm_task_instance ti
        WHERE EXISTS ( SELECT * FROM incident_info ii
                WHERE ii.bpm_process_instance_id = ti.bpm_process_instance_id
                AND ii.status = 12
                AND ii.registrant = 23
                )
        )
SELECT id, instance_id, actor_id
FROM zcte dt
WHERE NOT EXISTS (
        SELECT *
        FROM zcte nx
        WHERE nx.instance_id = dt.instance_id
        AND nx.id > dt.id
        );

UPDATE addendum:

Oops, the bad news is that 8.3 did not have CTE's yet. (think about upgrading). The good news is: as a workaround you could make zcte () as a (temporary) VIEW, and refer to that instead.

Answer 2

try this:

select a.id,a.bpm_process_instance_id,a.actor_id 
from bpm_task_instance A 
inner join
    (select max(a.id) as id,a.bpm_process_instance_id
    from bpm_task_instance a 
    where a.bpm_process_instance_id in 
        (  select bpm_process_instance_id 
           from incident_info 
           where status = 12 and registrant = 23
        )
group by a.bpm_process_instance_id)B
on A.bpm_process_instance_id=B.bpm_process_instance_id
and A.id=B.id

Answer 3

@wildplasser

    SELECT dt.* FROM 
    (
       SELECT id,bpm_process_instance_id,actor_id 
       FROM bpm_task_instance WHERE bpm_process_instance_id in 
       (
           SELECT bpm_process_instance_id FROM incident_info 
           WHERE status = 12 and registrant = 23
       ) 
    ) as dt
    WHERE NOT EXISTS (
        SELECT *
        FROM bpm_task_instance nx
        WHERE nx.bpm_process_instance_id = dt.bpm_process_instance_id
        AND nx.id > dt.id
    )
   ORDER BY id asc

Answer 4

At large scale, the DISTINCT ON syntax is sometimes faster than the perfectly valid answers already given.

SELECT DISTINCT ON (instance_id)
  id, instance_id, actor_id
  FROM the_table dt
  ORDER BY instance_id, id DESC;

Once you get used to this syntax, you may find it easier to read than the alternatives. Inside the parentheses in the DISTINCT ON clause you put the list of columns which should be unique, and the ORDER BY clause must start with matching columns and continue with enough columns to ensure that the one you want to keep comes first.