query to display the student table with students of age more than 18 with unique city

Question

My table in MySQL :

mysql> select * from student;
+-----+----------+--------+------+---------+
| ano | name     | gender | age  | place   |
+-----+----------+--------+------+---------+
| 114 | ron      | m      |   18 | cbe     |
| 115 | dhruv    | m      |   18 | cbe     |
| 116 | mini     | f      |   23 | chennai |
| 117 | yash     | m      |   20 | chennai |
| 118 | aathmika | f      |   19 | delhi   |
| 119 | aadhi    | m      |    9 | pune    |
+-----+----------+--------+------+---------+

There was a question called : Create a query to display the student table with students of age more than 18 with unique city.

According to me, required output :

+-----+----------+--------+------+---------+
| ano | name     | gender | age  | place   |
+-----+----------+--------+------+---------+
| 116 | mini     | f      |   23 | chennai |
| 118 | aathmika | f      |   19 | delhi   |
+-----+----------+--------+------+---------+

Or 

+-----+----------+--------+------+---------+
| ano | name     | gender | age  | place   |
+-----+----------+--------+------+---------+
| 117 | yash     | m      |   20 | chennai |
| 118 | aathmika | f      |   19 | delhi   |
+-----+----------+--------+------+---------+

I've tried the following :

mysql> select distinct place from student where age>18;
+---------+
| place   |
+---------+
| chennai |
| delhi   |
+---------+
2 rows in set (0.05 sec)

I tried to add unique key to place field to delete the second record with cbe, whereas my assumption was wrong.

mysql> alter table student add constraint unique(place);
ERROR 1062 (23000): Duplicate entry 'cbe' for key 'place'

mysql> alter table student modify place char(10) unique;
ERROR 1062 (23000): Duplicate entry 'cbe' for key 'place'

mysql> alter table student change place place char(10) unique;
ERROR 1062 (23000): Duplicate entry 'cbe' for key 'place'

mysql> select place from student where age>18 group by place having count(place)
=1;
+-------+
| place |
+-------+
| delhi |
+-------+

Also,

mysql> select distinct place,name,ano,age from student where age>18;
+---------+----------+-----+------+
| place   | name     | ano | age  |
+---------+----------+-----+------+
| chennai | mini     | 116 |   23 |
| chennai | yash     | 117 |   20 |
| delhi   | aathmika | 118 |   19 |
+---------+----------+-----+------+
3 rows in set (0.00 sec)

When I use many fields along with distinct place, it's distinct characteristic is lost!!!

What changes shall I make in any of the above queries to get the desired output???

Thanks in advance.

This is a similar problem: [Select query return 1 row from each group](https://stackoverflow.com/questions/14375099/select-query-return-1-row-from-each-group). — Rup, Oct 23 '20 at 07:34
DISTINCT always applies to ALL columns in the SELECT, so the 'distinct combination of all selected columns' — Strawberry, Oct 23 '20 at 07:39
IF aathmika is also a student in chennai then this scenario would fit the question but the solution would be different. — P.Salmon, Oct 23 '20 at 07:46

score 3 · Answer 1 · answered Oct 23 '20 at 08:03

Create a query to display the student table with students of age more than 18 with unique city

I understand this as: the student should be more than 18, and their place should appear only once in the table. Only one row meets this criteria, that is ano 118 (Aathmika is 19 years old, and no other student lives in Delhi).

You could phrase this as:

select s.*
from student s
where 
    age > 18 
    and not exists(select 1 from student s1 where s1.place = s.place and s1.ano <> s.ano)

score 0 · Answer 2 · edited Oct 29 '20 at 07:45

0

Important note : Check your sql_mode before executing this query.

You can try with the following query:

select * from student where age > 18 group by place;

You filter the age with the where statement and then you make the place unique with a group by.

edited Oct 29 '20 at 07:45

Sanmitha Sadhishkumar

58
1
23

answered Oct 23 '20 at 08:02

Michele Della Mea

966
2
16
35

For me, for the above query, mysql shows the Column 'name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Oct 23 '20 at 15:17
My mysql version - 5.7.31. Is that error due to version or due to wrong query? – Oct 23 '20 at 15:18
To be specific : ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cs.student.ano' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Sanmitha Sadhishkumar Oct 25 '20 at 16:48
It looks like you have a different mysql configuration. If you want that the above query works you can give a look at the following SO discussion: https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc – Michele Della Mea Oct 26 '20 at 15:42
Thank you @Sanmitha Sadhishkumar for quoting the exact query – Oct 29 '20 at 03:32
Thank you @Michele Della Mea for providing that apt link. Thank you so much – Oct 29 '20 at 03:32

score 0 · Answer 3 · answered Oct 29 '20 at 15:51

SQL

SELECT *
FROM student s1
WHERE age > 18
      AND NOT EXISTS
      (SELECT * FROM student s2
       WHERE s2.ano < s1.ano  /* Or could alternatively use > here */
         AND s2.age > 18
         AND s2.place = s1.place);

Demo

dbfiddle.uk demo

Have added a couple of extra rows in the demo for testing purposes - please note that some of the other answers fail with this data.

score 0 · Answer 4 · answered Oct 29 '20 at 16:31

For each distinct place you want only 1 row returned under the condition age > 18.

Since you don't care which row will be returned, if there are more than 1 rows that satisfy the conditions you set, you can GROUP BY place and for each place get only 1 of ano with this query:

SELECT ANY_VALUE(ano) ano 
FROM student
WHERE age > 18
GROUP by place

The aggregate function ANY_VALUE() will choose 1 value of ano for each place.

The above query can be joined to the table student on the column ano and return your expected results:

SELECT s.* 
FROM student s
INNER JOIN (
  SELECT ANY_VALUE(ano) ano 
  FROM student
  WHERE age > 18
  GROUP by place
) t ON t.ano = s.ano

See the demo.

Note that instead of ANY_VALUE() you could also use MIN() or MAX(), since you don't care which row will be returned for each place.

score 0 · Answer 5 · answered Oct 29 '20 at 19:28

From the question, I think the result should have students with age>18 and the city should be only once in the table.

select * from student group by place having age>18 and count(*) = 1;

This query groups by place, checks age and return records for which there is only one row in the group.

score 0 · Answer 6 · answered Oct 29 '20 at 19:49

if you are not bothered of ambiguity in results - as you expected (mini and yash are from chennai and are above 18), i think following queries may help you

SELECT ano,name, age from student where ano in(SELECT MIN(ano) FROM student WHERE age>18 GROUP BY place)

RESULTS

117 MINI 23 chennai
118 AATHMIKA 29 delhi

OR YOU CAN USE

SELECT ano,name, age from student where ano in(SELECT MIN(ano) FROM student WHERE age>18 GROUP BY place)

RESULTS

4   yash    20 chennai
5   aathmika    19 delhi

query to display the student table with students of age more than 18 with unique city

6 Answers6

SQL

Demo