Optimized mysql queries in mysql database

Question

I want to fetch 3 rows data from class table who is belongs to class 5,6 and 7 last row only.

Right now I'm using 3 sql queries to fetch data:

 1. SELECT * from class where class_name = '5';

 2. SELECT * from class where class_name = '6';

 3. SELECT * from class where class_name = '7';

How can I use single sql query to retrieve 3 row data instead of 3 sql queries ?

**class table structure:**

|id | class_name | student  |
|---| -----------| ---------| 
|1  |      5     | Student A|
|2  |      6     | Student B|
|3  |      4     | Student C|
|4  |      6     | Student D|
|5  |      7     | Student E|
|6  |      5     | Student F|
|7  |      4     | Student G|
|8  |      6     | Student H|
|9  |      5     | Student I|
|10 |      6     | Student J|
|11 |      7     | Student K|
|12 |      6     | Student L|
|13 |      8     | Student M|
|14 |      6     | Student N|
|15 |      8     | Student O|

Result required: Student I,Student N and Student K

This is the most frequently asked question under this tag. Occasionally, it's correctly answered too. For a definitive solution, please refer to the manual https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html — Strawberry, Jun 07 '17 at 06:42

Bilal Ahmed · Answer 1 · 2017-06-07T06:38:26.967

1

try this

SELECT * FROM class WHERE id IN (SELECT MAX(id) FROM class WHERE class_name in (5,6,7) GROUP BY class_name );

sqlfiddle

edited Jun 07 '17 at 06:38

answered Jun 07 '17 at 06:01

Bilal Ahmed

4,005
3
22
42

I want only last row who belongs to class 5, class 6, class 7. above sql query will not return data if class 5 and 6 doesn't have data. – John Jun 07 '17 at 06:02
result should be Student I,Student N and Student K – John Jun 07 '17 at 06:04
in this query we used `OR` condition.. this query retrieve data 5 or 6 or 7 class.. – Bilal Ahmed Jun 07 '17 at 06:04
first you correct id (1,2,2,2,2,2,2,2...) its not true.. id should have (1,2,3,4,5.....) – Bilal Ahmed Jun 07 '17 at 06:07
I need only 1 records with class 5 and class 6 and class 7 result will be Student I,Student N and Student K Is this possible ? – John Jun 07 '17 at 06:07
you mention the desire result in your question – Bilal Ahmed Jun 07 '17 at 06:09

score 0 · Answer 2 · edited Jun 07 '17 at 13:26

0

select * from (select * from class order by id desc, class_name) x group by class_name

edited Jun 07 '17 at 13:26

MZaragoza

10,108
9
71
116

answered Jun 07 '17 at 05:59

Tushar Walzade

3,737
4
33
56

That's return all data of sql queries. I want only last row who belongs to class 5, class 6, class 7 – John Jun 07 '17 at 06:01
please try this - SELECT * from class where class_name in (5, 6, 7) order by id DESC limit 1; – Tushar Walzade Jun 07 '17 at 06:04
not working, because indexing will return only last record in table result will be Student I,Student N and Student K – John Jun 07 '17 at 06:05
Do you want last 3 rows totally ? First one will be of class 5, second of 6 & last of 7 ? – Tushar Walzade Jun 07 '17 at 06:09
I want class 5 last row data, class 6 last row data, class 7 last row data. result will be Student I,Student N and Student K according to table – John Jun 07 '17 at 06:11
Okay, now got your question. Please update it this way. – Tushar Walzade Jun 07 '17 at 06:12
if it doesn't work, please refer this - https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results – Tushar Walzade Jun 07 '17 at 06:20
your latest answer is helpful :) – John Jun 07 '17 at 06:29

score 0 · Answer 3 · answered Jun 07 '17 at 06:18

0

SELECT * from (SELECT * from class ORDER BY id  DESC) t WHERE class_name in (5,6,7) GROUP BY class_name ORDER BY id DESC

answered Jun 07 '17 at 06:18

king

300
1
13

can you explain what you are doing? – MZaragoza Jun 07 '17 at 13:26

score 0 · Answer 4 · answered Jun 07 '17 at 06:35

Use a subquery since GROUP BY cannot be used with a wildcard. Instead you have to group by every column that you select.

SELECT * FROM class
WHERE id IN (
    SELECT MAX(id) FROM class
    WHERE class_name IN (5, 6, 7) GROUP BY class_name ORDER BY id DESC
);

score 0 · Answer 5 · answered Jun 07 '17 at 06:49

SELECT * FROM class 
WHERE id IN (
    SELECT MAX(ID) 
    FROM dbo.class 
    WHERE class_name IN (5,6,7) 
    GROUP BY class_name )

This should do the trick, tested and here's the result:

id          class_name student
----------- ---------- --------------------
1           5          A                   
2           6          B                   
3           4          C                   
4           6          D                   
5           7          E                   
6           5          F                   
7           4          G                   
8           6          H                   
9           5          I                   
10          6          J                   
11          7          K                   
12          6          L                   
13          8          M                   
14          6          N                   
15          8          O                   

(15 row(s) affected)

id          class_name student
----------- ---------- --------------------
9           5          I                   
11          7          K                   
14          6          N                   

(3 row(s) affected)

score -1 · Answer 6 · answered Jun 07 '17 at 06:00

-1

SELECT * from class where class_name = '5' or class_name = '6' or class_name = '7'

answered Jun 07 '17 at 06:00

wijaya

152
7

Optimized mysql queries in mysql database

6 Answers6