Select id of smallest record value

[Fredrated]

Here is a problem I am soliciting solutions to:

I have a list of records, each record has an id field and a value field, and
I want the id of the record with the smallest value in the value field.
ex.:
id value
1 25
2 13
3 179

Is there an easy way to construct a query that returns id=2?

If I try to use a 'totals' query and use the Min function on the value
field, I have no 'aggregate function' to include the id in so that only 1
record is returned.

The origin of the problem is how to extract the 'next meeting day' from a
list of meetings, where the 'next meeting day' is the first meeting (record)
whose meeting day is today or after today.

Thanks for any help.

Fred

 

[KARL DEWEY]

I have no 'aggregate function' to include the id in so that only 1 record
is returned.
That is correct - there is only ONE smallest value.
What has 'smallest value' to do with 'next meeting day'? That is the old
'apples and oranges' thing.
Post your table structure with field names and datatype. Post sample data.
Hopefully you are using a DateTime field for your meeting days and not a
'value.'

 

[Brian Evans]

Here is a problem I am soliciting solutions to:

I have a list of records, each record has an id field and a value field,
and
I want the id of the record with the smallest value in the value field.
ex.:
id value
1 25
2 13
3 179

Is there an easy way to construct a query that returns id=2?

If I try to use a 'totals' query and use the Min function on the value
field, I have no 'aggregate function' to include the id in so that only 1
record is returned.

The origin of the problem is how to extract the 'next meeting day' from a
list of meetings, where the 'next meeting day' is the first meeting
(record)
whose meeting day is today or after today.

Thanks for any help.

Fred

SELECT TOP 1 ID
FROM SOMETABLE
ORDER BY value;

Brian

 

[Fredrated]

What has 'smallest value' to do with 'next meeting day'? That is the old

'apples and oranges' thing.

days can be considered 'ordered' because they can be compared using <, <=,
=, >= and >. Thus the 'smallest value' in a list of dates is the date
xx/xx/xxxx for which the statement is true that all other days in the list
are >= xx/xx/xxxx. No apples and oranges problem here that I can see, as
long as the list is finite it has a record that satisfies this condition.

I am sure that if someone provides code or a query that solves the simple
example I have provided, I can morph that solution into one I can use. To
reiterate, solve the problem using:

id value
1 25
2 13
3 179

where id and value are integers.

Fred

 

[Fredrated]

That does the trick, the full query that gets the id I want is:

SELECT TOP 1 meeting.id
FROM meeting
WHERE (((meeting.mdate)>=Date()) AND ((meeting.g_id)=2))
ORDER BY meeting.mdate;

This gives me the next meeting day for group member 2,

Thanks!