On 12-8-2017 08:28, venussoftop@... [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