0

I am running a SQL (mysql) query which results in a table like below and is not stored anywhere Now for each user based on count, I need to fetch the top 3 values. I tried to work with partitions but it fails as result is not in any table.

-----------------------------------------
pkid | username | genre     | count|
-----------------------------------------
1     user1      Horror         7
2     user1      Comedy         3
3     user1      Romantic       2
4     user1      Documentary    1
5     user2      Documentary    9
6     user2      Chik-Flick     5
7     user3      Romantic       12

I am trying to get output something like this

------------------------------------------------------------------------
pkid | username | genre1    | count1|   genre2| count2 | genre3 | count3
-------------------------------------------------------------------------
1     user1      Horror         7     Comedy      3      Romantic    2
2     user2      Documentary    9     Chik-Flik   5      Null       Null
3     user3      Romantic       12    Null        Null   Null       Null

I tried:

WITH temp1 (username,genre,Count1) AS 
    (<My query which gave result shared>) 
SELECT username,genre, Count1 
FROM ( 
    SELECT username,genre, Count1 
    Rank() over (Partition BY username ORDER BY Count1 DESC ) AS Rank 
    FROM temp1 
) WHERE temp1 Rank <= 3

Any Pointers?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Pratha P
  • 1
  • 1
  • Show what you tried to do with partitions. Putting them horizontally is a pivot. – Barmar Jun 10 '20 at 19:15
  • WITH temp1 (username,genre,Count1) AS () SELECT username,genre, Count1 FROM ( SELECT username,genre, Count1 Rank() over (Partition BY username ORDER BY Count1 DESC ) AS Rank FROM temp1 ) WHERE temp1 Rank <= 3 – Pratha P Jun 10 '20 at 19:19
  • You have to assign an alias to the subquery. – Barmar Jun 10 '20 at 19:21
  • I think you're missing a comma before the `RANK()` call. – Barmar Jun 10 '20 at 19:22
  • Added the comma, still facing the same issue. Is it because the result is not stored anywhere as a table? – Pratha P Jun 10 '20 at 19:27
  • Here is another question answered. This is not same as you expect in output, but this shows that you can use your genre name as column name and display count as value.https://stackoverflow.com/questions/12666796/mysql-groupby-and-shows-it-horizontally – Zedex7 Jun 10 '20 at 19:37

0 Answers0