Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: Order by causing problems in sub query

    Hi I am trying to get the latest logon time for each user in the second subquery. However when I use order by in the second sub query I get the error: "ORA - 00907: missing right parenthesis". I am using version 9i, is it possible to do this as one query or will I need two seperate ones?

    select t1.userName,
    (SELECT count(userId)
    FROM log_history t2
    WHERE userId = t1.userId),

    (SELECT logon_dtime
    FROM log_history t3
    WHERE rownum =1
    AND t3.userId= t1.userId
    ORDER BY logon_dtime
    )

    FROM users t1;

    Any help would be greatly apprecaited.

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Instead of using rownum = 1, you could do max:

    Code:
    select max(logon_dtime)
       from log_history
    where t3.user_id = t1.user_id
    Assuming logon_dtime is a date column, which if its a date, should be a date column. That would be one way to do it.

    EDIT:
    Ok on 2nd thought I would personally do something more along the lines of this:
    Code:
    select t1.userName,
             t2.user_count,
             t2.last_logon_time
      from users t1,
             (select userid, 
                       count(userid) user_count, 
                       max(logon_dtime) last_logon_time
                 from log_history
                group by userid) t2
    where t1.userid = t2.userid
    This should fit it all in one query, and get what you need - i'm not connected to test, but it looks like it should be close
    Last edited by ss659; 10-16-05 at 12:24.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    ultramods,
    you can not use order by in subquery, it doesn't make sense. BTW the result wouldn't be as you wish even if you could use it.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    yep incase you didnt realise the rownum is calculated before the order by.

    Alan

  5. #5
    Join Date
    Oct 2005
    Posts
    2
    Thank you for all your help, I have used the max function and it is now working.

Posting Permissions

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