Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

new aggregate function: AtMax( exp1, exp2 )

3 posts in Product Futures Discussion Last posting was on 2002-08-17 17:40:07.0Z
Andrew Schonberger Posted on 2002-08-11 04:57:37.0Z
Message-ID: <3D55EEC0.B1F94C7C@idx.com.au>
Date: Sun, 11 Aug 2002 14:57:37 +1000
From: Andrew Schonberger <aschon@idx.com.au>
X-Mailer: Mozilla 4.77 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: new aggregate function: AtMax( exp1, exp2 )
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 45
NNTP-Posting-Host: tntwc01-3-178.idx.com.au 203.166.3.178
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:333
Article PK: 93504

Hi,

I often encounter the need to show the value of a column, corresponding
to the maximum of another column. A trivial example is somethig like:
give me the ID of the salesman who had the highest volume of sales.

Another typical situation is a single entity which, at the level of
Local Modeling has both a permanent, and a historical component. For
example, the interest rate of a particular type of loan. The loan
itself has immutable properties, but the rate varries from time to time.
At Physical Modeling level, this is implemented by a master-detail pair,
where the most recent detail holds the "current" value. The "most
recent" can be found by looking for the maximum of a Date field, or the
maximum of a numeric ID, among the Details of a given Master.

In situations like this, I usually must do two queries:

- step 1: find first the maximum values for column A (group by into a
#temp)

- step 2: retrieve the value of column B, corresponding to the maximum
of A ( join #temp back to same tables)

In effect, I need to access the same records twice. Unless I write a
cursor loop (yukk) and I scan the result set myself. With a cursor
loop, I can retain, at each iteration, the largest value of A seen so
far. If the current iteration has an A higher that the maximum seen so
far, then I also retain the value of B.

A new aggregate function could easily solve this situation

AtMax( exp1, exp2 )

Returns the value of exp2 corresponding to the maximum of exp1. If max(
exp1 ) is reached by more than one record in the group, then AtMax()
should return exp2 of the first such exp1 encountered.

I've talked to colleagues in other projects, and they would also like to
see such a function.

Andrew Schonberger


Mariano Corral Posted on 2002-08-17 17:40:07.0Z
From: "Mariano Corral" <corral@iname.com>
Date: Sat, 17 Aug 2002 13:40:07 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: new aggregate function: AtMax( exp1, exp2 )
Message-ID: <7B810EB5474F8FF400610E6D85256C18.0053BFBD85256C14@webforums>
References: <3D55EEC0.B1F94C7C@idx.com.au>
Lines: 35
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:315
Article PK: 93486


"Andrew Schonberger" <aschon@idx.com.au> wrote
[snip]
> In situations like this, I usually must do two queries:
>
> - step 1: find first the maximum values for column A (group by into a
> #temp)
>
> - step 2: retrieve the value of column B, corresponding to the maximum
> of A ( join #temp back to same tables)
>

In step 1, let's assume there is another column named
grouping_col. Step 1 selects max(A) for every grouping_col.
That is,
select grouping_col, max (A) from mytable
group by grouping_col

To select other columns' values for the row having
max(A) there is an ASE extension to ISO-SQL
which allows to select rows, not groups, by a select with
the group by clause. This happens when there are more
columns in the select-list than in the group by-list:

select grouping_col, B, max (A) from mytable
group by grouping_col
having A = max (A)

Confusing, isn't it? But it works. AFAIK, the manuals
reserve several pages to document this strange use
of GROUP BY. Think of it as a worktable where the values
of grouping_col and B come from the selected *rows*
whilst max(A) comes from *groups* (grouped by grouping_col)

Regards,
Mariano Corral


Carl Kayser Posted on 2002-08-11 17:13:36.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <3D55EEC0.B1F94C7C@idx.com.au>
Subject: Re: new aggregate function: AtMax( exp1, exp2 )
Date: Sun, 11 Aug 2002 13:13:36 -0400
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <y94pzwVQCHA.298@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:332
Article PK: 93499


"Andrew Schonberger" <aschon@idx.com.au> wrote in message
news:3D55EEC0.B1F94C7C@idx.com.au...
> Hi,
>
> I often encounter the need to show the value of a column, corresponding
> to the maximum of another column. A trivial example is somethig like:
> give me the ID of the salesman who had the highest volume of sales.
>
> Another typical situation is a single entity which, at the level of
> Local Modeling has both a permanent, and a historical component. For
> example, the interest rate of a particular type of loan. The loan
> itself has immutable properties, but the rate varries from time to time.
> At Physical Modeling level, this is implemented by a master-detail pair,
> where the most recent detail holds the "current" value. The "most
> recent" can be found by looking for the maximum of a Date field, or the
> maximum of a numeric ID, among the Details of a given Master.
>
> In situations like this, I usually must do two queries:
>
> - step 1: find first the maximum values for column A (group by into a
> #temp)
>
> - step 2: retrieve the value of column B, corresponding to the maximum
> of A ( join #temp back to same tables)
>

Just so I understand the example. Is "A" the master or detail table?
(Let's assume that the master table has info about salespeople and the
detail table has information about individual sales.)

If "A" is the master then your grouping would be by a non-temporal column
and there would be multiple rows that qualify?

If "B" is the master then wouldn't something like

select B.name, 'Total Sales' = sum (A.sales)
from salespeople B, sales A
where B.id = A.id
group by A.sales
having sum (A.sales) = (select max (sum (C.sales))
from sales C
group by C.sales)

do the trick? The problems with my suggestion, assuming that it works, are
that sales is searched twice (ugh) and it may not be ANSI standard. (As I
remember one can't have an aggregate of an aggregate. Joe Chelko will
probably chime in on this.)