<p>You need to join all the assets for a policy to their transactions and join the transaction addresses to stake addresses. If the most recent transaction has a null stake address that means it's been burned.</p>
<pre><code>select
  a.nft_name,
  sa.view
from
  (
    --selecting (nfts x tx) grouped by most recent tx
    select
      nft_name,
      max(tx_id) as tx_id
    from
      (
        --entire raw set
        select
          convert_from(
            concat('\x', encode(asset.name, 'hex')) :: bytea,
            'UTF8'
          ) as nft_name,
          sa.view as stake_address,
          tx.id as tx_id
        from
          ma_tx_out matx
          inner join multi_asset asset on matx.ident = asset.id
          inner join tx_out tx on tx.id = matx.tx_out_id
          left outer join stake_address as sa on tx.stake_address_id = sa.id
        where
          asset.policy = decode('SOME_POLICY_ID', 'hex') 
      ) a
    group by
      nft_name
  ) a
  inner join tx_out tx on tx.id = a.tx_id
  inner join stake_address as sa on tx.stake_address_id = sa.id
order by
  a.nft_name;
</code></pre>
<p>The left join to stake address inside the inner query will provide your records where the max tx_id is associated with a null stake id, and the outermost inner join to stake address will filter that result out of the return set.</p>

CC BY-SA 4.0
2022-02-07T03:11:36.950Z
2022-02-07T03:11:36.950Z
TonyKC
<p>All tables (other than the <code>epoch</code> table) are designed to be append only (ignoring rollbacks). That means that you need to modify your SQL query to filter out the items that you do not want to see.</p>

CC BY-SA 4.0
2022-01-14T22:53:11.663Z
Do you know how to modify the SQL to filter out a burned NFT?
Kyle M
CC BY-SA 4.0
2022-01-14T23:12:16.373Z
I don't know how to do it, but you would start with a query that only returns the burned NFTs and then use that as the filter.
Erik de Castro Lopo
CC BY-SA 4.0
2022-01-14T22:43:13.447Z
2022-01-14T23:11:01.893Z
2022-01-14T23:11:01.893Z
Erik de Castro Lopo
<p>I am querying the Cardano Postgres DB and can't figure out how to remove NFTs (multi-assets) that have been burned. In the SQL query below, I am selecting the mint transactions for an asset fingerprint that I know to be a duplicate (meaning there was a burn associated with it). The SQL output is below.</p>
<p>After doing some manual research I was able to figure out that the 3rd row in the output is a mint transaction that 'burned' the 1st row's mint transaction (Hence the -1 quantity). The 2nd row is the NFT that is active today.</p>
<p>However, I am unsure how to know the 3rd transaction burned the 1st row and not the 2nd row. I would like to have my SQL script automatically remove the 1st row and only show active (not burned) NFTs.</p>
<p>Any help would be greatly appreciated. Thanks! :)</p>
<p>P.S. Here are the same mint transactions on Cardano scan: <a href="https://cardanoscan.io/tokenminttransactions?assetId=b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b949444503030383436" rel="nofollow noreferrer">https://cardanoscan.io/tokenminttransactions?assetId=b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b949444503030383436</a></p>
<pre><code>with policy_assets as (
    select
        id as ma_id
        , encode(policy, 'hex') as policy_id
        , name
        , fingerprint
    from multi_asset
    where encode(policy, 'hex') = 'b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b9494'
    order by id
)

-- Dupe asset fingerprint
select *
from ma_tx_mint m
inner join policy_assets p
    on m.ident = p.ma_id
where fingerprint in ('asset1x0hrrjs6xtmhnwy8ee60j2jrn4k005awqum4zd')
order by m.id
</code></pre>
<p><a href="https://i.sstatic.net/w0n71.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/w0n71.png" alt="SQL Output" /></a></p>

CC BY-SA 4.0
2022-01-16T03:57:22.663Z
Welcome to Cardano SE! As a new user be sure to take the [Tour].
gRebel
<nft><minting><cardano-db-sync><mint><transaction-metadata>
How to Remove Burned NFTs from SQL Output
# How to Remove Burned NFTs from SQL Output

(Asked by: username_0 on 2022-01-14)

I am querying the Cardano Postgres DB and can't figure out how to remove NFTs (multi-assets) that have been burned. In the SQL query below, I am selecting the mint transactions for an asset fingerprint that I know to be a duplicate (meaning there was a burn associated with it). The SQL output is below.
After doing some manual research I was able to figure out that the 3rd row in the output is a mint transaction that 'burned' the 1st row's mint transaction (Hence the -1 quantity). The 2nd row is the NFT that is active today.
However, I am unsure how to know the 3rd transaction burned the 1st row and not the 2nd row. I would like to have my SQL script automatically remove the 1st row and only show active (not burned) NFTs.
Any help would be greatly appreciated. Thanks! :)
P.S. Here are the same mint transactions on Cardano scan: https://cardanoscan.io/tokenminttransactions?assetId=b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b949444503030383436
with policy_assets as (
    select
        id as ma_id
        , encode(policy, 'hex') as policy_id
        , name
        , fingerprint
    from multi_asset
    where encode(policy, 'hex') = 'b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b9494'
    order by id
)

-- Dupe asset fingerprint
select *
from ma_tx_mint m
inner join policy_assets p
    on m.ident = p.ma_id
where fingerprint in ('asset1x0hrrjs6xtmhnwy8ee60j2jrn4k005awqum4zd')
order by m.id

**Tags:** nft, minting, cardano-db-sync, mint, transaction-metadata
> **Comments:**
> **username_1**: Welcome to Cardano SE! As a new user be sure to take the [Tour].  

---

## 2 answers

### Answer by username_2 on 2022-02-07. Score: 1

You need to join all the assets for a policy to their transactions and join the transaction addresses to stake addresses. If the most recent transaction has a null stake address that means it's been burned.
select
  a.nft_name,
  sa.view
from
  (
    --selecting (nfts x tx) grouped by most recent tx
    select
      nft_name,
      max(tx_id) as tx_id
    from
      (
        --entire raw set
        select
          convert_from(
            concat('\x', encode(asset.name, 'hex')) :: bytea,
            'UTF8'
          ) as nft_name,
          sa.view as stake_address,
          tx.id as tx_id
        from
          ma_tx_out matx
          inner join multi_asset asset on matx.ident = asset.id
          inner join tx_out tx on tx.id = matx.tx_out_id
          left outer join stake_address as sa on tx.stake_address_id = sa.id
        where
          asset.policy = decode('SOME_POLICY_ID', 'hex') 
      ) a
    group by
      nft_name
  ) a
  inner join tx_out tx on tx.id = a.tx_id
  inner join stake_address as sa on tx.stake_address_id = sa.id
order by
  a.nft_name;

The left join to stake address inside the inner query will provide your records where the max tx_id is associated with a null stake id, and the outermost inner join to stake address will filter that result out of the return set.


---

### Answer by username_3 on 2022-01-14. Score: 0

All tables (other than the `epoch` table) are designed to be append only (ignoring rollbacks). That means that you need to modify your SQL query to filter out the items that you do not want to see.

> **Comments:**
> **username_0**: Do you know how to modify the SQL to filter out a burned NFT?  
> **username_3**: I don't know how to do it, but you would start with a query that only returns the burned NFTs and then use that as the filter.  

---

