Would like to get top 25 frame sales for each distinct company in database

[mikeybware]

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.

[rproffitt]

I don't see the problem but did you try the visual studio query builders?
One is built into VS. Read https://technet.microsoft.com/en-us/library/ms141087(v=sql.105).aspx

[mikeybware]

I will give that article a read and see if I can come up with a solution.

[mikeybware]

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.

[rproffitt]

As to speed, are key fields indexed? https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html for example notes how the index matters for WHERE statements.

Is Manufacturers indexed?

[mikeybware]

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

[Reverend Jim]

You should consider using parameterized queries. Not only are they more secure, they are much easier to read and debug.