Firebird SQL syntax help / most recent record

[dave31175]

SELECT “date” AS “Date”, “2” AS “2”, “3” AS “3”, “4” AS “4”, FROM “table” WHERE “date” > ‘1919-01-01’ AND “4” LIKE ‘%100%’ ORDER BY “date” DESC

In this query, there may be several duplicate instances of values in “3”. Can this query be modified to select only the most recent instance of each value?

[Ratslinger]

Hello,

Try this

SELECT "date" AS "Date",
       "2" AS "2",
       "3" AS "3",
       "4" AS "4"
FROM "table" "t"
  INNER JOIN (SELECT "3" AS "33",
                     MAX("date") AS "MaxDate"
              FROM "table"
              GROUP BY "3") "tm"
          ON "t"."date" = "tm"."MaxDate"
         AND "t"."3" = "tm"."33"
WHERE "date" > '1919-01-01'
AND   "4" LIKE '%100%'
ORDER BY "date" DESC

Based on answer here → how do I query sql for a latest record date for each user

[dave31175]

Thanks! That seems to have done the trick on my test DB with my generically renamed fields Now I just need to translate it into my real DB with the actual field names. I appreciate the help!