I have a tables like this
Table 1
Date ID Category Product
24-July-2018 1 A Product1
24-July-2018 2 A Product2
25-July-2018 2 A Product2
24-July-2018 3 B Product3
24-July-2018 4 B Product3
25-July-2018 5 C Product2
24-July-2018 1 D Product1
24-July-2018 2 D Product2
25-July-2018 2 D Product2
24-July-2018 3 E Product3
24-July-2018 4 E Product3
25-July-2018 5 E Product2
24-July-2018 1 F Product1
24-July-2018 2 F Product2
25-July-2018 2 G Product2
24-July-2018 3 H Product3
24-July-2018 4 I Product3
25-July-2018 5 J Product2
I want to display top products for each category for top 6 categories by product count for that date(The date column is in filter). For example, if the user selects 24-July-2018 from filter, the results should be
SubTable1 (B)
Id Product Count
1 Product3 2
SubTable2 (D)
Id Product Count
1 Product1 1
2 Product3 1
SubTable3 (E)
Id Product Count
1 Product3 2
Subtable4 (F)
Id Product Count
1 Product1 1
2 Product2 1
Subtable5 (H)
Id Product Count
1 Product3 1
Subtable6 (A)
Id Product Count
1 Product2 1
and if the filter is 25-July-2018, the results should be -
SubTable1 (A)
Id Product Count
1 Product2 1
SubTable2 (C)
Id Product Count
1 Product1 1
SubTable3 (D)
Id Product Count
1 Product2 1
Subtable4 (E)
Id Product Count
1 Product1 1
Subtable5 (G)
Id Product Count
1 Product2 1
Subtable6 (J)
Id Product Count
1 Product1 1
