0

I'm trying to use ROW_NUMBER() function to give me distinct groups of my data but keep getting an Invalid column name error. Here is my query:

SELECT
    ROW_NUMBER() OVER (PARTITION BY Access.Number ORDER BY Access.Time) dstbadge,
    Access.Event AS 'Event',
    Access.Reader AS 'Reader',
    Access.Time AS 'Event Time UTC',
    Access.LastName AS 'Last',
    Access.FirstName AS 'First',
    Access.Number AS 'EmpNum'
FROM
    Access
WHERE
    dstbadge = 1

The above query would provide distinct groups of Access.numbers ordered by Access time and return only the first row in each group.

I get this error:

Msg 207, Level 16, State 1, Line 59
Invalid column name 'dstbadge'

aynber
  • 22,380
  • 8
  • 50
  • 63
patrickA.
  • 1
  • what database engine are you using?, what es the error message? – Lamak Mar 07 '23 at 16:29
  • 1
    Welcome to stackoverflow. Please provide the schema of the table (Access) you are querying from. Please provide the error message. Please put more care into the clarity and formatting of the question. – w08r Mar 07 '23 at 16:29
  • 2
    ah, I see now, you can't use a column alias directly in the `WHERE` of that table. Either use a CTE or a derived table – Lamak Mar 07 '23 at 16:30

1 Answers1

0

Here's an article on the order in which SQL Server executes queries. As you can see, the relevant parts (in order of execution) for your query are:

  1. FROM
  2. WHERE
  3. SELECT

You're trying to apply WHERE on a column that is defined only in your SELECT and it fails because the SQL Server process doesn't know what that column is at the point when the WHERE is executed. It won't know about that column until it hits the SELECT, which is executed after the WHERE.

As Lamak suggests in the comments, a CTE or derived table can be used to resolve this.

Deirdre O'Leary
  • 420
  • 2
  • 6