How to select latest entry of each group along with value-CodePudding

I have a table looking like this

Id   Type   TimeStamp   Amount   Partition  year   Hash
-------------------------------------------------------
1    194I   108          28       Monthly   2022   abc
1    194I   105          56       Monthly   2022   abc
1    194J   106          36       Monthly   2022   abc
2    194K   110          29       Monthly   2022   xyz
1    194J   105          32       Monthly   2022   abc
2    194C   100          20       Monthly   2022   xyz
1    194J   106          36       Monthly   2022   abc
1    194K   110          29       Monthly   2022   abc
1    194J   105          32       Monthly   2022   abc
2    194C   100          20       Monthly   2022   xyz

I want to select the following info using Athena query

Type  Amount
-------------
194I    28 
194J    36 
194K    29 
194C    20

Currently using this query, but it is selecting all the records instead of latest records

select type, amount
from table
where id = 1
  and hash = abc
  and financialyear = 2022
  and partition = Monthly
order by timestamp desc

CodePudding user response：

Some references： 1、SELECT amount,type from (SELECT * from yourtable where hash = ... and = ... ORDER BY TimeStamp desc limit 18446744073709551615) t GROUP BY t.type;

2^64-1 = 18446744073709551615 A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order). You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT. this cause the optimizer to create a temporary table, and use filesort to order the query the limit number is a 64bit unsigned -1 (2^64-1), this is a big number and can work with 99.999% of queries i know

2、SELECT type,amount from yourtable t1 GROUP BY type, TimeStamp HAVING TimeStamp = (SELECT max(TimeStamp) from yourtable WHERE type = t1.type AND hash = ... AND id = ...);

3、select u.type,u.amount from yourtable u inner join ( select type, max(TimeStamp) as max_a2 from yourtable group by type ) v on u.type = v.type and u.TimeStamp = v.max_a2 where id =... AND hash = ...

CodePudding user response：

This worked

select "type", "amount"
from table
where hash = '%1$s'
  and year = '%2$s'
  and partition = '%3$s'
  and "timestamp" in (
    select max("timestamp") as timestamp
    from table
    where hash = '%1$s' and year = '%2$s' and partition = '%3$s'
    group by type
    )