first?

[C L Humphreys]

Hi,

Can I choose how, when using group by, the first() function works? I want it
to pick the first record from a sort on one of the fields, but must I first
create a query to sort the data, then run another query on top of that?

Cheers,
Chris

[Rick Brandt]

Re: first?

"C L Humphreys" <clhumphreys@to ofgib.moc> wrote in message
news:bnljdn$1e8 $1@ucsnew1.ncl. ac.uk...[color=blue]
> Hi,
>
> Can I choose how, when using group by, the first() function works? I want[/color]
it[color=blue]
> to pick the first record from a sort on one of the fields, but must I[/color]
first[color=blue]
> create a query to sort the data, then run another query on top of that?[/color]

First() and Last() are fairly useless although I often use First() as a
"don't care" option. If the field can be sorted you can generally use
Min() or Max() instead and get the results you want.



--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[MGFoster]

Re: first?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


To pick the 1st record in a sorted query you can use the TOP
predicate. E.g.:

SELECT TOP 1 *
FROM Products
WHERE BuyDate >= #1/1/2003#
ORDER BY BuyDate Desc

This will select the product record that has the latest (most recent)
BuyDate on, or after, Jan 1, 2003.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP57R2YechKq OuFEgEQLJuwCg75 FXKLnFw0pBVtrQq NpBLxmX1cMAoLMC
a16RqZ/BtN7Fji3+BlUknJ Lu
=WqDY
-----END PGP SIGNATURE-----

C L Humphreys wrote:
[color=blue]
> Hi,
>
> Can I choose how, when using group by, the first() function works? I want it
> to pick the first record from a sort on one of the fields, but must I first
> create a query to sort the data, then run another query on top of that?
>
> Cheers,
> Chris
>
>[/color]