Post reply

query results not matching

  • January 28, 2011 at 8:03 am

    #144453

    I have two queries one is fetching data for a particular id

    select max(a1.sequence)

    from A a1, R r1

    where a1.id = 1234

    and a1.id = r1.id

    and a1.sequence = r1.sequence

    This one below fetches data for all ids.To test this I added a particular id

    where a2.id = 1234.

    It does not fetches any record ,whereas the previous one does

    select

    max(a2.sequence),a2.id

    from

    A a2,R r2

    where

    a2.id=1234

    and a2.id=r2.id

    and a2.sequence=r2.sequence

    group by a2.id

    I want the max sequence for each id ,but this does not gets all the result.

    Can anyone please explain this behaviour or provide a way to select maximum for all ids.

    Thanks in advance

  • January 28, 2011 at 8:30 am

    #1279728

    Can you provide a full reproduction script ?

    please include DDL (Create Table ) , data inserts and Select that show your issue


    Clear Sky SQL
    My Blog[/url]

  • January 28, 2011 at 10:33 am

    #1279825

    Thanks for your reply.

    This is the full query.The table description is as follows

    In table A ,the columns are

    id,sequence,name,address....

    For one id there are multiple sequences.

    Table R

    id,sequence,startdate,enddate....

    Table also has multiple sequence for a id

    The query will get the ids from table and their max sequence number if the id and sequence

    number is also there in recipient table.

    Please help.

    Thankls in advance

  • January 28, 2011 at 10:42 am

    #1279829

    Sorry , i need SQL code i can run , not a vague description.


    Clear Sky SQL
    My Blog[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply