ASE Database forum - MAX() function

Author

MAX() function

AGA

2005-12-21, 7:24 am

Hi all,

for performance reasons we have a denormalized table with at
least 16 different amounts (datatype float).

Is there a possibility to receive the max of these amounts
just in one sql statement. I want to avoid a cursor or a
while loop.

We use sybase version
Adaptive Server Enterprise/12.5.3/EBF 12339 ESD#1/P/Linux
Intel/Enterprise Linux/ase1253/1900/32-bit/OPT/Mon Jan 24
21:41:32 2005

Thx
Andreas

Stefan Karlsson

2005-12-21, 1:24 pm

<AGA> wrote in message news:43a92dad.1665.1681692777@sybase.com...
> Hi all,
>
> for performance reasons we have a denormalized table with at
> least 16 different amounts (datatype float).
>
> Is there a possibility to receive the max of these amounts
> just in one sql statement. I want to avoid a cursor or a
> while loop.
>
> We use sybase version
> Adaptive Server Enterprise/12.5.3/EBF 12339 ESD#1/P/Linux
> Intel/Enterprise Linux/ase1253/1900/32-bit/OPT/Mon Jan 24
> 21:41:32 2005

Not sure I understand your question. Is this what you're looking for ?

SELECT <some columns>, MAX( amount_float )
FROM ...
WHERE ...
GROUP BY <some columns>

HTH,

/Stefan

AGA

2005-12-21, 8:25 pm

> <AGA> wrote in message
>
> Not sure I understand your question. Is this what you're
> looking for ?
>
> SELECT <some columns>, MAX( amount_float )
> FROM ...
> WHERE ...
> GROUP BY <some columns>
>
> HTH,
>
> /Stefan
>
>

Hi Stefan,

thx for your answer, but that is not exactly what I need.
Imagine a table like this:

create table Test
(
Amount01 float(8) NULL,
Amount02 float(8) NULL,

M. Searer

2005-12-22, 11:24 am

create a view to present a normalized view of the table, then do a select max on
that:
create view normalized
as
select yourprimarykey, amount01 as amount
from Test
UNION ALL
select yourprimarykey, amount02
from Test
....

select max(amount)
from normalized
where yourwhereclause
=================
or, normalize the table.

<AGA> wrote in message news:43a9da52.1eaf.1681692777@sybase.com...
>
> Hi Stefan,
>
> thx for your answer, but that is not exactly what I need.
> Imagine a table like this:
>
> create table Test
> (
> Amount01 float(8) NULL,
> Amount02 float(8) NULL,
> .
> .
> Amount16 float(8) NULL
> )
>
> insert Test select 1, 2, ..... 16
> insert Test select 16, 15, ..... 1
>
> What I need now is a select to retrieve the MAX value across
> these amount columns in every data row, means in the first
> row retrieve Amount16 with value of 16, in the second row
> Amount01 with value 16.
>
> Thx and best regards
> Andreas

AGA

2005-12-23, 7:24 am

> create a view to present a normalized view of the table,
> then do a select max on that:
> create view normalized
> as
> select yourprimarykey, amount01 as amount
> from Test
> UNION ALL
> select yourprimarykey, amount02
> from Test
> ...
>
> select max(amount)
> from normalized
> where yourwhereclause
> =================
> or, normalize the table.
>
>
>
>
>
>
> <AGA> wrote in message
> news:43a9da52.1eaf.1681692777@sybase.com... >> <AGA> wrote
> in message >> >
> news:43a92dad.1665.1681692777@sybase.com... Hi all, >> >
> 2005 >>
> you're >> looking for ?
>
>

Thx for your reply. The solution to create a view seems to
be a passable compromise between performance and
normalization and I will try it.

Wish you all Merry Christmas

Sponsored Links