0

I currently have some code that gets the overall top 25 frame sales, which is as follows:

Dim FrameCountSelectStr As String = "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

What I would also like to do is get the top 25 frame sales for each DISTINCT company and group the results by company (and order by count). I tried the following but I am not having any luck:

Dim FrameCountSelectStr As String = "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName FROM(Glasses) WHERE (SELECT DISTINCT Glasses.Manufacturer FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

Maybe I am just tired or getting confused, but any pointers in the right direction would be much appreciated.

3
Contributors
6
Replies
20
Views
4 Weeks
Discussion Span
Last Post by Reverend Jim
0

I was able to perform the task in 2 separate SELECT queries but it is pretty slow to load all of the data the way I came up with. I am pretty sure it can be combined into 1 quick efficient query but I am not able to get any combination working.

My first SELECT statement is: "SELECT DISTINCT Manufacturer FROM Glasses WHERE FrameName <> '' AND Manufacturer <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# AND Dispenser ='" & ComboBox1.Text & "' ORDER BY Manufacturer ASC"

I then iterate through the returned dataset rows: For Each row As DataRow In ManuDS.Tables(0).Rows

And throw this SELECT statement in there: "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName, Glasses.Colour, Glasses.ADim FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# AND Manufacturer = '" & row.Item("Manufacturer").ToString & "' AND Dispenser ='" & ComboBox1.Text & "' GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

Again, this gets the job done, but it does take some time to load all of the data as I am running this query for multiple Manufacturers.

Any help on getting this into 1 efficient query would be much appreciated.

0

Manufacturers is not indexed. I will definitely do that to improve performance. Thank you for all of your help.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.