Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Unanswered: Select max and show last result

    Hi I am trying to select from a table and do max on the date and show the oldest result of field slstop.stop_flag

    I have tried this but it's not working - any help would be great

    Code:
    select slstop.account, slstop.sys_date, slstop.stop_flag
     from (
     select slstop.account, 
    slstop.sys_date,
     slstop.stop_flag,
     row_number() over (partition by slstop.account order by slstop.sys_date desc) as rn
     from slstop
     ) t
     where rn = 1
     order by slstop.account
    But get this error?

    Code:
    ERROR: missing FROM-clause entry for table "slstop"
     LINE 1: select slstop.account, slstop.sys_date, slstop.stop_flag
    Tried adding this

    Code:
    select slstop.account, slstop.sys_date, slstop.stop_flag
     from slstop(
     select slstop.account, 
    slstop.sys_date,
     slstop.stop_flag,
     row_number() over (partition by slstop.account order by slstop.sys_date desc) as rn
     from slstop
     ) t
     where rn = 1
     order by slstop.account
    But get this error? Where am i going wrong?

    Code:
    ERROR: syntax error at or near "select"
     LINE 3: select slstop.account,

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You are using the alias t for your derived table (the inner query). Therefor the outer query must also use "t"
    Code:
    select t.account, t.sys_date, t.stop_flag
    from (
       ....  
    ) t
    where rn = 1
    order by t.account
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jul 2012
    Posts
    3

    Almost

    Hi Shammat, thank you for your help so far.

    I have changed my query - but perhaps I have not quite understood what your explanation was

    Code:
    select t.account, t.sys_date, t.stop_flag
     from (
     select 
     t.account, 
     t.sys_date,
     t.stop_flag,
     row_number() over (partition by t.account order by t.sys_date desc) as rn
     from slstop
     ) t
     where rn = 1
     order by t.account
    I now get the following error

    Code:
    ERROR:  missing FROM-clause entry for table "t"
    LINE 4:  t.account, 
             ^
    
    ********** Error **********
    
    ERROR: missing FROM-clause entry for table "t"
    SQL state: 42P01
    Character: 61
    Whare am I going wrong?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Now you used the t alias in the inner query. But there you don't have the "t" alias. The inner query only "sees" the slstop table. The outer query only "sees" the "t" alais.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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