EaziLuizi EaziLuizi - 2 months ago 19
SQL Question

SQL GroupBy returning inconsistent (alternating) results

I have the following code, and after running it a few times a realized it's returning alternating results and I don't understand why:

DECLARE @M_ID CHAR(8) = '12345678';
DECLARE @C_ID TINYINT = 1;
DECLARE @StartDate CHAR(8) = '20160726000000';
DECLARE @EndDate CHAR(8) = '20160825235959';

DECLARE @ActivePresent FLOAT;
DECLARE @ActivePresent_DT CHAR(14);

SELECT
@ActivePresent = MAX(ActivePowerPresent),
@ActivePresent_DT = [DateTime]
FROM
EnergyDemand
WHERE
M_ID = @M_ID
AND C_ID = @C_ID
AND ([DateTime] BETWEEN @StartDate AND @EndDate)
GROUP BY
[DateTime]

SELECT @ActivePresent, @ActivePresent_DT


The result alternates between 2 values...

0.00 (20160824195408) and 210.04 (20160815085655)


What I am trying to do:

Get the highest value in the table and the corresponding datetime that value occurred, based on
M_ID = @M_ID
,
C_ID = @C_ID
and between the given date range,

*NOTE: I am storing datetime values as char(14) because they have to be in indexes...

Perhaps my heading is wrong, because it's consistently "inconsistent", hmmm...

Even an alternative way to retrieve the data I require would be much appreciated. But an explanation why, for the win... I would have thought the
WHERE
clause runs before the
GROUP BY
since that's it's order of execution and therefore it's using the exact same result set?

Thanks in advance!

Answer

You can simply use TOP 1 with an ORDER BY clause:

SELECT TOP 1 @ActivePresent = ActivePowerPresent, @ActivePresent_DT = [DateTime]
FROM EnergyDemand
WHERE M_ID = @M_ID AND C_ID = @C_ID AND 
      ([DateTime] BETWEEN @StartDate AND @EndDate)
ORDER BY ActivePowerPresent DESC

Note: You should store datetime values in their native format and not as CHAR(14). SQL Server can index datetime fields, just like any other RDBMS that I know of.