[Firebird-devel] [ firebird-Bugs-523472 ] FIRST() and LAST() aggregate functions

Bugs item #523472, was opened at 2002-02-27 08:58
You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=109028&aid=523472&group_id=9028

Category: Core Engine
Group: Feature request
>Status: Deleted
Resolution: Rejected
Priority: 5
Submitted By: Fabiano Bonin (fabianobonin)
Assigned to: Ann W. Harrison (awharrison)
Summary: FIRST() and LAST() aggregate functions

Initial Comment:
select first(field2) from table1 group by field1;
select last(field2) from table1 group by field1;

FIRST should return the first occurrence of the field 
and LAST, the last occurrence

to work like
select max(field2) from table1 group by field1;
select min(field2) from table1 group by field1;

----------------------------------------------------------------------

>Comment By: Sean Leyne (seanleyne)
Date: 2002-02-27 09:40

Message:
Logged In: YES 
user_id=71163

To reflect Ann decision to reject the request at this time.

----------------------------------------------------------------------

Comment By: Sean Leyne (seanleyne)
Date: 2002-02-27 09:39

Message:
Logged In: YES 
user_id=71163

Doesn't the current SELECT FIRST x SKIP y ... FROM ... 
syntax address the issue of the FIRST() function?

Accordingly, wouldn't the LAST() functionality be addressed 
by a SELECT LAST x ... FROM ...?  (although this seems much 
more difficult to enabled)

----------------------------------------------------------------------

Comment By: Fabiano Bonin (fabianobonin)
Date: 2002-02-27 09:38

Message:
Logged In: YES 
user_id=346895

SELECT FIRST and SELECT LAST doesn't works to the purpose i 
was thinking.

I was thinking in the case of a query with a group by 
clause where i need to get the first or the last value of a 
non grouped field.

For example:
I have a table (invoices) with fields (id, date, number and 
customer_id)

if i want to know the number of the last invoice for each 
customer (based on the invoice date), i would do this:

select
  customer_id,
  last(number)
from
  invoices
group by
  customer_id
order by
  date

And i will have the last invoice.number for each customer, 
based on the invoice date

----------------------------------------------------------------------

Comment By: Sean Leyne (seanleyne)
Date: 2002-02-27 09:22

Message:
Logged In: YES 
user_id=71163

Doesn't the current SELECT FIRST x SKIP y ... FROM ... 
syntax address the issue of the FIRST() function?

Accordingly, wouldn't the LAST() functionality be addressed 
by a SELECT LAST x ... FROM ...?  (although this seems much 
more difficult to enabled)

----------------------------------------------------------------------

Comment By: Ann W. Harrison (awharrison)
Date: 2002-02-27 09:12

Message:
Logged In: YES 
user_id=66088

We can discuss this one on the list, but MAX and MIN provide that functionality.
Yes, you can use MAX and MIN on character datatypes.  If you're asking for
a positional function rather than a value-based comparison, I have a great deal
of trouble with that concept because position isn't a relational concept and the
introduction of positional operators will signficantly inhibit efforts to improve
performance by performing operations in parallel.

----------------------------------------------------------------------

You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=109028&aid=523472&group_id=9028