SQL Server : Group by breaks my program

Question

I have the following query. The idea is to inner join the records and group them in order to get one record (the latest one) from each group.

• If I add the GROUP BY (like on the example bellow) it doesn't work.
• If I remove the GROUP BY the query works but display duplicated data.
• If I group by all fields that I selected before the inner join, it works but not as intended. It will display all records.

Any suggestions?

 SELECT 
    Calibrations.Cert_No, 
    Calibrations.Cust_Ref, 
    Calibrations.Rec_Date, 
    Instruments.Inst_ID, 
    Instruments.Description, 
    Instruments.Model_no, 
    Instruments.Manufacturer, 
    Instruments.Serial_no, 
    Instruments.Status, 
    Instruments.Cust_Acc_No  
 FROM 
    Instruments 
 INNER JOIN 
    Calibrations ON Instruments.Inst_ID = Calibrations.Inst_ID  
 WHERE 
    Instruments.Cust_Name = '" & Session("MM_Username") & "' 
    AND Instruments.Cust_Acc_No = '" & Session("MM_Password") & "' 
    AND Instruments.Cust_Acc_No = '" & Replace(rsDue__MMColParam, "'", "''") & "' 
    AND Instruments.Status IN ('N')
 GROUP BY
    Instruments.Inst_ID  
 ORDER BY 
    Calibrations.Rec_Date DESC

Answer 1

You cannot have columns in the SELECT part of your query, that does not appear in the GROUP BY part of the query, unless they are inside an aggregate function such as MIN(), MAX(), SUM(), etc...

Think about it this way: Say you have a table that looks like this:

+----------+------+--------+
|   Col1   | Col2 | NumCol |
+----------+------+--------+
| Value 1a | ABC  |    123 |
| Value 1a | DEF  |    234 |
| Value 1b | GHI  |    345 |
| Value 1b | JKL  |    456 |
+----------+------+--------+

This query would not work:

SELECT Col1, Col2, NumCol FROM Table
GROUP BY Col1 ORDER BY NumCol

Why? Because you are only grouping by Col1, and since this column only contains two distinct values, the query engine doesn't know which of the values it should display in the Col2 or NumCol columns (since these contain 4 distinct values).

To fix this, you should either remove the columns from your SELECT statement like this:

SELECT Col1 FROM Table
GROUP BY Col1

...or aggregate the columns somehow. For example like this:

SELECT Col1, MAX(Col2) AS Col2, SUM(NumCol) AS NumCol FROM Table
GROUP BY Col1 ORDER BY NumCol

However, this is not the same as getting the "latest record", or for example the record with the largest NumCol for each distinct value of Col1. To do that, you should consider using the ROW_NUMBER() windowed function like this:

SELECT Col1, Col2, NumCol FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY NumCol DESC) AS N
    FROM Table
) AS T
WHERE T.N = 1

How this works is a topic of its own, but basically, ROW_NUMBER assigns a running value to each row, resetting the value each time it encounters a new value in Col1. The ordering makes sure that the running value starts with 1 for the record that has the largest NumCol value. In the outer select statement, you then apply a filter on this running value, to get only the first record for each distinct Col1 value - that is the record with the largets NumCol value.

Answer 2

When you are grouping in a SQL query, you have to either list the column in the group by clause or use an aggregate function -> There can not be columns without aggregation since they are not in the group by list.

You did not provided any information about your specific goal, but either you can get the values by aggregating (using MIN, MAX, AVG, etc) functions to get the desired data, or you can use subqueries to retrieve the distinct list than another one to retrieve their specific data, or you can use analytic functions (FIRST_VALUE, LAST_VALUE, etc) and distinct.