Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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 ....)

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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