Need help with a SQL query that returns the lasts records matching a certain criteria.

Question

I have the following table:

CREATE TABLE "posting" (
    "id" integer NOT NULL PRIMARY KEY,
    "amount" real NOT NULL,
    "balance" real NOT NULL,
    "account_id" integer NOT NULL REFERENCES "account" ("id"),
    "asset_type_id" integer NOT NULL REFERENCES "asset_type" ("id")        
)

For this table, I manually generate ids in a way that there's no gaps(records can't be deleted). It is guaranteed that the following statement will return the latest record :

SELECT * FROM posting ORDER BY id DESC LIMIT 1

The problem is that now I need to retrieve not only the last, but the last record for each combination of 'account_id' and 'asset_type_id'. For example, lets say I have two 'accounts' and two 'asset_types' (both with ids 1 and 2) and the following records(omiting amount and balance):

id | account_id | asset_type_id 
1  |    1       |    1
2  |    2       |    1
3  |    1       |    2
4  |    2       |    1
5  |    2       |    2
6  |    2       |    2

It will return me records 6, 4, 3 and 1 since records 5 and 2 were "replaced" by 6 and 4 respectively. I have no idea how to represent this in SQL, any help is appreciated.

Answer 1

This works in mysql

select * from (select * from posting order by id desc) as a group by account_id, asset_type_id;

Answer 2

Use a subquery that groups by account id and asset type and selects the maximum id for each group. Then just make sure the id is in that subquery. I haven't tested it, but I think this should work:

select *
from posting
where id in
    (
    select max(id)
    from   posting
    group
    by     account_id,
           asset_type_id
    )

Answer 3

What You need to peek is the largest value of ID for group of account_id and asset_type_id

SELECT MAX(ID),account_id,asset_type_id FROM posting group by account_id, asset_type_id;

Table

id | account_id | asset_type_id 
1  |    1       |    1
2  |    2       |    1
3  |    1       |    2
4  |    2       |    1
5  |    2       |    2
6  |    2       |    2

After group

id | account_id | asset_type_id 
1   |    1       |    1
2,4 |    2       |    1
3   |    1       |    2
5,6 |    2       |    2

After Max

id | account_id | asset_type_id 
1  |    1       |    1
3  |    1       |    2
4  |    2       |    1
6  |    2       |    2

EDIT:

Q2:How To run this query without using whole table ?

The SQL select statement has some phases that are done in specific order each of this phase create a temporary input table for next phase.

(5) SELECT (5-2) DISTINCT (5-3) TOP (<top specification>) 
(5-1) <select clauses>
(1) FROM (1-J) <left table> <connection type> JOIN <right table> ON <predicates of on clause>
<alias>
(2) WHERE <predicates of where clause>
(3) GROUP BY <grouping specification>
(4) HAVING <predicates of having clause>
(6) ORDER BY <list specifying the order>

As You see the first step is the FROM, that in your case is one table, then we go to WHERE so when You put some predicates here for example account_id between 1 and 10 AND asset_type_id between 1 and 10 you will only operate on this piece of table in step 3 that is group by.