Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2011
    Posts
    18

    Unanswered: max date selection

    hi guys,

    I am trying to archieve selecting different Ids with different codes and i have this code runing

    select code
    from tbl1
    where activity_date =(select max(s.activity_date)
    from tbl1 s
    where s.tbl1_id = tbl1_id)

    I want to be able to list different codes based based on which is/are currently used - my issue is, when i ran the code above i got one row of information with the max date of one id only, what am i doing wrong?

    Thanks in advance,

    eenje

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please realize that we don't have your tables & we don't have your data.
    Therefore we can't run, test or improve your posted SQL.
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2011
    Posts
    18
    Sorry about that, I am using a banner tbl with this syntax

    select sorints_ints_code
    from sorints
    where sorints_activity_date =(select max(s.sorints_activity_date)
    from sorints s
    where sorints_id = s.sorints_id)

    and i want to see the different codes attached to the same id base on date used in a specific year.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by eenje View Post
    what am i doing wrong?
    Aliasing only one (not needed) table. As the used tables and column names are the same, the condition takes the column without alias from the inner table (as it is available in the nearest scope). So, the condition is equal to
    Code:
    s.tbl1_id = s.tbl1_id
    which is always true, does not correlate to the outer query and returns always one value - maximal ACTIVITY_DATE.

    You should give alias to the table in the outer SELECT, something like this (differences from your query are in uppercase):
    Code:
    select code
    from tbl1 T
    where activity_date = (select max(s.activity_date)
      from tbl1 s
      where s.tbl1_id = T.tbl1_id)

  5. #5
    Join Date
    Jun 2011
    Posts
    18
    I did get it running but i have this error showing: -

    ORA-01427: single-row subquery returns more than one row.

    the code is embedded in a calculated field

    any ideas?

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by eenje View Post
    I did get it running but i have this error showing: -

    ORA-01427: single-row subquery returns more than one row.

    the code is embedded in a calculated field

    any ideas?
    What happened when you ran this query alone?

    If there is only one row with maximal TBL1.ACTIVITY_DATE, the previous code returned one row.
    If there are rows with different TBL1.TBL_ID, the corrected code returns multiple rows - (at least) one for each TBL1.TBL_ID (as you probably required).

    According to the error, "embedding in calculating query" (whatever it is) requires only one row.
    As you did not post more details about it, this is all I can say.

  7. #7
    Join Date
    Jun 2011
    Posts
    18
    when i ran the query alone it gave me the codes i wanted but with multiple code(repeating fields for a number. I want it to give me the codes for a specific year based of the main selection, lets say i pick 2011, it will give me codes for that year alone, rather than giving me all the codes associated with a number. there is no year to specify in the table except that is why i am trying to connect it to the max date
    Last edited by eenje; 07-05-11 at 18:28.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by eenje View Post
    when i ran the query alone it gave me the codes i wanted but with multiple code(repeating fields for a number. I want it to give me the codes for a specific year based of the main selection, lets say i pick 2011, it will give me codes for that year alone, rather than giving me all the codes associated with a number. there is no year to specify in the table except that is why i am trying to connect it to the max date
    If you want to filter rows based on year from ACTIVITY_DATE, just add this condition:
    Code:
    extract( year from activity_date ) = 2011
    It is impossible to deduce from your post, where should this be placed or whether it will suffice.

    Maybe you should post a test case.
    Table definition - relevant columns and constraints should be enough.
    Sample data - around ten rows should be enough, but they should fully cover all possible scenarios/cases.
    Some people here prefer this in form of SQL statements (CREATE TABLE, INSERT), so they are able to check it without the need of its laborious preparation.
    And, in the end, the expected result with text description of the logic how it should be achieved.

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    Additionally, repeating fields for a number may happen, when the same TBL1.CODE is present with maximal TBL1.ACTIVITY_DATE for different TBL1.TBL_IDs. Just temporarily use this column in SELECT clause and see its result (ORDERing by TBL1.CODE could show it clearer).
    Using DISTINCT or UNIQUE keyword after SELECT keyword will return unique column combinations, so this probably is what you would want.

    It seems the problem is in the tool (whatever it is - yes you did not post it) where the query is used or your implementation of some functionality there. Maybe passing some specific TBL_ID from outside would be needed too. But, this is just a guess. According to your description, I am unable to say more.

    Good luck.

  10. #10
    Join Date
    Jun 2011
    Posts
    18
    IT WORKED, thanx flyboy

Posting Permissions

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