Mysql – Find results with greatest difference

MySQL

I have a database which has a user's real age and the age that people have guessed them to look like in an uploaded photo.

I want to find the photos which have the greatest difference between guessed age and actual age. What Query could I run?

For example, the two rows that I am interested in are as follows.

age | avg
18 | 34.45
32 | 33.23
45 | 28.21

Thank you

Best Answer

The query below will return the maximum of the difference age from the guessed age:

SELECT MAX(DiffAge) AS MaxDifference
FROM (
    SELECT (`avg` - `age`) AS DiffAge 
    FROM `AgeTest`
) AS Result

Sample execution with given sample data:

CREATE TABLE `AgeTest` (`age` INT, `avg` numeric(18,2));

INSERT INTO `AgeTest` (`age`, `avg`) VALUES (18,34.45);
INSERT INTO `AgeTest` (`age`, `avg`) VALUES (32,33.23);
INSERT INTO `AgeTest` (`age`, `avg`) VALUES (45,28.21);

SELECT MAX(DiffAge) AS MaxDifference
FROM (
    SELECT (`avg` - `age`) AS DiffAge 
    FROM `AgeTest`
) AS Result

Working Demo: http://rextester.com/BSEEG25743

Related Solutions

Mysql – Too many records on database and how to handle it? MySQL is it good to clone

Partitioning and proper indexing plus do not forget proper multi server setup. Start from MySQL documentation on indexing and partitioning. When you hit such db size you should be able to afford somebody who knows what to do.

For big datasets you might want to look in to other solutions like Hadoop. Do not forget caching Redis/memcached.

Based of my experience with big data sets. Your table has too many columns, does not have proper indexing. So in case you do proper indexing and partitioning on production(properly setup) speed on indexed, partitioned columns would start degrade after 40,000,000-50,000,000 per partition.

MySQL – How to Select Rows Where Condition Exists

This is a "relational division" problem and there are several ways to solve.

One way is with two EXISTS subqueries:

SELECT p.id 
FROM photos AS p
WHERE EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cute'
      )
 AND  EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cool'
      ) ;

Another with two joins (assuming that the photo_tags table has a unique constraint on (photo_id, tag_id), i.e. no photo can been tagged with the same tag twice):

SELECT p.id 
FROM photos AS p
  JOIN photos_tags AS pt1
    ON  p.id = pt1.photo_id 
    AND pt1.tag = 'cute'
  JOIN photos_tags AS pt2
    ON  p.id = pt2.photo_id 
    AND pt2.tag = 'cool' ;

One more way is @Abhik's answer, with one join and GROUP BY.

You can see a few more ways (more than 13) in this awesome answer: How to filter SQL results in a has-many-through relation. Not all of them will work in MySQL as the question is for Postgres but the problem is identical.

Strictly speaking - and if there is a FOREIGN KEY from photo_tags to photos - the photos table can be removed from all the above queries. You can return the photo_id instead. But I guess you may want more columns from the photo table in the result set.

Best Answer

Related Solutions

Mysql – Too many records on database and how to handle it? MySQL is it good to clone

MySQL – How to Select Rows Where Condition Exists

Related Question