About max and min


#1

Dear all,
Thank you for your time. I encounter a question which min and max. With fake_apps table, I want to show the name and category of the most downloaded app.
select name, category from fake_apps order by downloads desc limit 1;
this line would return what am i aiming for.
however, it seems i am supposed to use something related to max.
select name, category, max(downloads) from fake_apps;
it would return 3 columns and I only need the first two columns.
I am trying to write something like:
select name, category from fake_apps where downloads = max(downloads);
but it doesn't work.
any thoughts on what should i do?


#2

There was an exercise, 5. max, that sounds just like what you are asking for.

It looked like this:

SELECT name, category, MAX(downloads)
FROM fake_apps
GROUP BY category;

#3

Your code would display the most downloaded app per category. I am hoping to get the most downloaded app. and only include column of name and category.


#4

I see, that query gets this:

but you just want the name and category of the most downloaded app in the whole database?


#5

yes, thats what i want


#6

You could do this:

SELECT name, category
FROM fake_apps
ORDER BY downloads DESC
LIMIT 1;

#7

that is what i did, and plz read the post. I am asking if i could use max


#8

I definitely didn't read it all :smile:

You could do this:

SELECT name, category
FROM fake_apps
WHERE downloads=(
  SELECT max(downloads) FROM fake_apps
  );

but this SO post that I found it at doesn't seem to recommend it:
http://stackoverflow.com/a/7604911


#9

that is exactly what i am looking for. thanks a lot man!


#11

When I use avg(downloads) instead of max or min, why does the name of the app change? How does the query decide which name to use in the row to describe the average downloads within the category?

The code would be same as above, but instead of max or min, you would type "avg". I am just curious because I am interested in the logic of SQL as someone who is serious about learning more and improving my SQL coding abilities. Max and min are easy to understand, but what logic is used to decide the name for avg?

Thank you in advance for your answer and time.


#12

Remember SQL statement ALWAYS return a table of some sort.
Apparently, when you use MAX, the query returns a table with a row that contains the value for MAX(downloads) in downloads column. I think if the fake_app table contains two apps with the same largest values for the downloads column will return the table with two rows.

Conversely, when you use AVG what it gets returned should be a table with one row having one name, and one averaged value. But the name should come from name column. I think in this case (and usually) you get names from the last row in the fake_app table in each category. In fact, you get the same app names if you run:

SELECT name, category, downloads FROM fake_apps GROUP BY category;