title: Finding the 3 top selling products in each category
body: <p>I am trying to find the 3 top selling products in each category. But the SQL I tried does not yield the correct results. Here's the things I did:</p>
  <p><strong>Products Table:</strong></p>
  <pre><code>CREATE TABLE [dbo].[Products](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [name] [nvarchar](100) NOT NULL,
      [category] [nvarchar](100) NOT NULL,
   CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
  (
      [id] ASC
  )
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>category</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>prod1</td>
  <td>cat1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>prod2</td>
  <td>cat1</td>
  </tr>
  <tr>
  <td>3</td>
  <td>prod3</td>
  <td>cat1</td>
  </tr>
  <tr>
  <td>4</td>
  <td>prod4</td>
  <td>cat1</td>
  </tr>
  <tr>
  <td>5</td>
  <td>prod5</td>
  <td>cat1</td>
  </tr>
  <tr>
  <td>6</td>
  <td>prod6</td>
  <td>cat2</td>
  </tr>
  <tr>
  <td>7</td>
  <td>prod7</td>
  <td>cat2</td>
  </tr>
  <tr>
  <td>8</td>
  <td>prod8</td>
  <td>cat2</td>
  </tr>
  <tr>
  <td>10</td>
  <td>prod10</td>
  <td>cat2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>OrderItems table:</strong></p>
  <pre><code>CREATE TABLE [dbo].[OrderItems](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [prodID] [int] NOT NULL,
      [quantity] [int] NOT NULL,
   CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED 
  (
      [id] ASC
  )
  
  ALTER TABLE [dbo].[OrderItems]  WITH CHECK ADD  CONSTRAINT [FK_OrderItems_Products] FOREIGN KEY([prodID])
  REFERENCES [dbo].[Products] ([id])
  
  ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_Products]
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>prodID</th>
  <th>quantity</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>6</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>6</td>
  </tr>
  <tr>
  <td>4</td>
  <td>4</td>
  <td>3</td>
  </tr>
  <tr>
  <td>5</td>
  <td>3</td>
  <td>2</td>
  </tr>
  <tr>
  <td>6</td>
  <td>6</td>
  <td>10</td>
  </tr>
  <tr>
  <td>7</td>
  <td>7</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>SQL I tried:</strong></p>
  <pre><code>select *
  from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
  from Products as p1) as p2
  where p2.sales &gt;= (
      select distinct(sales)
      from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
  from Products as p1) as p3
      where p2.category = p3.category
      order by sales desc
      offset 2 rows fetch next 1 rows only)
  </code></pre>
  <p><strong>Expected Result:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>category</th>
  <th>sales</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>cat1</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2</td>
  <td>cat1</td>
  <td>12</td>
  </tr>
  <tr>
  <td>4</td>
  <td>cat1</td>
  <td>3</td>
  </tr>
  <tr>
  <td>6</td>
  <td>cat2</td>
  <td>10</td>
  </tr>
  <tr>
  <td>7</td>
  <td>cat2</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Actual Result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>category</th>
  <th>sales</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>cat1</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2</td>
  <td>cat1</td>
  <td>12</td>
  </tr>
  <tr>
  <td>4</td>
  <td>cat1</td>
  <td>3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What is the mistake in the query? And is there a better way to achieve the result?</p>