> I have a view and the following query which returns multiple records 
from the view (see below) for each iItemID
 >
 > SELECT iItemID
 >         , cID
 >         , dDt
 >         , bRate
 >    FROM vwPriceListHistory
 >    ORDER by iItemID, dDt DESC, cID DESC
 >
 > What I want is one first record of each iItemID (ie. the latest price)

Just rephrase your question slightly: "For each iItemID I want the most 
recent record, i.e. the one for which there doesn't exist any newer 
version."

Then, the query you're looking for is simply:

SELECT PLH1.iItemID, PLH1.cID, PLH1.dDt, PLH1.bRate
FROM vwPriceListHistory PLH1
WHERE NOT EXISTS( SELECT *
                   FROM vwPriceListHistory PLH2
                   WHERE PLH1.iItemID = PLH2.iItemID
                     AND ( PLH1.dDt < PLH2.dDt
                        OR ( PLH1.dDt = PLH2.dDt
                         AND PLH1.cID < PLH2.cID ) ) )
ORDER by PLH1.iItemID

Set
  • [firebird-support] ... venussof...@gmail.com [firebird-support]
    • Re: [firebird-... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
      • Re: [fireb... venussof...@gmail.com [firebird-support]
        • Re: [f... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
          • [f... venussof...@gmail.com [firebird-support]
            • ... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
              • ... venussof...@gmail.com [firebird-support]
    • Re: [firebird-... setysvar setys...@gmail.com [firebird-support]

Reply via email to