CODEWITHSUNDEEP
mysqlsql
vvvvvvvvvv
vvvvvvvvvv

Reputation: 53

Group by and desc duplicate name

I need some help with Group by issue. I am working a mysql to get the last row(red color row) of the table.

This is how the example data look like:

enter image description here

The query that I have tried:

Select *,min(remain),max(Sequance) 
from ApplePen
Group by Name
Order by max(Sequance);

Then the result that I get is:

enter image description here

The results that I want is:

( to be shown by op here. Right now we don't know. Maybe the arrows above )

Upvotes: 0

Views: 50

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a tuple and subselect 

  select * from ApplePen
  where (remain, sequance) in  (select min(remain), max(sequance) from ApplePen)

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Not quite sure because of colors you are using in the sample output, but I believe this is what you may need:

select
  a.max_id,
  a.name,
  b.use,
  a.max_remain,
  a.min_sequance,
  a.max_remain,
  a.max_sequance
from (
  select 
      name
    , max(id) as max_id
    , min(remain) as min_remain
    , max(sequance) as max_sequance
    , max(remain) as max_remain
    , min(sequance) as min_sequance
  from applepen
  group by name
  ) a
left join applepen b on 
  a.name = b.name and a.id = b.id

Calculating MIN and MAX column values for every name and then returning the max(id) row use column.

Upvotes: 1

Related Questions