What about this hack?
SELECT TOP 1
(SELECT TOP 1 TipID
FROM TIPS
WHERE TipTipCategoryLink = #Q_Tip.TipTipCategoryLink#
ORDER BY TipTitle ASC) As FirstTip,
(SELECT TOP 1 TipID
FROM TIPS
WHERE TipTipCategoryLink = #Q_Tip.TipTipCategoryLink#
ORDER BY TipTitle DESC) As LastTip
FROM TIPS
I tried the Last() and First() functions and they didn't work in Query
Analyzer (on 7 and 2K). Weird.
-----Original Message-----
From: James Sleeman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 21, 2002 7:55 PM
To: CF-Talk
Subject: RE: SQL trickyness
At 04:12 PM 2/22/2002, you wrote:
>James,
>
>Rather than using Last() and First(), try Max() and Min(). Also, try
adding
>in a GROUP BY clause. Adding it in always seems to fix the error
messages
>about aggregate functions.
Min and Max will give me the minimum and maximum values without regard
to
where they occur in the recordset, I want the first and last values as
they
appear in the recordset
eg if the select gives a recordset like
[ID][TITLE]
1 Bar
3 Foo
2 Goop
I want to get
[MinimumID][MaximumID]
1 2
using a group by would give me
[MinimumID][MaximumID]
1 1
3 3
2 2
(because titles are unique).
James Sleeman
Innovative Media Ltd
Phone: (03) 377 6262
http://www.websolutions.co.nz/
CAUTION: The information contained in this email message is confidential
and may be legally privileged. If the reader of this message is not the
intended recipient you are notified that any use, dissemination,
distribution or reproduction of this message is prohibited. If you have
received this message in error please notify the sender immediately and
destroy the original message and any attachments.
Views expressed in this communication may not be those of Innovative
Media Ltd.
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
