By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,945 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,945 IT Pros & Developers. It's quick & easy.

Getting the first value in a recordset

P: n/a
Hi

I am grouping on a number of fields in a select query. How can I get the min
value of a date field in the overall query result set? If I choose
min([date]) then I get the min dates in each of the group and not overall
min value.

Thanks

Regards
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sat, 16 Oct 2004 03:33:42 +0100, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Btw, "Date" is a reserved word. Rename your field to something like
"PaymentDate", "OrderDate", etc.

If I understand you correctly, you currently have:
select min([Date]) from MyTable group by SomeField, SomeOtherField

If you want the absolute minimum, just remove the Group By:
select min([Date]) from MyTable

-Tom.

Hi

I am grouping on a number of fields in a select query. How can I get the min
value of a date field in the overall query result set? If I choose
min([date]) then I get the min dates in each of the group and not overall
min value.

Thanks

Regards


Nov 13 '05 #2

P: n/a
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41**********************@news-text.dial.pipex.com...
Hi

I am grouping on a number of fields in a select query. How can I get the
min
value of a date field in the overall query result set? If I choose
min([date]) then I get the min dates in each of the group and not overall
min value.

use a subquery. Following gives a count of all products in the Northwind
database grouped by category, together with the overall minimum price

select c.CategoryName, count(*) as nbrProducts,
(
select min(UnitPrice)
from Products
) as overallMinimimPrice
from products as p
inner join categories as c on p.CategoryID = c.CategoryID
group by c.CategoryName
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.