CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Jon Weinraub
Jon Weinraub

Reputation: 394

Return largest number in a group in MySQL

I have three MySQL tables that I use to keep track of uptimes internally. I like to do some basic stats (mainly for queries to run into an eventual jpGraph) and since all uptimes are kept for historical purposes, rather than discarding, I like to show the maximum in certain instances.

I can't figure out a way to do so. 

select * from stats
inner join servers
on stats.sid = servers.sid
inner join os
on servers.os = os.oid
order by uptime desc;

essentially, sid in stats can have non-unique server names since it is a foreign key for server_name in the servername table. So, the largest number in uptime should be only returned if there are multiple server sid's being listed, obviously if there is only one, it should be printed as well. I hope I explained this well enough.

sid uptime  sid os  server_name name    oid 
 7  47647995    7   3   weinraub.net    GNU/Linux Debian    3
 9  5474640 9   2   hera    Windows 7   2
 1  3498373 1   4   ares    Mac OS X Mountain Lion  4
 8  1024013 8   2   jweinraub-dt3   Windows 7   2
 8  946330  8   2   jweinraub-dt3   Windows 7   2
 8  687139  8   2   jweinraub-dt3   Windows 7   2

So, as you can see here, jweinraub-dt3 has three instances. I only want the largest uptime for each server_name.

mysql> desc stats;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| zid         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sid         | int(11)     | NO   |     | NULL    |                |
| uptime      | int(11)     | NO   |     | NULL    |                |
| lastCheckin | varchar(10) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

mysql> desc servers;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| sid         | int(11)     | NO   | PRI | NULL    | auto_increment |
| server_name | varchar(50) | NO   |     | NULL    |                |
| os          | int(11)     | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

mysql> desc os;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| oid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| logo  | mediumblob  | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

added table schema

Upvotes: 0

Views: 112

Answers (2)

echo_Me
echo_Me

Reputation: 37233

Try this:

select     ..., max(uptime)

from       stats

... 

group by   sid
order by   max(uptime) desc

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

Try finding the largest uptime for each sid.

 SELECT MAX(uptime) AS max_uptime,
        sid
   FROM stats
  GROUP BY sid

Then, join that virtual table to the other stuff.

SELECT whatever, whatever
  FROM (
         SELECT MAX(uptime) AS max_uptime,
                sid
           FROM stats
          GROUP BY sid
       ) AS s
  JOIN servers ON s.sid = servers.sid
  JOIN os ON servers.os AS os.oid
 ORDER BY max_uptime DESC

The trick here is to summarize (aggregate) the stats table before joining it to the other tables.

Pro tip: don't use SELECT * in software. Especially don't use it when you're trying to explain what you need in your result set.

Upvotes: 2

Related Questions