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
18 1818 NeoPa 32,556
Expert Mod 16PB
Give us a clue as to what you're talking about. What data are you working with?
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........Country...........Popn
1............UK...............1000
2............USA.............2000
3............France..........1500
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
...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: -
-
SELECT TOP 1 Population, [Country Name] from qryLeastPopulations...
-
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
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 32,556
Expert Mod 16PB
...
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 :(
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........Country...........Popn..........Continen t
1............UK...............1000.............Eur ope
2............USA.............2000.............US
3............France..........1000.............Euro pe
So i would want a query that returned UK from this table.
Thanks in advance
NeoPa 32,556
Expert Mod 16PB
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.
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!!
...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: - SELECT Min(countries.ID) AS [min ID], Min(countries.Population) AS LowestPopulation, countries.Continent
-
FROM countries
-
GROUP BY countries.Continent;
Save this as QryLowest
Now feed this in to the only other query you need: - SELECT countries.ID, countries.Country, countries.Population, countries.Continent
-
FROM QryLowest INNER JOIN countries ON QryLowest.[min ID] = countries.ID
-
ORDER BY countries.Continent;
save this as QryLowestCountries.
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... QryLowestCountries - 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
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?
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
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 QryLowestCountries with -
-
SELECT Min(countries.ID) AS [Min Country], countries.continent
-
FROM QryLowest INNER JOIN countries ON (QryLowest.LowestPopulation=countries.Population) AND (QryLowest.continent=countries.continent)
-
GROUP BY countries.continent
-
ORDER BY countries.continent;
-
Then add a (truly and honestly) final query called QryFinalCountry -
-
SELECT Countries.ID, Countries.Country, Countries.Population, Countries.continent
-
FROM QryLowestCountries INNER JOIN Countries ON QryLowestCountries.[Min Country] = Countries.ID;
-
Test Data from revised QryLowestCountries - 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 32,556
Expert Mod 16PB
...
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 32,556
Expert Mod 16PB
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 : - Groups by the continent.
- 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.
- From here it ignores the [Population] part of the result and returns the value of the [ID] part.
- 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
Way to go, NeoPa! Concise, clear, well structured and in one query too! Subqueries are not my strong suit - yours is a great solution. Well done!
-Stewart
NeoPa 32,556
Expert Mod 16PB
Thanks very much Stewart.
I think I'll store a link to this one for when you're brains are better recognised on here and I can show people what you said :D
Thanks for all your help guys!! Will implement this when I get a chance today!!
NeoPa 32,556
Expert Mod 16PB
Good for you JC :)
Let us know how you get on with it.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: rong.guo |
last post by:
Greetings!
I have the 3 raw data tables below, and would like to extract some
data. My current query is really cumbersome, does anyone know a better
way to get all the info (attach the right...
|
by: psql-mail |
last post by:
I am running a SELECT to get all tuples within a given date range. This
query is much slwoer than i expected - am i missing something?
I have a table 'meta' with a column 'in_date' of type...
|
by: nick_faye |
last post by:
hi guys,
im still a newbie in ms-access and i would like to ask how can i
get the highest and the lowest date in ms-access report? for example,
i have these entries:
10/1/2003
10/5/2003...
|
by: cs8404 |
last post by:
I cannot quite figure out how to accomplish the following results.
My table is "Products" with the following fields:
ID Item Metal Size Price
1 Ring 18ctGold 4-7 $23.00...
|
by: Neo Geshel |
last post by:
I have an Access DB, from which I am going to pull images. Each image
has an associated ID, but the ID's are not necessarily sequential (some
images may have been deleted, leaving gaps in the list...
|
by: Douglas |
last post by:
Hi
Im not an SQL n00b, but I'm no SQL guru...
I'd like to work out the SQL code required to achieve the following goal in pseudocode:
Grap a record
Using info from that record, add (the...
|
by: coolindienc |
last post by:
I got this program running and gives me result that I want (actually not yet). Now I want to find out the lowest and highest numbers I entered. Andy advise???
number = int (input ("Enter a...
|
by: beacon |
last post by:
Hello to everybody,
I have a section on a form that has 10 questions, numbered 1-10, with 3 option buttons per question. Each of the option buttons have the same response (Yes, No, Don't know),...
|
by: tjc0ol |
last post by:
Hi guys,
I'm a newbie in php and I got error in my index.php which is:
1054 - Unknown column 'p.products_id' in 'on clause'
select p.products_image, pd.products_name, p.products_id,...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
| |