I am trying to get all the internal transactions from my local TzKT DB in the same order that they are displayed in tzkt.io, which I assume is the same as in the JSON RPC API. The default order that I get from the DB is not the same, and I cannot find the right column to sort by (I tried nonce and some others but it did not work). Here is the kind of query that I am using, for one of the cases where I get a different order (note that I am only interested on those transactions that happen between smart contracts, i.e. KT... addresses):

  SAddr."Address" AS source,
  TAddr."Address" AS destination
FROM "TransactionOps"
  INNER JOIN "Accounts" SAddr ON "TransactionOps"."SenderId" = SAddr."Id"
  INNER JOIN "Accounts" TAddr ON "TransactionOps"."TargetId" = TAddr."Id"
  SAddr."Address" < 't'
  AND TAddr."Address" < 't'
  AND "OpHash" = 'onea2jEQy5eHAnwP56n7yVskRtYYKQkMcubSDFmciF2vH3JCLdp';

which gives me the following output:

                source                |             destination              
 KT1Pyd1r9F4nMaHy8pPZxPSq6VCn9hVbVrf4 | KT1H25LW5k4HQGm9hmNXzaxf3nqjsAEhQPah
 KT1Pyd1r9F4nMaHy8pPZxPSq6VCn9hVbVrf4 | KT1BB3oNr5vUSw1CuPNb2zpYEVp376XrXWaJ
 KT1PrRTVNgxkRgyqqNQvwTiVhd55dqyxXJ6n | KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1Pyd1r9F4nMaHy8pPZxPSq6VCn9hVbVrf4
 KT1Pyd1r9F4nMaHy8pPZxPSq6VCn9hVbVrf4 | KT1NGTDBKDPMrAYEufb72CLwuQJ7jU7jL6jD
 KT1Pyd1r9F4nMaHy8pPZxPSq6VCn9hVbVrf4 | KT1NBgqqJacbdoeNAg9MvrgPT9h6q6AGWvFA
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh
 KT1Pyd1r9F4nMaHy8pPZxPSq6VCn9hVbVrf4 | KT1LpGZnT6dj6STSxHXmvSPqx39ZdPXAMpFz
 KT1Pyd1r9F4nMaHy8pPZxPSq6VCn9hVbVrf4 | KT1PnUZCp3u2KzWr93pn4DD7HAJnm3rWVrgn
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1PnUZCp3u2KzWr93pn4DD7HAJnm3rWVrgn
 KT1PnUZCp3u2KzWr93pn4DD7HAJnm3rWVrgn | KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1PwoZxyv4XkPEGnTqWYvjA1UYiPTgAGyqL
 KT1PwoZxyv4XkPEGnTqWYvjA1UYiPTgAGyqL | KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK
 KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK | KT1PwoZxyv4XkPEGnTqWYvjA1UYiPTgAGyqL
 KT1PwoZxyv4XkPEGnTqWYvjA1UYiPTgAGyqL | KT1PwoZxyv4XkPEGnTqWYvjA1UYiPTgAGyqL
 KT1PwoZxyv4XkPEGnTqWYvjA1UYiPTgAGyqL | KT1PnUZCp3u2KzWr93pn4DD7HAJnm3rWVrgn
 KT1PwoZxyv4XkPEGnTqWYvjA1UYiPTgAGyqL | KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK
 KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK | KT19ovJhcsUn4YU8Q5L3BGovKSixfbWcecEA
 KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK | KT1PnUZCp3u2KzWr93pn4DD7HAJnm3rWVrgn
 KT1PnUZCp3u2KzWr93pn4DD7HAJnm3rWVrgn | KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK
 KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK | KT19ovJhcsUn4YU8Q5L3BGovKSixfbWcecEA
 KT19ovJhcsUn4YU8Q5L3BGovKSixfbWcecEA | KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK
 KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK | KT1TGzfW1ME3QzfiHWgTUnTyJubv5mk2ZuHK
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT19ovJhcsUn4YU8Q5L3BGovKSixfbWcecEA
 KT19ovJhcsUn4YU8Q5L3BGovKSixfbWcecEA | KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1PrRTVNgxkRgyqqNQvwTiVhd55dqyxXJ6n
 KT1PrRTVNgxkRgyqqNQvwTiVhd55dqyxXJ6n | KT19ovJhcsUn4YU8Q5L3BGovKSixfbWcecEA
 KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh | KT1DMQpgHs8v5mtzS3mPHhpPuDs8aMyy82Uh

while in tzkt.io that is not the order they appear in: https://tzkt.io/onea2jEQy5eHAnwP56n7yVskRtYYKQkMcubSDFmciF2vH3JCLdp

Any idea which column(s) I can use to sort them properly?

1 Answer 1


To get the same order as in RPC you need to order operations by the "Id" column.

Also, the condition SAddr."Address" < 't' AND TAddr."Address" < 't' won't give you all interanal transactions, because it will filter out KT1->tz transactions, which are also internal. To get all internal transactions, you need to simply check for either the "Nonce" or "InitiatorId" column to be not null.

So, the final query will be like this:

  sender."Address" AS source,
  target."Address" AS destination
FROM "TransactionOps" AS tx
  INNER JOIN "Accounts" AS sender ON tx."SenderId" = sender."Id"
  LEFT JOIN "Accounts" AS target ON tx."TargetId" = target."Id"
  tx."OpHash" = 'onea2jEQy5eHAnwP56n7yVskRtYYKQkMcubSDFmciF2vH3JCLdp' AND 
  tx."InitiatorId" IS NOT NULL
  • Why the left join for target?
    – Fernando
    Commented Apr 22 at 9:50
  • 1
    @Fernando because "TargetId" can be null
    – Groxan
    Commented Apr 24 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.