trouble in Group by query

Question

todo：I grouped a set of data by c_id and took the data for the maximum value of the height of each group. problem：The data that does not display the maximum value correctly like c_id=3 in picture no.2.

I want to ask how to fix it, thanks.

If performance is important, consider a solution along these lines: http://sqlfiddle.com/#!9/096589/1 Note the index on c_id, height - and for next time see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jul 30 '18 at 11:03

Sinto · Answer 1 · 2018-07-30T10:03:53.663

1

You can check this query out:

SELECT `id`, `name`, `gender`, `age`, `c_id`, MAX(`height`) AS height 
FROM `student` 
GROUP BY `c_id` 
ORDER BY `height` DESC;

Here is the Demo

edited Jul 30 '18 at 10:03

answered Jul 30 '18 at 09:50

Sinto

3,915
11
36
70

Thank you very much for your help. – D.Todd Jul 30 '18 at 10:54
The values returned for id, name, gender, and age in this query are indeterminate. They may be correct, but only by luck. The manual covers this in considerable detail. – Strawberry Jul 30 '18 at 11:10

score 1 · Accepted Answer · edited Jul 30 '18 at 11:43

1

select * 
  from student
 where (c_id, age,height) in ( Select c_id
                                    , max(age) age
                                    , max(height) as height 
                                 from student 
                                group 
                                   by c_id )

edited Jul 30 '18 at 11:43

Strawberry

33,750
13
40
57

answered Jul 30 '18 at 10:06

Zaynul Abadin Tuhin

31,407
5
33
63

Thank you very much for your help. – D.Todd Jul 30 '18 at 10:54
@D.Todd if it helps please keep upvote and accept answer for SO appreciation – Zaynul Abadin Tuhin Jul 30 '18 at 10:55
of course~~~~~~ – D.Todd Jul 30 '18 at 10:57
@Strawberry seriously ???? – Zaynul Abadin Tuhin Jul 30 '18 at 11:23
@Strawberry http://sqlfiddle.com/#!9/aaf88f/2 this link only for you buddy and you did again down vote i really love your down vote :P – Zaynul Abadin Tuhin Jul 30 '18 at 11:23
@Strawberry i considered his sample data not yours – Zaynul Abadin Tuhin Jul 30 '18 at 11:31
@Strawberry i don't know the OP's thought i see who's age greater his height also greater then why not i consider this can you explain that – Zaynul Abadin Tuhin Jul 30 '18 at 11:34
OK, but it seems to that this solution is still nonsensical. What does it mean to return both the max height and max age? Plus, there is no reference to age in the question. – Strawberry Jul 30 '18 at 11:44

score 0 · Answer 3 · answered Jul 30 '18 at 09:53

0

Try this

Select id, name, gender, age, c_id, height
From student
Where (c_id, age) in (Select c_id, max(age) from student group by c_id)
Order by height desc

answered Jul 30 '18 at 09:53

Gaj

888
5
5

Thank you very much for your help. – D.Todd Jul 30 '18 at 10:54
To the best of my recollection, a solution like this cannot use an index. – Strawberry Jul 30 '18 at 10:59
Also, of what relevance is the max(age) !?! – Strawberry Jul 30 '18 at 11:05
@Strawberry in won't use index ? – Gaj Jul 30 '18 at 11:15
@Strawberry hope you read the question fully. he want max age of student under each c_id with their details – Gaj Jul 30 '18 at 11:17
Actually, it will. I stand corrected, – Strawberry Jul 30 '18 at 11:19
I read the question. – Strawberry Jul 30 '18 at 11:20
There are 6 rows in the table but in the expected output 4 rows only. That's they want max age in each c_id. – Gaj Jul 30 '18 at 11:25
Actually, the OP hasn't provided a desired result. – Strawberry Jul 30 '18 at 11:47

trouble in Group by query

3 Answers3