title: Most frequent value + number of time displayed in columns for each
body: <p>I've got the following order table:</p>
  
  <blockquote>
    <p>Client_name|Product <br> Megane|#768_Samsung<br> Megane|#310_Apple<br>
    Megane|#659_Samsung<br> Victor|#890_Apple<br></p>
  </blockquote>
  
  <p>I'd like to see for each client what's their most bought brand + the number of time they bought it.
  So for this table, I'd like this result:<br></p>
  
  <blockquote>
    <p>Client_Name|Favourite_brand|Order_number<br> Megan|Samsung|2<br>
    Victor|Apple|1<br></p>
  </blockquote>
  
  <p>So far this is the query I've built:</p>
  
  <pre><code>SELECT Client_name, brand 
    FROM (SELECT Client_name, brand, ROW_NUMBER() OVER (PARTITION BY Client_name ORDER BY freq DESC) AS rn
            FROM (  SELECT Client_name, brand, COUNT('x') AS freq
                    FROM (SELECT Client_name, substring(Product,5) as brand
                          FROM Orders
                  GROUP BY Client_name, brand) frequency) ranked) client
   WHERE rn = 1;
  </code></pre>
  
  <p>I am still struggling with removing the number before the brand name (ie #768 for instance) by using substring. I shows me an error message</p>
  
  <blockquote>
    <p>-ERROR:  column "frequency.Client_name" must appear in the GROUP BY clause or be used in an aggregate function</p>
  </blockquote>
  
  <p>So I haven't started yet to think how to add the Order_number column</p>
  
  <p>Your help is highly appreciated!</p>