On 12-8-2017 13:33, venussoftop@... [firebird-support] wrote:

>
>
> Hi Mark
>
> I have updated the original post with the new cID column values
>
> I have a view and the following query which returns multiple records
> from the view (see below) for each iItemID
>
>
> SELECT iItemID
> , cID
> , bRate
> FROM vwPriceListHistory
> ORDER by iItemID, 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, bRate
>
> 35 201405135940 0 1825 34.740000
>
> 36 2015052513821 0 1825 46.140000
>
> 37 2017071522277 0 1880 2000.000000
>
>
> Please advise

That is already addressed by my initial answer, just do:

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

Also note that how you have now constructed cID is not what my answer
advised, your current cID will not sort correctly if ids with different
lengths occur on the same date, eg 2017081212345 will be considered
smaller than 20170812234, while 12345 is definitely larger than 234.

If you follow my advise, those cIDs would be
201708120000012345 and
201708120000000234

If for some reason you are still stuck on ancient versions like Firebird
1.5, then I don't think there is an easy way to do this, if at all (I
don't recall if Firebird 1.5 supported sub-queries in IN or EXISTS, if
it does, you might be able to use that).

Mark
--
Mark Rotteveel