unable to select top 10 records per group in sparksql
Here we can used dense_rank also
select *
from (select *,
dense_rank() over (partition by listner order by timestamp) as rank
from avg_table
)
where rank <= 10;
Difference Between dense_rank() and row_number() is dense_rank() provide the same rank/number to matching column[on partitioned is done] values in multiple row where as row_number() provide the unique row number/rank to matching column values in multiple row
Thanks
Doesn't this work?
select rssi, timestamp, tagid
from avg_table
order by timestamp desc
limit 10;
EDIT:
Oh, I get it. You want row_number()
:
select rssi, timestamp, tagid
from (select a.*,
row_number() over (partition by listner order by timestamp desc) as seqnum
from avg_table
) a
where seqnum <= 10
order by a.timestamp desc;