Member Avatar for upload12300

i have one table,
which has three fields and data.
Name , Top , total
cat 1 10
dog 2 7
cat 3 20
hourse 4 4
cat 5 10
Dog 6 9

i want to select record which has highest value of "total" for each Name

so my result should be like this.

Name , Top , total
cat 3 20
hourse 4 4
Dog 6 9

i tried group by name order by total, but it give top most record of group by result.

any one can guide me , please!!!!

  • 2 Contributors
  • 1 Reply
  • 60 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by genevish
Member Avatar for genevish

i have one table,
which has three fields and data.
Name , Top , total
cat 1 10
dog 2 7
cat 3 20
hourse 4 4
cat 5 10
Dog 6 9

i want to select record which has highest value of "total" for each Name

so my result should be like this.

Name , Top , total
cat 3 20
hourse 4 4
Dog 6 9

i tried group by name order by total, but it give top most record of group by result.

any one can guide me , please!!!!

select tsh.name, tsh.top, tsh.total from tmp_sql_help tsh, (
select name, MAX(`total`) AS total from tmp_sql_help group by name) maxnum
WHERE tsh.total = maxnum.total AND tsh.name = maxnum.name
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.