Top 5 records per group

[rmarie127]

Ok, I am trying to create a query that will bring me back the top 5 Shrink %
Retails for each store in a table. I've been tooling around with this but I
can seem to get it to work correctly. Here's the base query:

SELECT [Weekly Detail].Area, [Weekly Detail].Region, [Weekly
Detail].District, [Weekly Detail].store_nbr, [Weekly Detail].Inv_Date,
[Weekly Detail].Prior_Inv_Date, [Weekly Detail].Type, [Weekly Detail].[FS
Shrink %], [Weekly Detail].[FS Prior %], [Weekly Detail].[FS Shrink%
Erosion], CatShrinkStatCode9.[Cat Number], category.cat_dsc,
CatShrinkStatCode9.[Shrink % Retail], CatShrinkStatCode9.[Shrink % Cost]
FROM ([Weekly Detail] INNER JOIN CatShrinkStatCode9 ON [Weekly
Detail].store_nbr = CatShrinkStatCode9.Store) LEFT JOIN category ON
CatShrinkStatCode9.[Cat Number] = category.cat_nbr
GROUP BY [Weekly Detail].Area, [Weekly Detail].Region, [Weekly
Detail].District, [Weekly Detail].store_nbr, [Weekly Detail].Inv_Date,
[Weekly Detail].Prior_Inv_Date, [Weekly Detail].Type, [Weekly Detail].[FS
Shrink %], [Weekly Detail].[FS Prior %], [Weekly Detail].[FS Shrink%
Erosion], CatShrinkStatCode9.[Cat Number], category.cat_dsc,
CatShrinkStatCode9.[Shrink % Retail], CatShrinkStatCode9.[Shrink % Cost],
CatShrinkStatCode9.[Shrink % Retail]
ORDER BY [Weekly Detail].store_nbr, CatShrinkStatCode9.[Shrink % Retail];

What I'd like it to look like is this:

Store Cat Shrink % Retail
1234 1 5
1234 2 4
1234 3 3
1234 4 2
1234 5 1
2345 1 8
2345 2 6
2345 3 4
2345 4 3
2345 5 1

etc., etc.

 

[Michael Gramelspacher]

Ok, I am trying to create a query that will bring me back the top 5 Shrink %
Retails for each store in a table. I've been tooling around with this but I
can seem to get it to work correctly. Here's the base query:

SELECT [Weekly Detail].Area, [Weekly Detail].Region, [Weekly
Detail].District, [Weekly Detail].store_nbr, [Weekly Detail].Inv_Date,
[Weekly Detail].Prior_Inv_Date, [Weekly Detail].Type, [Weekly Detail].[FS
Shrink %], [Weekly Detail].[FS Prior %], [Weekly Detail].[FS Shrink%
Erosion], CatShrinkStatCode9.[Cat Number], category.cat_dsc,
CatShrinkStatCode9.[Shrink % Retail], CatShrinkStatCode9.[Shrink % Cost]
FROM ([Weekly Detail] INNER JOIN CatShrinkStatCode9 ON [Weekly
Detail].store_nbr = CatShrinkStatCode9.Store) LEFT JOIN category ON
CatShrinkStatCode9.[Cat Number] = category.cat_nbr
GROUP BY [Weekly Detail].Area, [Weekly Detail].Region, [Weekly
Detail].District, [Weekly Detail].store_nbr, [Weekly Detail].Inv_Date,
[Weekly Detail].Prior_Inv_Date, [Weekly Detail].Type, [Weekly Detail].[FS
Shrink %], [Weekly Detail].[FS Prior %], [Weekly Detail].[FS Shrink%
Erosion], CatShrinkStatCode9.[Cat Number], category.cat_dsc,
CatShrinkStatCode9.[Shrink % Retail], CatShrinkStatCode9.[Shrink % Cost],
CatShrinkStatCode9.[Shrink % Retail]
ORDER BY [Weekly Detail].store_nbr, CatShrinkStatCode9.[Shrink % Retail];

What I'd like it to look like is this:

Store Cat Shrink % Retail
1234 1 5
1234 2 4
1234 3 3
1234 4 2
1234 5 1
2345 1 8
2345 2 6
2345 3 4
2345 4 3
2345 5 1

etc., etc.

maybe something like this, but I must say your column names
seem strange. There does not seem to be a consistent system.

SELECT b.store_nbr AS Store,
COUNT(* ) AS Cat,
b.[Shrink% Retail]
FROM BaseQuery AS b
INNER JOIN BaseQuery AS b1
ON ((b.[Shrink% Retail] < b1.[Shrink% Retail])
OR (b.[Shrink% Retail] = b1.[Shrink% Retail]
AND b.Inv_Date <= b1.Inv_date))
AND (b.store_nbr = b1.store_nbr)
GROUP BY b.store_nbr,b.[Shrink% Retail], b.Inv_Date
HAVING 5 >= COUNT(* )
ORDER BY b.store_nbr,
COUNT(* );

 

[Allen Browne]

To get the top 5 for each store, use a subquery.

Example in:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[rmarie127]

Thanks! I did get this to work...your are right...the column headers are
strange...this is a table thrown together from two separate spreadsheets.

Ok, I am trying to create a query that will bring me back the top 5 Shrink %
Retails for each store in a table. I've been tooling around with this but I
can seem to get it to work correctly. Here's the base query:

SELECT [Weekly Detail].Area, [Weekly Detail].Region, [Weekly
Detail].District, [Weekly Detail].store_nbr, [Weekly Detail].Inv_Date,
[Weekly Detail].Prior_Inv_Date, [Weekly Detail].Type, [Weekly Detail].[FS
Shrink %], [Weekly Detail].[FS Prior %], [Weekly Detail].[FS Shrink%
Erosion], CatShrinkStatCode9.[Cat Number], category.cat_dsc,
CatShrinkStatCode9.[Shrink % Retail], CatShrinkStatCode9.[Shrink % Cost]
FROM ([Weekly Detail] INNER JOIN CatShrinkStatCode9 ON [Weekly
Detail].store_nbr = CatShrinkStatCode9.Store) LEFT JOIN category ON
CatShrinkStatCode9.[Cat Number] = category.cat_nbr
GROUP BY [Weekly Detail].Area, [Weekly Detail].Region, [Weekly
Detail].District, [Weekly Detail].store_nbr, [Weekly Detail].Inv_Date,
[Weekly Detail].Prior_Inv_Date, [Weekly Detail].Type, [Weekly Detail].[FS
Shrink %], [Weekly Detail].[FS Prior %], [Weekly Detail].[FS Shrink%
Erosion], CatShrinkStatCode9.[Cat Number], category.cat_dsc,
CatShrinkStatCode9.[Shrink % Retail], CatShrinkStatCode9.[Shrink % Cost],
CatShrinkStatCode9.[Shrink % Retail]
ORDER BY [Weekly Detail].store_nbr, CatShrinkStatCode9.[Shrink % Retail];

What I'd like it to look like is this:

Store Cat Shrink % Retail
1234 1 5
1234 2 4
1234 3 3
1234 4 2
1234 5 1
2345 1 8
2345 2 6
2345 3 4
2345 4 3
2345 5 1

etc., etc.

maybe something like this, but I must say your column names
seem strange. There does not seem to be a consistent system.

SELECT b.store_nbr AS Store,
COUNT(* ) AS Cat,
b.[Shrink% Retail]
FROM BaseQuery AS b
INNER JOIN BaseQuery AS b1
ON ((b.[Shrink% Retail] < b1.[Shrink% Retail])
OR (b.[Shrink% Retail] = b1.[Shrink% Retail]
AND b.Inv_Date <= b1.Inv_date))
AND (b.store_nbr = b1.store_nbr)
GROUP BY b.store_nbr,b.[Shrink% Retail], b.Inv_Date
HAVING 5 >= COUNT(* )
ORDER BY b.store_nbr,
COUNT(* );