using MIN in a query

T

thread

Hi all,
I'm building a query and i would like to pull all the data from the
same row where specifc column indicate the MIN function
how should i do it?
 
M

Matt

Hi all,
I'm building a query and i would like to pull all the data from the
same row where specifc column indicate the MIN function
how should i do it?
Click to expand...


Are you saying that you want to say pull the record of a person that
has the lowest point total or something along those lines.

I think that is what you are trying to do, but I had trouble
understanding your question.
 
R

Rick Brandt

thread said:
yes exacly this is what i meant
Click to expand...

If you only want a single record at a time then it is fairly simple...

SELECT TOP 1 *
FROM TableName
WHERE SomeField = SomeCriteria
ORDER BY SomeOtherField DESC

The ORDER BY ... DESC clause will cause the row with the smallest value for
that field to be at the top of the RecordSet and the TOP 1 clause will cause
that to be the only row returned.

If you want multiple rows with each one being the row with the minimum value
per a field you use Group By on then you either need to use two queries or a
sub-query.
 
R

Rick Brandt

Rick said:
If you only want a single record at a time then it is fairly simple...

SELECT TOP 1 *
FROM TableName
WHERE SomeField = SomeCriteria
ORDER BY SomeOtherField DESC

The ORDER BY ... DESC clause will cause the row with the smallest
value for that field to be at the top of the RecordSet and the TOP 1
clause will cause that to be the only row returned.

If you want multiple rows with each one being the row with the
minimum value per a field you use Group By on then you either need to
use two queries or a sub-query.
Click to expand...

Sorry, obviously the DESC would give you the row with the MAX value rather
than the MIN value. Just remove that.
 
M

Matt

do you have an example of how to do a sub query?
Rick Brandt כתב:




- Show quoted text -
Click to expand...



You could do something like

SELECT a.UserID
FROM tblStats a
Where a.points = (SELECT Min(points) FROM tblStats)

The sub query is (SELECT Min(points) FROM tblStats). It simply
returns the least amount of the "points" field in the table
"tblStats".
Then you are just selecting UserID where points = least amount of
points in the table.

Caution: If 2 people are tied for the lowest amount of points then it
will return both of those records

This is a very generic example, but you get the idea.
 
T

thread

Hi,
how can i give the same record in the total query
when i have one column with min and i would like to get the next
column based on the first column for example

period month :4
amount(besed on the first column) 1000
?
(when i have in the third column the sum of another data
i hope i explained myself good

Matt כתב:
 
Top