4

If I do this --

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM 
    [Devices] as dv
    INNER JOIN 
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name

I get this error --

Msg 207, Level 16, State 1, Line 17 Invalid column name 'DE'.

If I do this --

SELECT Name, DE FROM (
    SELECT dv.Name
          ,MAX(hb.[DateEntered]) as DE
    FROM 
        [Devices] as dv
        INNER JOIN 
        [Heartbeats] as hb ON hb.DeviceID = dv.ID
    GROUP BY dv.Name
) as tmp WHERE tmp.DE < '2013-03-04'

it works as expected.

Can someone explain why I need to nest my main query as a subquery to limit my data set?

Also, is there maybe a better way to achieve the goal here? Retrieve all records from one table, and the single "top" related record ordered by [DateEntered] descending?

Nate
  • 1,727
  • 4
  • 22
  • 33

2 Answers2

6

You can't reference an alias in the WHERE clause - this is just because of the order in which SQL Server parses the statement.

There have been many discussions about this here and on StackOverflow. A couple of examples that give some background:

https://dba.stackexchange.com/questions/19762/why-is-the-select-clause-listed-first/

Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

An alternative would be:

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM 
    [Devices] as dv
    INNER JOIN 
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';
Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
4

There are a couple of things that are important here. First, there is a logical order of processing that occurs. And, the assignment of your DE column alias in the SELECT clause occurs after the WHERE clause is processed. So, the DE alias is not valid in the WHERE clause.

Note that the sub-query solution is logically processed before the outer query, so the DE alias is assigned to the column before you reference it.

You can alter the original query to do what you want. Here's one way.

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM 
    [Devices] as dv
    INNER JOIN 
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '2013-03-04'

Move your date test to the HAVING clause, where you can test aggregates. This will exclude data after it's been grouped. Hopefully, that's what you're trying to do.

bobs
  • 381
  • 6
  • 16