0

I am stuck with a problem who's solution can easily be solved by using Sub Query but it's taking too long so I'm looking for an alternate option.

Here are two tables

user

  • id
  • fName
  • lName

transaction

  • id
  • user_id FK to user.id
  • amount

Now, a user can have multiple transactions. If I want to get the fName, lName and the latest transaction id, latest transaction amount of the user, is it possible to do it in a single query without using sub query?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
iDeceivex1
  • 1
  • Which MySQL version are you using? – jarlh Oct 23 '22 at 17:37
  • It's MySQL v5.7 – iDeceivex1 Oct 23 '22 at 17:38
  • The question has been asked several times per week since Stack Overflow started. Follow the [tag:greatest-n-per-group] tag for many solutions. For example, here's one of mine with high upvotes: https://stackoverflow.com/a/1313293/20860 – Bill Karwin Oct 23 '22 at 17:38
  • I wasn't sure how to search and nothing I searched matched the criteria. I will check those, thanks for taking your time and linking. – iDeceivex1 Oct 23 '22 at 17:42
  • ideally ids are opaque values and you should not rely on them for "latest"; if you need order, store a timestamp – ysth Oct 23 '22 at 17:46
  • To help you with this sort of question, we need to see your query, table definitions, index definitions, and the output of EXPLAIN. Please [read this](https://stackoverflow.com/tags/query-optimization/info), then [edit] your question. Sometimes subqueries cause performance problems, but more often they are caused by a missing index. – O. Jones Oct 23 '22 at 17:47
  • Ah true, actually I have the "created_at" field in my actual table. I tried to keep only the necessary fields in the question and made the mistake. Thanks for correcting. – iDeceivex1 Oct 23 '22 at 17:47
  • @BillKarwin I think this is not a duplicate because the question specifically asks for a method that has no subqueries. Treating any 'with' clause, any inline view, or any other nested select as a subquery, I think there is no solution. Even windowed function which would be the preferred answer will require a subselect to reduce each group to one row. – Robert Hamilton Oct 24 '22 at 00:54
  • @RobertHamilton, Did you look at the answer I linked to in my comment above? I wrote a join-based solution. – Bill Karwin Oct 24 '22 at 01:05
  • @BillKarwin OK I give you credit for using a self join instead of subquery. I will have to modify my statement to "it is not possible without using subqueries or self joins" – Robert Hamilton Oct 24 '22 at 13:17
  • @RobertHamilton LOL - so you might as well say "it's not possible without some form of SQL query." :-) – Bill Karwin Oct 24 '22 at 13:19

0 Answers0