Jump to content

# 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

MAX(TDATE)

#### 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

Thank you Stano, Frickler, Serge

Serge Thank you so much for your code.

## 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

×