FB-3 SQL ROWS

[Henry Olive 5]

Good Day,

My Table like below

CUSTNO...TDATE...................AA........BB 

1..................20/01/2022.......1,50.....1,65

1..................26/02/2022.......1,55.....1,60

2..................15/02/2022.......1,50.....1,55

 

I'd like to get Max TDATE datas of a desired CUSTNO

That is, if my parameter for CustNo = 1 then the result should be like below

CUSTNO...TDATE...................AA........BB 

1..................26/02/2022.......1,55.....1,60

if my parameter for CustNo = 2 then the result should be like below

CUSTNO...TDATE...................AA........BB 

2..................15/02/2022.......1,50.....1,55

 

I wrote below SQL and i get correct result

but i'd like to be sure it is the only one solution or there is a better way

SELECT TDATE, AA, BB FROM MYTABLE
WHERE CUSTNO=:CUSTNO
ORDER BY TDATE DESC ROWS 1

Thank You

 

 

 

 

 

 

 

 

 

 

 

[Stano 143]

MAX(TDATE)

[Frickler 11]

35 minutes ago, Stano said:

MAX(TDATE)

...would require GROUP BY, and then problems arrive with "AA" and "BB" not being grouped.

 

[Stano 143]

Of course. You have to put them in GROUP BY. No TDATE.

[Serge_G 87]

Well, your SQL is the first and easy way to get the line with Firebird < 3.0 

With Firebird 3+ perhaps a windows analytical function can be used 

 

WITH C AS (SELECT CUSTNO,LAST(TDATE) OVER (ORDER BY TDATE) D
FROM MYTABLE WHERE CUSTNO=:CUSTNO )

SELECT M.TDATE,M.AA,M.BB FROM MYTABLE M
                                  LEFT JOIN C ON C.CUSTNO=M.CUSTNO AND M.TDATE=C.D

 

But IMO, advantage is null, and if you have more than one line with CUSTNO,TDATE identical you will get more than one line

 

 

 

[Henry Olive 5]

Thank you Stano, Frickler, Serge

Serge Thank you so much for your code.