PHP MySQL Order by Two Columns and limit by 1 result per first column

Question

Helo, I have this table:

CREATE TABLE `pupils` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` varchar(24) NOT NULL DEFAULT '',
`Sex`  varchar(8) NOT NULL DEFAULT '',
`Age` SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;
INSERT INTO `pupils` VALUES ( DEFAULT, 'Tom', 'male', 12);
INSERT INTO `pupils` VALUES ( DEFAULT, 'Audrina', 'female', 17);
INSERT INTO `pupils` VALUES ( DEFAULT, 'Darnell', 'male', 15);
INSERT INTO `pupils` VALUES ( DEFAULT, 'Dana', 'female', 11);
INSERT INTO `pupils` VALUES ( DEFAULT, 'Roberta', 'female', 14);
INSERT INTO `pupils` VALUES ( DEFAULT, 'Walton', 'male', 14);

Мy goal is to find the eldest students for both sexes ( this is just an example ). The result I have to get is the following:

Audrina
Darnell

I use this PHP script for this purpose:

<?php

include 'login.php';

function MySqlConnect( ) {
    global $host, $sql_username, $sql_password, $db_name;
    $con = new mysqli( $host, $sql_username, $sql_password, $db_name );
    if( $con->connect_errno ) { echo "ops1"; die( ); }
    return $con;
}

function GetNames( $con, &$all_names ) {
    global $tbl_pupils;
    if(!($result = $con->query( "SELECT Name,Sex FROM $tbl_pupils ORDER BY Sex,Age DESC" ) ) ) { echo "ops2"; die( ); }
    $names = [ ];
    while( $row = $result->fetch_row( ) ) {
        $curr_pupil = end( $names );
        if( !strcasecmp( $curr_pupil[1], $row[1] ) ) {
            $curr_pupil[0] = $row[0];
            $curr_pupil[1] = $row[1];
        } else {
            $names[ ] = array( $row[0], $row[1] );
        }
    }
    foreach( $names as &$curr_pupil ) $all_names[ ] = $curr_pupil[0];
    $result->free_result( );
}

$con = MySqlConnect( );
GetNames( $con, $all_names );
$con->close( );

foreach( $all_names as $name ) echo "$name<br>";

?>

My question is can I create a MySQL query with which to get only the necessary two rows of results?

Try `SELECT Sex, Name, Age FROM pupils GROUP BY Sex ORDER BY Age DESC` — Mickaël Leger, Jul 19 '18 at 11:56
Thanks! I tried it and got the following result: Audrina Tom — Ted, Jul 19 '18 at 12:00
What should you do if there's more than one student with given max age? Like, taking your example, Walton is 15, not 14? — raina77ow, Jul 19 '18 at 12:04
It does not bother me. The example is bad! In fact, I have more than 2 sexes. I'm sorry, I did not want right in my question to present third sex :( — Ted, Jul 19 '18 at 12:08

score 2 · Answer 1 · edited Jul 19 '18 at 12:46

2

One of the ways is union two results:

(SELECT * FROM `pupils` WHERE `Sex` = 'male' ORDER BY Age DESC LIMIT 1) 
UNION
(SELECT * FROM `pupils` WHERE `Sex` = 'female' ORDER BY Age DESC LIMIT 1)

Demo here

edited Jul 19 '18 at 12:46

Mickaël Leger

3,426
2
17
36

answered Jul 19 '18 at 11:58

u_mulder

54,101
5
48
64

score 2 · Accepted Answer · answered Jul 19 '18 at 12:14

2

Try this select name from pupils where age in (select max(age) from pupils group by sex);

answered Jul 19 '18 at 12:14

Danesh G

121
4

score 1 · Answer 3 · answered Jul 19 '18 at 12:05

1

You can use the union of the two individual queries.

(SELECT * FROM `pupils` WHERE `Sex` = 'male' ORDER BY `Age` DESC LIMIT 1) UNION (SELECT * FROM `pupils` WHERE `Sex` = 'female' ORDER BY `Age` DESC LIMIT 1);

answered Jul 19 '18 at 12:05

Tushar Udayan Tiwari

61
6

score 0 · Answer 4 · answered Jul 19 '18 at 11:59

0

I guess that will do it :

SELECT * FROM pupils as p1 
WHERE p1.Sex='male' ORDER BY p1.Age DESC LIMIT 1
UNION
SELECT * FROM pupils as p2 
WHERE p2.Sex='female' ORDER BY p2.Age DESC LIMIT 1

answered Jul 19 '18 at 11:59

PHP MySQL Order by Two Columns and limit by 1 result per first column

4 Answers4