Jump to content
Henry Olive

FB-3 SQL ROWS

Recommended Posts

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

 

 

 

 

 

 

 

 

 

 

 

Share this post


Link to post
35 minutes ago, Stano said:

MAX(TDATE)

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

 

Share this post


Link to post

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

Share this post


Link to post

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


 

 

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×