LIMIT a query Postgresql

Question

im doing this query and it gives me multiple hits because of each assetid may contain many vb.title, how can i limit the result to only show 1 hit for each assetid?

'select DISTINCT v.id,
           v.purchasedate,
           v.customerid,
           v.assetid,
           va.description,
           vb.title
  from purchases v,
           asset va,
           assetdescription vb
 where customerid = '$kid'
   and v.assetid = va.id
   and vb.assetid = va.id
 order by v.purchasedate'

Answer 1

If you don't care which you get, you can use something like this:

select max(v.id),
       max(v.purchasedate),
       max(v.customerid),
       v.assetid,
       max(va.description),
       max(vb.title)
  from purchases v,
           asset va,
           assetdescription vb
 where customerid = '$kid'
   and v.assetid = va.id
   and vb.assetid = va.id
 group by v.assetid
 order by v.purchasedate;

If you want e.g. the "latest", then you can use something like this:

select t.id,
       t.purchasedate,
       t.customerid,
       t.assetid,
       t.description,
       t.title
from (
    select v.id,
           v.purchasedate,
           v.customerid,
           v.assetid,
           va.description,
           vb.title,
           row_number() over (partition by v.assetid order by v.purchasedate desc) as rn
      from purchases v,
               asset va,
               assetdescription vb
     where customerid = '$kid'
       and v.assetid = va.id
       and vb.assetid = va.id
) t
where rn = 1
order by v.purchasedate

The decision which one to take is controlled by the ordering inside the window definition (partion by v.assetid order by ....)

Answer 2

If you want to pick the row with the earliest purchasedate as your example seems to indicate, this query may be preferable:

SELECT DISTINCT ON (v.assetid)
       v.id
      ,v.purchasedate
      ,v.customerid
      ,v.assetid
      ,va.description
      ,vb.title
FROM   purchases v
JOIN   asset va ON va.id = v.assetid
JOIN   assetdescription vb ON vb.assetid = va.id
WHERE  customerid = '$kid'
ORDER  BY assetid, v.purchasedate;

With DISTINCT the primary sort order has to be with the distinct column(s). You can wrap it all into a subquery to ORDER BY purchasedate.