Hi,

I'm looking at trying to find the best practise method for getting the
corresponding record / field values of a maximum value.

For instance, let's say I have an Invoice table, that holds the invoice
number, the date, and the Customer name.

What I want to achieve is getting a list of customers, the last time
they had an invoice sent, and the invoice number of that particular date.

Using a SQL such as...

select Invoice, Max(Date), Customer
From Invoices
Group by Customer

....doesn't work as it gives an incorrect INVOICE value. (Gives a
different invoice value compared to the maximum date returned for that
customer). I'm aware that SQL isn't supposed to support like this.


So... I'm aware in some databases you could execute the sql similar to...

Select Invoice, Date, Customer
From Invoices
inner join (Select Max(Date), Customer From Invoices group by Customer)
on (Date = Date) and (Customer = Customer)

.... but DBISAM doesn't appear to support this.


I know I can get around it in DBISAM by doing...

Select Max(Date) as MaxDate, Customer
into Memory\Memory1
From Invoices
Group by Customer
;
Select Date, Invoice, Customer
From Invoices
Inner join Memory\M1 on (MaxDate=Date) and (Customer=Customer)


.... however I'm not sure if this is best / correct practise, or whether
there is something more simpler designed just for this specific purpose.

Cheers

Adam.

Adam

>Using a SQL such as...
>
>select Invoice, Max(Date), Customer
> From Invoices
>Group by Customer
>
>...doesn't work as it gives an incorrect INVOICE value. (Gives a
>different invoice value compared to the maximum date returned for that
>customer). I'm aware that SQL isn't supposed to support like this.

With GROUP BY there's no guarantee which row will supply the data. Sometimes people get lucky but don't count on it.

>So... I'm aware in some databases you could execute the sql similar to...
>
>Select Invoice, Date, Customer
> From Invoices
>inner join (Select Max(Date), Customer From Invoices group by Customer)
>on (Date = Date) and (Customer = Customer)
>
>... but DBISAM doesn't appear to support this.

Nope but ElevateDB does

>I know I can get around it in DBISAM by doing...
>
>Select Max(Date) as MaxDate, Customer
>into Memory\Memory1
> From Invoices
>Group by Customer
>;
>Select Date, Invoice, Customer
> From Invoices
>Inner join Memory\M1 on (MaxDate=Date) and (Customer=Customer)
>
>
>... however I'm not sure if this is best / correct practise, or whether
>there is something more simpler designed just for this specific purpose.

The only question I'd ask is "is there a change that a customer could have two invoices issued on the same day" if the answer is yes than you could have two results for that customer. Otherwise I'd say go  for it.

Roy Lambert [Team Elevate]

Adam,

<< ... however I'm not sure if this is best / correct practise, or whether
there is something more simpler designed just for this specific purpose. >>

Yes, that is the best practice for DBISAM.  It's lack of sub-queries can
always be overcome with a script that creates an in-memory table that
represents the sub-query.

Tim Young
Elevate Software
www.elevatesoft.com

> Yes, that is the best practice for DBISAM.  It's lack of sub-queries can
> always be overcome with a script that creates an in-memory table that
> represents the sub-query.

Thanks Tim.

Hi Roy,

>> ... but DBISAM doesn't appear to support this.
>
> Nope but ElevateDB does

Nice... you bet Tim to it <vbg>

>> ... however I'm not sure if this is best / correct practise, or whether
>> there is something more simpler designed just for this specific purpose.
>
> The only question I'd ask is "is there a change that a customer could have two invoices issued on the same day" if the answer is yes than you could have two results for that customer. Otherwise I'd say go  for it.

Good point. In that case I would probably need to do a max on the
invoice number to - resulting in another memory table - nice find!

Cheers

Adam.