Query to get results with the grouping

Question

I have a simple table Customers with fields id, name, rating, seller_id. The result of query

select seller_id, MAX(rating) 
from Customer 
group by seller_id

is the customers grouped by seller_id with max values of the rating. Like this:

seller_id  rating
2          17
3          20
4          17

How to modify this query to get customer's id additionally?
If I add id to SELECT I get an error, that I should add id to grouping. But I want only get above shown values with id specified. Does anyone know how to do this? Thanks.

please post some sample Input and output data – mohan111 Feb 11 '16 at 06:04 — mohan111, Feb 11 '16 at 06:04
Tag properly. MySQL and tsql don't go well together... – jarlh Feb 11 '16 at 07:34 — jarlh, Feb 11 '16 at 07:34
Why do you have a seller_id column in the customers table? – jarlh Feb 11 '16 at 07:35 — jarlh, Feb 11 '16 at 07:35
it is a foreign key for the customers table – user3523923 Feb 11 '16 at 08:38 — user3523923, Feb 11 '16 at 08:38
to @jarlh . Thanks. Fixed) – user3523923 Feb 11 '16 at 08:44 — user3523923, Feb 11 '16 at 08:44

score 0 · Answer 1 · answered Feb 11 '16 at 06:08

Try this

select c1.* from Customer c1 inner join 
(select seller_id, MAX(rating) as rating from Customer 
group by seller_id) c2 on c1.rating  = c2.rating

or

 SELECT c1.* FROM Customer b1 LEFT JOIN Customer c2
 ON (b1.seller_id = b2.seller_id  AND b1.rating > b2.br_created_date)
 WHERE b2.rating is null;

score 0 · Accepted Answer · edited Feb 11 '16 at 07:11

0

Try:

select t.id, t.seller_id, t.rating
from Customer c
join (
    select seller_id, MAX(rating) rating
    from Customer 
    group by seller_id
) x
on x.seller_id = c.seller_id
and x.rating = c.rating

edited Feb 11 '16 at 07:11

cybertextron

10,547
28
104
208

answered Feb 11 '16 at 06:11

Praveen

8,945
4
31
49

score 0 · Answer 3 · answered Feb 11 '16 at 06:11

SimarjeetSingh has a good answer. Just tweak a little more:

select c.customer_id, c.seller_id
from customer c
join (
  select seller_id, max(rating) max_rating
  from customer
  group by seller_id
) s 
  on c.seller_id = s.seller_id
  and c.rating = s.max_rating

This way you are comparing the seller_id and the maximum rating.

score 0 · Answer 4 · edited May 23 '17 at 12:31

A dataset is divided into groups using the GROUP BY clause. The grouping attribute is common key shared by members of each group. The grouping attribute is usually a single column but may be multiple columns or an expression that cannot be based on group functions. Note that only grouping attributes and group functions are permitted in the SELECT clause when using GROUP BY.

refer this Select EMP with max SAL from each DEPT

Query to get results with the grouping

4 Answers4