0

I have a table that is a collection entries of users' information:

username, date, sequence, value
--------------------------
Hannah, 1/2/2010, 0, peach
Kim, 1/3/2010, 0, watermelon
Hannah, 8/4/2009, 0, mango
David, 2/2/2010, 0, apple
David, 2/2/2010, 1, orange
David, 1/3/2010, 0, banana

...

I want to create a query that would give me the latest date and then the latest sequence for each user?

For example, the result will be:

Hannah, 1/2/2010, 0, peach
Kim, 1/3/2010, 0, watermelon
David, 2/2/2010, 1, orange

In details of the result, David has two latest data records:

David, 2/2/2010, 0, apple
David, 2/2/2010, 1, orange

and then from these two records, the latest sequence is 1, so the final result is:

David, 2/2/2010, 1, orange

Thank you so much if anyone can help me with this. The table is huge so the query would need to be very efficient, otherwise, it exceeds call limit on CPU usage.

miaj
  • 91
  • 4
  • 13

1 Answers1

1
select x.username,x.date,x.sequence,x.value   from
(
  select t.username, t.date, t.sequence, t.value,
  row_number()over(partition by t.username order by t.date desc,t.sequence desc)xcol
   from your_table t
)x where x.xcol=1
Sergey
  • 4,719
  • 1
  • 6
  • 11
  • x is not assigned in the query. I think you mean: select .. from (...) x where x.xcol=1 – miaj Oct 31 '22 at 20:17
  • Thank you for the query. I tried the query but it currently exceeds call limit on CPU usage in PS instance because this table is huge. Do you know if this query can be further optimized to be more efficient? – miaj Oct 31 '22 at 20:20
  • Could you please provide information about indexes on the table – Sergey Nov 01 '22 at 07:25