Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Near a PC
    Posts
    4

    Unanswered: Retrieving rows with 2 indexes

    I have a table that contains 2 keys (iId and iVersion) and some other data:

    iId int(4) NOT NULL default '0',
    iVersion int(4) NOT NULL default '0',
    vchName varchar(50) default NULL

    The data looks a little like this:

    1|1|Fred
    1|2|Fred edited once
    1|3|Fred edited twice
    2|1|Dave
    2|2|Dave edited once
    2|3|Dave edited twice

    I need a sql statement that will return all columns of the latest row (based on the greatest iVersion value) for each unique iId in the table.

    So using the data above it should return

    1|3|Fred edited twice
    2|3|Dave edited twice

    Any help would be appreciated!

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    select iId,
    iVersion,
    vchName
    from <table_name>
    group by iId
    having iVersion = max(iVersion)

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    select iId,
           iVersion,
           vchName
    from   <table_name>
    where (iId, iVersion) in
    ( select iId,
             max(iVersion) maxVer
      from   <table_name>
      group by iId
    );
    Or if using Oracle you could use the analytic functions.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would suggest using:
    Code:
    SELECT *
       FROM myTable AS a
       WHERE  a.iVersion = (SELECT Max(b.iVersion)
          FROM myTable AS b
          WHERE  b.iId = a.iId)
    -PatP

  5. #5
    Join Date
    Aug 2004
    Location
    Near a PC
    Posts
    4
    May have been that i was using MySQL but none of the above worked!

    They did however help me to get some sql that does work....

    select iId, vchName, MAX(iVersion) as iVersion
    from <Table>
    GROUP BY iId

    Thanks.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nzwy1p
    select iId, vchName, MAX(iVersion) as iVersion
    from <Table>
    GROUP BY iId
    you may think this works, but it doesn't

    even the mysql docs tell you that this gives unpredictable results (holler if you need the link to the page in the docs where it explains this)

    what you want is the vchName that comes from the row which has the largest iVersion, but this is not what you are getting, and if it looks like you are getting it, it is a fluke

    you could just as easily get this instead --

    1|3|Fred edited once
    2|3|Dave

    i'm sorry if this sounds like i'm dumping all over you, it's not your fault, it's mysql's fault for allowing non-standard sql to run (in any other database system, your query would generate a syntax error)

    here's what you want, done without subqueries if you're not on 4.1 yet --
    Code:
    select X.iId 
         , X.iVersion 
         , X.vchName 
      from yourtable as X
    inner
      join yourtable as Y
        on X.iId
         = Y.iId  
    group
        by X.iId 
         , X.iVersion 
         , X.vchName 
    having X.iVersion
         = max(Y.iVersion)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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