Select One Lowest Total

[JC2710]

I have a query that picks the lowest total by using a Group By clause. But if there are two lowest totals of the same amount I only want my query to return one value.

Any ideas?

Thanks

[NeoPa]

Give us a clue as to what you're talking about. What data are you working with?

[JC2710]

Well i can put it simply. Using an example which explains basically what I want. It seems that either Im bad at SQL or SQL is bad. OK here goes

Lets say you have a table of countrys and their populations....

ID........Count ry...........Po pn

1............UK ............... 1000
2............US A.............2 000
3............Fr ance..........1 500

Ok. Now all i want to do is select the country with the lowest population. Sounds easy. But it seems as though its impossible.

If you use a group by query all you can return is the population so you get an answer of 1000. Which is of no use to anyone because it doesnt tell you which country has that population.

So i have come to the conclusion i need two queries and link them. But if two countrys have same popn I only want to return 1 country. It doesnt matter which country but I only want to return one record. Any ideas?

Thanks

[Stewart Ross]

...Now all i want to do is select the country with the lowest population. Sounds easy. But it seems as though its impossible...

You will need a third query to do this, fed by your second query (the one with the countries joined back to the lowest populations). Use the TOP 1 predicate before the list of fields in this third query, like this:
[CODE=SQL]
SELECT TOP 1 Population, [Country Name] from qryLeastPopulat ions...
[/CODE]
Although TOP 1 is not designed to choose between equal values it is returning a single row when I test it. I do think that you are being somewhat unkind to SQL (and yourself!) when you say that either you are bad or SQL is. If you think about it, SQL has no direct means of knowing that you want to arbitrarily throw away a valid row from a correct answer to your question. If it did this by default such a bug would mean nobody could use or rely on SQL at all!!

-Stewart

[JC2710]

Thanks for your help! Much appreciated! But as usual im afraid this doesnt work in my case. In my case i have more than one group and i want to select the lowest in each group. So TOP 1 just returns the first record, not the lowest in each group. Thanks Anyway.

[NeoPa]

...
Ok. Now all i want to do is select the country with the lowest population. Sounds easy. But it seems as though its impossible.
...

Not impossible, but SQL isn't VERY helpful there I'm afraid.
Also, you've not shown data that's grouped by any particular field. My understanding of what you want is the/a record that has the lowest population.

SELECT Val(Mid(Min(Format(tbl.Popn, '0000000000') & tbl.ID), 11)) AS ID, _
       Mid(Min(Format(tbl.Popn, '0000000000') & tbl.Country), 11) AS Country, _
       Min(tbl.Popn) AS Popn
FROM [YourTable] AS tbl

This CAN be done within groups too, but there's nothing here to group by :(

[JC2710]

Thanks!! Much appreciated Although seems a lot of fuss just to get the simplest of querys.

And if they had a group of continents and i wanted to find the lowest pop of each continent? And only return 1 record if the pop were equal?

ID........Count ry...........Po pn..........Con tinent

1............UK ............... 1000........... ..Europe
2............US A.............2 000............ .US
3............Fr ance..........1 000............ .Europe


So i would want a query that returned UK from this table.

Thanks in advance

[NeoPa]

It's only simple if you look at it from the perspective of not understanding databases. From a database point of view it's not remotely simple.

I will look at getting a post together for you later when I have some more time. I doubt the UK would be returned in your dataset though. As UK pop = France pop so either would be selected. I do need to amend my basic logic though, as selecting TRULY randomly can produce field results from different records which would not be good.

[JC2710]

I appreciate yr help! I must sound really ungrateful!! But SQL just seems more trouble than its worth!! As I said earlier in my post maybe I just dont get it!! But if its this much hassle to do something so simple I wonder if its worth my time!
It seems that just to select the lowest record in each group i need 3 querys all linked together!!

Cant i just have SELECT Country FROM table WHERE Popn = Min(Popn)....I know this isnt right but why cant it be??

All i want to do is select the country with the lowest population..... ....it just seems ridiculous!! Im thinking about going back to pen and paper and a nice big filing cabinet!! Would save an awful lot of time!! Sorry for sounding ungrateful!!

I do appreciate yr help!!

[Stewart Ross]

...All i want to do is select the country with the lowest population..... ....it just seems ridiculous!! Im thinking about going back to pen and paper and a nice big filing cabinet!! Would save an awful lot of time!! Sorry for sounding ungrateful!!

Ahh, but there IS a simple solution - now that in your previous posts you have associated a continent code with each country and alongside your country ID. Without these, as NeoPa pointed out, there was nothing to group on. With these - all the difference :)

Starting with a table called
Countries

ID; Long; PK
Country: Text;
Population: Long;
Continent: Text;

Find the lowest population grouped by continent, and include the lowest country ID:
[CODE=SQL]SELECT Min(countries.I D) AS [min ID], Min(countries.P opulation) AS LowestPopulatio n, countries.Conti nent
FROM countries
GROUP BY countries.Conti nent;[/CODE]
Save this as QryLowest

Now feed this in to the only other query you need:
[CODE=SQL]SELECT countries.ID, countries.Count ry, countries.Popul ation, countries.Conti nent
FROM QryLowest INNER JOIN countries ON QryLowest.[min ID] = countries.ID
ORDER BY countries.Conti nent;[/CODE]
save this as QryLowestCountr ies.

Test data to show that this solution works, with numbers mostly made up:
Countries

ID Country Population Continent
1 France 60 Europe
2 USA 200 North America
3 UK 60 Europe
4 Four 100 Europe
5 Canada 100 North America
6 Elsewhere 1000 Asia
7 Another 1000 Asia
8 And Another 2000 Asia
9 Finally 80 Europe

QryLowest

min ID LowestPopulation Continent
6 1000 Asia
1 60 Europe
2 100 North America

And the proof of this particular pudding...
QryLowestCountr ies

ID Country Population Continent
6 Elsewhere 1000 Asia
1 France 60 Europe
2 USA 200 North America

Done!! No need for despair... Just shows how important it is to define something that allows SQL to group the data, though, and in this case the key changes from your first post were the inclusion of a continent code along with the unique country ID.

-Stewart

[JC2710]

Hi Stewart!

Thanks for all your help! It looks like youve put alot of effort in! I really appreciate it but im afraid it doesnt seem to work!!!

From what i can see shouldnt Canada be returned as the lowest population in North America?

[Stewart Ross]

Hi Stewart!
Thanks for all your help! It looks like youve put alot of effort in! I really appreciate it but im afraid it doesnt seem to work!!!

From what i can see shouldnt Canada be returned as the lowest population in North America?

All through today I have been confusing lowest and highest values - well, we're very near now, so I'll check it out soon and post again :(

-Stewart

[Stewart Ross]

All through today I have been confusing lowest and highest values - well, we're very near now, so I'll check it out soon and post again :(

More haste less speed and all that... Anyway, back to a three-query solution which picks up the minimum country ID correctly. My logic error was in picking up the minimum country ID at the same time as the minimum population - it could only be picked out after the creation of the set of lowest populations.

Replace query QryLowestCountr ies with

[CODE=SQL]
SELECT Min(countries.I D) AS [Min Country], countries.conti nent
FROM QryLowest INNER JOIN countries ON (QryLowest.Lowe stPopulation=co untries.Populat ion) AND (QryLowest.cont inent=countries .continent)
GROUP BY countries.conti nent
ORDER BY countries.conti nent;
[/CODE]
Then add a (truly and honestly) final query called QryFinalCountry
[CODE=SQL]
SELECT Countries.ID, Countries.Count ry, Countries.Popul ation, Countries.conti nent
FROM QryLowestCountr ies INNER JOIN Countries ON QryLowestCountr ies.[Min Country] = Countries.ID;
[/CODE]
Test Data from revised QryLowestCountr ies

Min Country continent
6 Asia
1 Europe
5 North America

Final Test Data from QryFinalCountry

ID Country Population continent
6 Elsewhere 1000 Asia
1 France 60 Europe
5 Canada 100 North America

(Note to self: check, check and CHECK AGAIN before finalising responses...)

Regards

Stewart

[NeoPa]

...
Can't I just have :

SELECT Country
FROM table
WHERE Popn = Min(Popn)

I know this isnt right but why cant it be??
...

Firstly, this would not select only ONE of the records if more than one shared the lowest value (See France - UK in example data).
Secondly, Min(Popn) would have to be a different value depending on which continent (grouping) it was from.

What we can do is similar in construct to this, but does involve using a subquery (See Subqueries in SQL). Subqueries are very like defining a separate query and using it as a named recordset in the main query, except they are defined in the one set of SQL. If you can be patient a little while longer I'll put together a set of SQL for this problem which is as similar as possible to your stated construct and which uses a subquery to help select the records required...

[NeoPa]

As it happens, there was no real need for a WHERE clause, as the INNER JOIN to the subquery did the full selection of the records matching the criteria you specified. For explanation, the subquery simply :

1. Groups by the continent.
2. Adds the [ID] field to the end of the [Population] field (formatted to ten chars to avoid mixing the fields up) and finds the smallest result in the group.
3. From here it ignores the [Population] part of the result and returns the value of the [ID] part.
4. The outer part of the query simply uses this dataset (of specified records) to select the records of interest from [Table].

ID Country   Population Continent
 1 France         60    Europe
 2 USA           200    North America
 3 UK             60    Europe
 4 Germany       100    Europe
 5 Canada        100    North America
 6 India       1,000    Asia
 7 Pakistan    1,000    Asia
 8 China       2,000    Asia
 9 Luxembourg     80    Europe

SELECT [Continent],
       Table.ID
       [Country],
       [Population],
FROM Table INNER JOIN
     (SELECT Val(Mid(Min(Format([Population], '0000000000') & 
                         Table.ID), 11)) AS ID
      FROM [Table]
      GROUP BY [Continent]) AS sub
  ON Table.ID = sub.ID
ORDER BY [Continent]

This produced :

Continent     ID  Country Population
Asia           6  India     1,000
Europe         1  France       60
North America  5  Canada      100