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

min function doesn't work

P: 5
I have a query that gives me a desired result of 700 records. The query is being run on a table:

SELECT Min(my.number) AS numberOfMin, my.hello, my.boy, my.girl, my.truck, my.kid, my.mom, my.dad, my.man, my.woman, my.duck, my.goose, my.cat
FROM my
GROUP BY my.hello, my.boy, my.girl, my.truck, my.kid, my.mom, my.dad, my.man, my.woman, my.duck, my.goose, my.cat;


If, however, I add one more column (added at end of query - my.dog), I get one more record returned in my output than desired (a result of 701 records):

SELECT Min(my.number) AS numberOfMin, my.hello, my.boy, my.girl, my.truck, my.kid, my.mom, my.dad, my.man, my.woman, my.duck, my.goose, my.cat, my.dog
FROM my
GROUP BY my.hello, my.boy, my.girl, my.truck, my.kid, my.mom, my.dad, my.man, my.woman, my.duck, my.goose, my.cat, my.dog;

If I add the remaing columns in the table, I get even more results (records)
What is going on here? I am baffled. . . The results just go up as I add more columns
Sep 6 '07 #1
Share this Question
Share on Google+
2 Replies


P: 47
The GROUP BY statement means that the query returns a value for every UNIQUE combination of the selected fields, so as you add more fields you create more combinations.

e.g if I have the following values in a table

Col1|Col2|Col3
A|B|C
A|C|B
A|B|D

Then if I select Col1 and group by Col1 I get only the value A
If I select Col1 and Col2 group by Col1, Col2 I get:

AB, and AC only.
(Since the combination AB is repeated it will only be selected once.)

Selecting all 3 and grouping on all 3 returns everything because all combinations are unique - i.e there are no repeated combinations over the 3 columns.

Hope this helps
Sep 6 '07 #2

P: 5
Ok, thanks a lot. Still just getting started with access / DBs in general.

So I suppose the best way to select entire records from a table based on a min value is to run a query using 2 columns, the group by column that I want to group with and the min column. Then I can use the results from this query with a table to pull the records I am interested in with the additional columns.

Thanks for the help!
Sep 7 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.