Hi all

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
 

 Kind regards
 Bhavbhuti
 

 iItemID, cID, dDt, bRate

35    5940      0         1825          2014-05-13    34.740000
35    9498      0         388           2014-04-10    43.231400
35                        0             1900-01-01    0.000000
36    13821     0         1825          2015-05-25    46.140000
36    13816     0         1825          2015-05-23    34.740000
36    11442     0         1825          2015-05-14    46.140000
36    11441     0         1825          2015-05-14    0.000000
36    5938      0         1825          2014-05-13    46.140000
36                        0             1900-01-01    0.000000
37    22277     0         1880          2017-07-15    2000.000000
37    22272     0         1880          2017-07-15    200.000000
37    22263     0         3263          2017-07-11    434.000000
37    18970     0         367           2016-09-17    176.090000
37    15723     0         367           2016-05-24    176.000000
37    15289     0         365           2016-04-17    176.090000
37    15232     0         2754          2016-04-10    176.090000
37    15103     0         365           2016-02-04    251.500000
37    13761     0         479           2015-05-21    20.290000
37    10133     0         365           2014-10-02    176.090000
37    2879      0         481           2013-08-09    216.580000
37    2645      0         365           2013-04-09    163.050000
37                        0             1900-01-01    0.000000
  • [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