Query to get latest addresses holding NFT of a given policy id

Developers Native Tokens
1

Wanted to ask if anyone has a query to get all the latest addresses holding NFTs of a given policy id.
I would like write a query directly using postgress not using graphql.

Regards,
Adam.

2

You would need to walk the txout path for any given NFT until you come to an utxo

3

Do you have an example query how to do it for a single one?

Regards,
Adam

4

Here is an example PostgreSQL query on cardano-db-sync:

select
    encode(policy, 'hex') as policy
  , convert_from(name, 'UTF8') as name
  , sum(quantity) as quantity
  , address
  from utxo_view
  inner join ma_tx_out
    on utxo_view.id = ma_tx_out.tx_out_id
  where encode(policy, 'hex') = '2aa9c1557fcf8e7caa049fa0911a8724a1cdaf8037fe0b431c6ac664'
  group by policy, name, address
order by quantity desc
limit 3;                                                    
                          policy                          |   name    |  quantity   |                                                 address                                                 
----------------------------------------------------------+-----------+-------------+---------------------------------------------------------------------------------------------------------
 2aa9c1557fcf8e7caa049fa0911a8724a1cdaf8037fe0b431c6ac664 | PIGYToken | 30000000000 | addr1qx2778sj320g56cnxtwq4t0d5qdcg6medfa8ec7xkgkl2qpcr6jg8lc7uzl5g2dww8lwn25yxu5klfldj6jhwl4q38sqk3pwp3
 2aa9c1557fcf8e7caa049fa0911a8724a1cdaf8037fe0b431c6ac664 | PIGYToken | 21847998900 | addr1q87gzd5247yh865ql898pzgeyha626e07m3pkg5r560fkevcmpgc80zylxur0y6m7ukw3tg5vjsvyndx6f7pwxkx520qejxg0x
 2aa9c1557fcf8e7caa049fa0911a8724a1cdaf8037fe0b431c6ac664 | PIGYToken | 20000000000 | addr1v9e06msz5r2kn2ae2c5rzjdx02na5tcdar3nxuylags709c5u7vvf
4 Likes
5

Thank you very much for the information.

Regards,
Adam

6

Unfortunately, this query does not work anymore. Maybe someone has an update how to query this information with the new multi_asset table?

7

Here is a query that gets you the last address of each asset of a given policy id. Hope this helps.

select distinct on (convert_from(ma.name, 'UTF8')) convert_from(ma.name, 'UTF8') as name
	, encode(ma.policy, 'hex') as policy_id
	, txo.address
	, txo.id as tx_out_id
	from multi_asset as ma 
	inner join ma_tx_out as mtx on ma.id = mtx.ident 
	inner join tx_out as txo on mtx.tx_out_id = txo.id
	where encode(ma.policy, 'hex') = 'd5e6bf0500378d4f0da4e8dde6becec7621cd8cbf5cbb9b87013d4cc' 
order by convert_from(ma.name, 'UTF8'), txo.id desc
limit 3;
name policy_id address tx_out_id
SpaceBud0 d5e6bf0500378d4f0da4e8dde6becec7621cd8cbf5cbb9b87013d4cc addr1q9aze5z3mxtjz76esx5ck2cwdl8y780f2lajyrn8d00x26l6fxx9vcv8r77875e2f0wqg5wwfmpwmfall3r3znwyhtfs8xhtm3 22545136
SpaceBud1 d5e6bf0500378d4f0da4e8dde6becec7621cd8cbf5cbb9b87013d4cc addr1q9aze5z3mxtjz76esx5ck2cwdl8y780f2lajyrn8d00x26l6fxx9vcv8r77875e2f0wqg5wwfmpwmfall3r3znwyhtfs8xhtm3 22545136
SpaceBud10 d5e6bf0500378d4f0da4e8dde6becec7621cd8cbf5cbb9b87013d4cc addr1qxjewuc2dcjtn8xvhy4awcyxn7e08uc8hlpgk872evt6p93zm5lr97kxmryf523h50kmwfr44jyu5z0zcqhpdddy008q96r0j5 21238155
1 Like