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.

  • Preventing SQL Injection in ASP.Net
  • SQL pivot with values calculated previously rather than using aggregate function
  • Natural sort for SQL Server?
  • Getting active type within a date range that was logged in or out of that date range
  • Simultaneous connections to SQL Server using the same name and password. Is there any problem with that?
  • t-sql Summing differences between timestamps
  • 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.

    Quick sample:

    SELECT   Count(*) AS Products, ProductNumber, MAX(Date) AS maxdate
    FROM     Table
    GROUP BY ProductNumber
    ORDER BY maxdate; 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.