473,320 Members | 1,867 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting the first value in a recordset

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
2 2213
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bryan Harrington | last post by:
I have a list of requests that I'm displaying. The client wants to be able to display the details of the FIRST request as the page loads. Right now, as I loop through the recordset, I'm checking...
15
by: U N Me | last post by:
I have a continuous form that lists records from a query. One of the columns is an amount field, In the footer of the form I place a text box that displays the total (sum) of the amount. The...
10
by: Lyn | last post by:
I have a form set to Single Form mode with which I can cycle through the records in a table via Next and Previous buttons. To avoid users pressing the Previous button on the first record and the...
5
by: Thelma Lubkin | last post by:
I have a form/subform with the common one-to-many relationship. The form allows user to display records and move to other records via the selector, to add,delete, and edit them, with the related...
6
by: Max | last post by:
Anyone know why I'm always getting 0 returned? My stored procedure returns -1. Dim iErrorCode As Int32 iErrorCode = Convert.ToInt32(SqlHelper.ExecuteScalar(AppVars.strConn, _ "gpUpdateMember",...
5
by: noLoveLusT | last post by:
hi everyone i am very very new to the sql server (2 days actually and ) so far i learned creating SPs etc but couldnt workout how to get return value from my prodecure my sp as follows...
1
by: Mike Lester | last post by:
I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset. I can get either but not both. (ie. if there is a select...
7
by: ammmmmu | last post by:
Hi all, I am using VB 5.0 and msaccess as a database, I am reading the data from logfiles and inserting it in db, its not throwing any error, but after excecution I not find any records in table...
1
by: bluepiper | last post by:
Im using VB6. On rowcolchange event of may datagrid, im using the Frame caption to get the selected data. The problem is when I select the data on the grid. Its not showing the absolute position,...
0
by: ppletkov | last post by:
Hi - I apologize if this is a simple question in advance :-) (hopefully it is so its quickly resolved!) i'm trying to figure out why i keep getting an error (Compile Error : expected function or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.