Group By (Name) and Order By (Date) for two select query
I got a sort of large query which contains a inner select query. My objective is to group all the records by field call Product Number (which is not unique and appears multiple times) and then by the Date.
I added the Order By date at the end of the query and seems to work fine. The query generated all the records in order of the Date but the records are not grouped by the ProductNumber.
Here is a visual sample of the data
ProductId Manufacturer ProductNumber Date 1 Kellog H1 05/05/2017 5 Nestle H1 05/01/2017 8 Nutmeg H1 04/30/2017 9 Highland H3 04/09/2017 10 Houston H1 04/15/2017 11 Miami H3 04/12/2017
I am trying to get the output to look like the following
ProductId Manufacturer ProductNumber Date 1 Kellog H1 05/05/2017 5 Nestle H1 05/01/2017 8 Nutmeg H1 04/30/2017 10 Houston H1 04/15/2017 9 Highland H3 04/09/2017 11 Miami H3 04/12/2017
So, how would I group by a column field (ProductNumber) with group by and then order by Date?
In the query, I added the Order by Date. When I added the group by ProductNumber, the error message is asking me add all the fields in the inner select to the group by. Do I really need to all all the fields(there are lot of them in the real query) in the select to my group by? There must be a better solution.
Here is my query (just a sample) so far
SELECT DISTINCT TOP 100 *, FROM (SELECT DISTINCT NP.ManufacuturerId, C.ManufacuturerName, IR2.ProductNumber, NP.Field1, NP.Field2, NP.Field3, NP.Field4, CN.C2Date CN.CompanyNotificationId, NPR.NotificationProcessName, Cast(NP.CompanyId AS VARCHAR(15)) + NPR.NotificationProcessName AS 'CompanyIdAndProcess', CC.UserId, NT.NotificationTypeName, CC.EmailAddress, IsRegistered = CASE WHEN C.IsRegistered = 1 THEN 'Yes' ELSE 'No' END, FROM ProductDetails NP INNER JOIN Manufactuers C ON C.ManufacuturerId = NP.ManufacturerId) Q ORDER BY CN.C2DateDESC;
2 Solutions collect form web for “Group By (Name) and Order By (Date) for two select query”
What you are calling “grouping” is really ordering.
Order by ProductNumber, Date.
I think your problem is that you are trying to order by a field that is included inside the grouped data, and is not always the same, example:
1 Kellog H1 05/05/2017 5 Nestle H1 05/01/2017
If you group by
ProductNumber and after the grouping you want to sort by date, is very likely that your database engine will not know by which value to order by (May 5th or May 1st?). So what you will need to do is to specify which value to use when grouping, for example with a MAX or MIN function.
SELECT Count(*) AS Products, ProductNumber, MAX(Date) AS maxdate FROM Table GROUP BY ProductNumber ORDER BY maxdate;