Ranking Products based on Location and NET Quantity

• rjjh78

  SSCrazy

  Points: 2784

  More actions

  April 2, 2018 at 4:06 pm

  #344526

  Hello,

  I am looking to create a query so that we can identify top 3 selling parts by Store  based on the Net Quantity sold.  Below I have created sample data, can someone help me understand how I can efficiently query this for multiple Stores?

  For example, I would like to return data that looks something like this

  StoreNo, Class, Rank, Qty
  135, Boots, 1, 14
  135, Hat, 2, 10
  135, Scarf, 3, 9
  136, Boots, 1, 11
  136, Hat, 2, 10
  136, Socks, 3, 4
  138,Socks,1,12
  138, Glove,2,3
  138, Hat,3,1

  


  --==========================================================================================

  -- 1. Create Table Table -- DROP TABLE #products

  --==========================================================================================

  CREATE TABLE #Products (Class varchar(50), ItemNo int, Qty int, StoreNo int, Region int)

  --==========================================================================================

  -- 2. Insert Test Data

  --==========================================================================================

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1001, 2, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1002, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1003, 9, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1004, 3, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1002, 6, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1003, -4, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1004, -2, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1005, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1001, 7, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1002, 12, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1003, 11, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1004, -3, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1005, 8, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2001, 2, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2002, -1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2003, 1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2004, -3, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2002, 6, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2003, -4, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2005, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2002, 6, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2003, 1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2004, -3, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2005, 4, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3001, 2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3002, -1, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3003, 1, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3004, -3, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3002, 6, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3003, -4, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3005, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3002, 6, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3003, 1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3004, -3, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3005, 4, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4001, 2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4002, -1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4003, 1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, -3, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4002, 6, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4003, -4, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4005, -1, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4001, -7, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4002, 6, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, 10, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4005, 14, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5001, 21, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5002, -1, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5003, 11, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, -3, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5002, 16, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5003, 8, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5005, 9, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5001, 7, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5002, 6, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, -1, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, 10, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5005, 1, 132, 1)

  --==========================================================================================

  -- 3. Get Data Results

  --==========================================================================================

  SELECT Class, ItemNo, Qty, StoreNo, Region FROM #Products

• Phil Parkin

  SSC Guru

  Points: 246716

  More actions

  April 2, 2018 at 4:38 pm

  #1985226

  rjjh78 - Monday, April 2, 2018 4:06 PM

  Hello,

  I am looking to create a query so that we can identify top 3 selling parts by Store  based on the Net Quantity sold.  Below I have created sample data, can someone help me understand how I can efficiently query this for multiple Stores?

  For example, I would like to return data that looks something like this

  StoreNo, Class, Rank, Qty
  135, Boots, 1, 14
  135, Hat, 2, 10
  135, Scarf, 3, 9
  136, Boots, 1, 11
  136, Hat, 2, 10
  136, Socks, 3, 4
  138,Socks,1,12
  138, Glove,2,3
  138, Hat,3,1

  


  --==========================================================================================

  -- 1. Create Table Table -- DROP TABLE #products

  --==========================================================================================

  CREATE TABLE #Products (Class varchar(50), ItemNo int, Qty int, StoreNo int, Region int)

  --==========================================================================================

  -- 2. Insert Test Data

  --==========================================================================================

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1001, 2, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1002, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1003, 9, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1004, 3, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1002, 6, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1003, -4, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1004, -2, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1005, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1001, 7, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1002, 12, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1003, 11, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1004, -3, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Hat', 1005, 8, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2001, 2, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2002, -1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2003, 1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2004, -3, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2002, 6, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2003, -4, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2005, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2002, 6, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2003, 1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2004, -3, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Glove', 2005, 4, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3001, 2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3002, -1, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3003, 1, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3004, -3, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3002, 6, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3003, -4, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3005, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3002, 6, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3003, 1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3004, -3, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Socks', 3005, 4, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4001, 2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4002, -1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4003, 1, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, -3, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4002, 6, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4003, -4, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4005, -1, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4001, -7, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4002, 6, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, -1, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4004, 10, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Scarf', 4005, 14, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5001, 21, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5002, -1, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5003, 11, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, -3, 133, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5005, 8, 134, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5001, 7, 135, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5002, 16, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5003, 8, 137, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, -2, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5005, 9, 136, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5001, 7, 131, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5002, 6, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, -1, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5004, 10, 132, 1)

  INSERT INTO #Products (Class, ItemNo, Qty, StoreNo, Region) VALUES ('Boots', 5005, 1, 132, 1)

  --==========================================================================================

  -- 3. Get Data Results

  --==========================================================================================

  SELECT Class, ItemNo, Qty, StoreNo, Region FROM #Products

  Either your desired results are almost unrelated to your test data, which is unhelpful, or I am completely clueless as to your logic. Store 138, for example, does not even appear in your test data.
  Here is a solution, based on what I think you are asking for.
  WITH summed
AS (SELECT p.StoreNo,
    p.Class,
    Qty = SUM(p.Qty)
  FROM #Products p
  GROUP BY p.StoreNo,
     p.Class),
  Ranked
AS (SELECT *,
    Rnk = ROW_NUMBER() OVER (PARTITION BY summed.StoreNo ORDER BY summed.Qty DESC)
  FROM summed)
SELECT *
FROM Ranked
WHERE Ranked.Rnk < 4
ORDER BY Ranked.StoreNo,
   Ranked.Rnk DESC;

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• rjjh78

  SSCrazy

  Points: 2784

  More actions

  April 4, 2018 at 4:29 pm

  #1985571

  Thank you kindly this does solve the problem!  Is it possible to insert CTE  results into a table?

• pietlinden

  SSC Guru

  Points: 63435

  More actions

  April 4, 2018 at 6:37 pm

  #1985575

  You'd just wrap this part in the INSERT statement:
  SELECT *
  FROM Ranked
  WHERE Ranked.Rnk < 4
  ORDER BY Ranked.StoreNo,
   Ranked.Rnk DESC

  (well, you'd have to expand the *...)
  INSERT INTO MyTable (Field1, Field2....)
  SELECT <field list>
  FROM Ranked....