Re: Problem with the group statement...

From: Nis Jorgensen <nis_at_dkik.dk>
Date: Fri, 19 Jul 2002 15:58:54 +0200
Message-ID: <fm2gjugbnjprmgu02ljfahn89628iim104_at_4ax.com>


On Fri, 19 Jul 2002 10:28:13 +0200, GoranG <no_at_spam.net> wrote:

>On Fri, 19 Jul 2002 01:54:18 -0400, Steve Kass <skass_at_drew.edu> wrote:
>><cut>
>Thank you for the explanations...
>
>>The FIRST() and LAST() aggregate functions do not exist in the SQL-92
>>standard. They are proprietary extensions.
>><cut>
>
>Just to clear up: Where this extensions does exist there DBMS ensures
>that sorting is applied after grouping and before actual call to
>aggregate functions?

In the one product I know that supports these (Microsoft Access) they are not guaranteed to give you the first or last row in any specific sort order. The documentation is quite sparse. Interestingly in the documentation these functions are NOT listed with the aggregate functions ... and the examples are for how to use them in a Report. What I have learned from using the functions myself is that this behavior seems to be reliable:

  1. All values returned by First (or Last) come from the same row.

(Very handy sometimes!)

b) If count(*) > 2 then First and Last returns values from different rows

(Can also be useful sometimes.)

-- 
Nis Jorgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks
Received on Fri Jul 19 2002 - 15:58:54 CEST

Original text of this message