On 12-8-2017 08:28, venussof...@gmail.com [firebird-support] wrote:
> 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).  
> I have tried various combination of GROUP BY and FIRST 1 but I do not 
> get the correct result, I just want:
> 
> 
> iItemID, cID, dDt, bRate
> 
> 35    5940      0         1825          2014-05-13    34.740000
> 
> 36    13821     0         1825         2015-05-25    46.140000
> 
> 37    22277     0         1880          2017-07-15    2000.000000
> 
> 
> Please advise

If you are using Firebird 3, you can use ROW_NUMBER:

select
   a.iItemId,
   a.cID,
   a.dDt,
   a.bRate
from (
   select
     ROW_NUMBER() OVER (PARTITION BY iItemID ORDER BY dDt DESC, cID 
DESC) as rownum,
     iItemId,
     cID,
     dDt,
     bRate
   from vwPriceListHistory) a
where rownum = 1

With Firebird 2.5 there are a number of ways that are a little bit more 
complicated.

However, assuming the cID is unique, and always increasing, and is 
therefor already ordered by date, you could do something like:

select
   a.iItemId,
   a.cID,
   a.dDt,
   a.bRate
from vwPriceListHistory a
inner join (
   select max(cID) as maxcID
   from vwPriceListHistory
   group by iItemId) b ON a.cID = b.maxcID

However, when I tried that it didn't work, because the assumption failed 
(cId 5940 has a higher date than cId 9498).

The alternative is to create a single column that enforces the order, 
using string manipulation (warning: this might perform badly for large 
numbers of records):

select
   a.iItemId,
   a.cID,
   a.dDt,
   a.bRate
from PriceListHistory a
inner join (
   select substring(max('' || extract(year from dDt) || 
lpad(extract(month from dDt), 2, '0') || lpad(extract(day from dDt), 2, 
'0') || lpad(cID, 10, '0')) from 9) as maxcID
   from PriceListHistory
   group by iItemId) b ON a.cID = b.maxcID

This

substring(max('' || extract(year from dDt) || lpad(extract(month from 
dDt), 2, '0') || lpad(extract(day from dDt), 2, '0') || lpad(cID, 10, 
'0')) from 9)

constructs a key consisting of the date and the (zero-padded) id that 
allows for lexicographical sorting (so yyyyMMdd0..cID). Note that 
lpad(cID, 10, '0') will need to be lpad(cID, 20, '0') when using bigint 
to prevent invalid sorting with numbers of more than 10 digits.

We then use the max value with the iItemID group, and substring then 
removes the date again to get the right cId back (zero-padded, but that 
isn't a problem in this case, otherwise, you'll need to trim leading zeros).

Mark
-- 
Mark Rotteveel
  • [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