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]

"C L Humphreys" <cl*********@toofgib.moc> wrote in message
news:bn**********@ucsnew1.ncl.ac.uk...

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?

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]

-----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/AwUBP57R2YechKqOuFEgEQLJuwCg75FXKLnFw0pBVtrQqNpBLx mX1cMAoLMC
a16RqZ/BtN7Fji3+BlUknJLu
=WqDY
-----END PGP SIGNATURE-----

C L Humphreys wrote:

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