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

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.


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

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


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

    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

Thank you very much for the information.


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

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