0

TABLE => users

 id   |  category | views
  1   |  cat2     |  700
  2   |  cat1     | 3900   <== most views in cat1
  3   |  cat1     |  200
  4   |  cat1     |  800
  5   |  cat3     | 2900   <== most view from cat3
  6   |  cat1     |  800
  7   |  cat2     | 4900   <== most views from cat2
  8   |  cat1     |  900
  9   |  cat2     |  100
 10   |  cat2     |  100


// this is random table..//

my output :

 id   |  category | views
  2   |  cat1     |  3900   <== most views in cat1
  5   |  cat3     |  2900   <== most view from cat3
  7   |  cat2     |  4900   <== most views from cat2

my desired output :

 id   |  category | views
  7   |  cat2     |  4900   <== most views from cat2
  6   |  cat2     | 1000
  10   |  cat2    | 4000
  8   |  cat2     |  500

  5   |  cat3     |  2900   <== most view from cat3
  11   |  cat3     | 1000
  13   |  cat3     |  2500
  15   |  cat3     | 100
  14   |  cat3     |  500

i want to display rows from that category where id of max views is greater than 4 and if not then do not display that row in result,thereafter category which fullfills the above condition of id > 4 then display rows of that category in order of category wise but category whose max views is higher should show first and accordinly other

my sql code :

SELECT * 
FROM neww 
where category in (
 SELECT t1.category
 FROM neww t1 inner join (
   SELECT category,MAX(views) AS  max_view
   FROM neww 
   GROUP BY category
 ) t2 on t2.max_view = t1.views and  t1.id >= 4
) and id >= 4
order by category desc, views desc
Tarusjo
  • 13
  • 6
rock
  • 1
  • 1
  • let me edit it prooerly – rock Jul 09 '18 at 14:29
  • "let me edit it prooerly " While we wait you should also add in table structure(s), example data place it on sqlfiddle.com or db-fiddle.com and a ascii data table (text formatted) that matches the example data. – Raymond Nijland Jul 09 '18 at 14:32
  • 2
    @RaymondNijland I was going to answer, but I've written that query literally hundreds of times here, and it's more helpful to actually link to a good duplicate. – Tim Biegeleisen Jul 09 '18 at 14:34
  • indeed @TimBiegeleisen i was also searching for a good duplicate.. Your duplicate link looks like it contains the most common queries for this problem.. if i was the topicstarter i would stay very very very far away from `SELECT *, MAX(age) FROM mytable GROUP BY `Group`;` example which is in the answers somewhere because it a invalid GROUP BY query. – Raymond Nijland Jul 09 '18 at 14:35

0 Answers0