1

I have a table like below, placed at SQL FIDDLE

id  empid proj  file  rating
~~  ~~~~~ ~~~~  ~~~~  ~~~~~~
1   1863   G1   file1    1
2   P4645  G1   file2    1
3   P6682  G1   file3    1
4   P6682  G1   file4    1
5   P4645  G1   file5    1
6   P4645  G1   file6    1
7   1863   G1   file7    1
8   1863   G1   file8    1
9   1863   G1   file19   1
10  P6682  G2   file21   1
11  1863   G2   file55   1
12  P6682  G2   file60   1
13  P4645  G2   file85   1
14  P6682  G2   file91   1
15  P4645  G2   file93   1
16  P4645  G3   file95   1
17  P4645  G3   file100  1
18  P4645  G3   file125  1
19  1863   G3   file131  1
20  1863   G3   file150  1
21  P6682  G3   file193  1
21  P6682  G3   file193  1

I need an SQL to retrieve top n (say top 2) ratings count from the above table for each category (proj)

Desired OP as below

proj  empid  ratings
~~~~  ~~~~~  ~~~~~~~
 G1   1863      4
 G1   P4645     3
 G2   P6682     3
 G2   P4645     2
 G3   P4645     3
 G3   1863      2
 G3   P6682     2

Tried to follow the meathod in this thread but could not achive from the table desing i have.

Community
  • 1
  • 1
Rao
  • 2,902
  • 14
  • 52
  • 70

2 Answers2

2

Try this query 

select * from 
(select @rn:=if(@prv=Proj, @rn+1, 1) as rId,
@prv:=Proj,
empid, 
stars
from
(SELECT
          starRatings.Proj
        , starRatings.empid
        , count(*) as stars
    FROM achivement as starRatings
    group by starRatings.Proj,starRatings.empid
order by proj, stars desc)a
join 
(select @rn:=0, @prv:='') tmp
)tmp where rid <= 2

SQL FIDDLE

select * from 
(select @rn:=if(@prv=Proj, if(@prvv=stars, @rn, @rn+1), 1) as rId,
@prv:=Proj,
empid, 
@prvv:=stars
from
(SELECT
          starRatings.Proj
        , starRatings.empid
        , count(*) as stars
    FROM achivement as starRatings
    group by starRatings.Proj,starRatings.empid
    order by proj, stars desc)t
join 
(select @rn:=0, @prv:='', @prvv:=0) tmp
)tmp where rid <= 2

SQL FIDDLE:

| RID | @PRV:=PROJ | EMPID | @PRVV:=STARS |
-------------------------------------------
|   1 |         G1 |  1863 |            4 |
|   2 |         G1 | P4645 |            3 |
|   1 |         G2 | P6682 |            3 |
|   2 |         G2 | P4645 |            2 |
|   1 |         G3 | P4645 |            3 |
|   2 |         G3 |  1863 |            2 |
|   2 |         G3 | P6682 |            2 |
Meherzad
  • 8,433
  • 1
  • 30
  • 40
  • when i add one more record for empid = P6682 and proj = G3 the result of top 2 in G@ will have 3 records but your SQL is showing firsr 2 even if 2ns 3rd are having 2 stars... check her `http://sqlfiddle.com/#!2/7420a/2` – Rao May 24 '13 at 12:15
  • Check the edit answer have updated the query as per your need... – Meherzad May 24 '13 at 12:27
  • There was mistake in the first query it was not returning on the basis of counts have updated it. – Meherzad May 24 '13 at 12:30
  • its working fine in workbench. But i will be executing this SQL from python. As you have used SQL variables `@rn,@prv,@prvv` which will give error in python. is there any other alternate like `joins,sub query's` – Rao May 27 '13 at 05:34
  • Why it will return error ?? Check how to escape such characters in string in python. i don't think it should return error. – Meherzad May 27 '13 at 05:36
  • @Meherzak K i will check and update – Rao May 27 '13 at 10:27
1

Use this:-

(
  select proj,empid,rating,count(rating)
  from achivement 
  where `proj` =  'G1'
  group by empid
  order by count(rating) desc
  LIMIT 2
)
UNION ALL
(
  select proj,empid,rating,count(rating)
  from achivement 
  where `proj` =  'G2'
  group by empid
  order by count(rating) desc
  LIMIT 2
)
UNION ALL
    (
      select proj,empid,rating,count(rating)
      from achivement 
      where `proj` =  'G3'
      group by empid
      order by count(rating) desc
      LIMIT 2
    )

SQL FIDDLE

Screenshot enter image description here

Vivek Sadh
  • 4,230
  • 3
  • 32
  • 49