Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Using Alias or Row_Number in Where Clause

    Good day,

    I have the following SQL statement:
    Select e.Field1 as 'ID', e.Field2 as 'ID Desc', b.Field1 as 'Kg', DateDiff(hour, a.DateEntered, GetDate()) as 'Age',
    a.Field1, b.Field2 as 'Length', a.Field1, (ROW_NUMBER() OVER (ORDER BY DateDiff(hour, a.DateEntered, GetDate()) DESC)) AS 'RowNum'
    From Table1 a, Table2 b, Table3 c, Table4 d, Table5 e
    Where e.FieldA = d.FieldA
    And d.FieldA = b.FieldA
    And a.Field1 = b.FieldB
    And a.FieldB = c.FieldA
    And e.Field1 = 'ID DESCRIPTION'
    --And RowNum = 10
    Order By 4 desc


    The line I have commented out is what I would like to get to work, at present it does not. I am using the alias from the Select list here and can't get it to work. IF this is not possible is there a way I can say only return for instance the 10th ROW_NUMBER row? The value of the row to be returned would have to be dynamic.

  2. #2
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Sorry just after I posted this I managed to figure it out, quite simple in the end:

    Select * From
    (
    Select e.Field1 as 'ID', e.Field2 as 'ID Desc', b.Field1 as 'Kg', DateDiff(hour, a.DateEntered, GetDate()) as 'Age',
    a.Field1, b.Field2 as 'Length', a.Field1, (ROW_NUMBER() OVER (ORDER BY DateDiff(hour, a.DateEntered, GetDate()) DESC)) AS 'RowNum'
    From Table1 a, Table2 b, Table3 c, Table4 d, Table5 e
    Where e.FieldA = d.FieldA
    And d.FieldA = b.FieldA
    And a.Field1 = b.FieldB
    And a.FieldB = c.FieldA
    And e.Field1 = 'ID DESCRIPTION'
    )
    Where RowNum = 10

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •