locked
Get id by max identifier RRS feed

  • Question

  • Is there anyhow to pass this as where statement? I mean, "where Max(version) group by name"
    I need to retrieve only the ids in it, something like this:

    select id where max(version) group by name
    Tuesday, September 10, 2013 2:02 PM

Answers

  • In this case you need to use Window function. I prepared a sample for you:

    USE AdventureWorks2008R2 
    go
    
    SELECT  *
    FROM    ( SELECT    * ,
                        MAX(PurchaseOrderDetailID) OVER ( PARTITION BY PurchaseOrderID ) AS mx
              FROM      Purchasing.PurchaseOrderDetail
            ) AS a
    WHERE   a.PurchaseOrderDetailID = mx


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page



    Tuesday, September 10, 2013 2:21 PM

All replies

  • You cannot use MAX() in WHERE clause. You can use it in HAVING clause like this:

    SELECT  id
    GROUP BY name, id
    HAVING   MAX(version) > 1


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page

    Tuesday, September 10, 2013 2:10 PM
  • But in this case I would have to retrieve all name with latest version, using having clause I wouldn't be able to do it so (if i'm not mistaken)
    Tuesday, September 10, 2013 2:14 PM
  • In this case you need to use Window function. I prepared a sample for you:

    USE AdventureWorks2008R2 
    go
    
    SELECT  *
    FROM    ( SELECT    * ,
                        MAX(PurchaseOrderDetailID) OVER ( PARTITION BY PurchaseOrderID ) AS mx
              FROM      Purchasing.PurchaseOrderDetail
            ) AS a
    WHERE   a.PurchaseOrderDetailID = mx


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page



    Tuesday, September 10, 2013 2:21 PM
  • Try this,

    select id from tablename
    where version IN (select max(version) from tablename group by name)


    Regards, RSingh


    Tuesday, September 10, 2013 2:23 PM
  • But in this case I would have to retrieve all name with latest version, using having clause I wouldn't be able to do it so (if i'm not mistaken)

    Post your complete query, so that we dont need to assume and reply.

    select * from <table1> T1 Inner join (SELECT name,MAX(version)max_version from <table1>

    GROUP BY name ) T2 On T1.name=T2.name and T1.version=T2.max_version



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Tuesday, September 10, 2013 3:02 PM
    Answerer
  • Hi Andre,

    Please confirm with us one thing, that does the three columns(id,name,version) in one table? It's hard to deliver a proper solution to you if we don't know the table structure.

    Thanks
    Candy Zhou

    • Edited by Candy_Zhou Wednesday, September 11, 2013 7:48 AM edit
    Wednesday, September 11, 2013 7:47 AM
  • Provide table structure and sample data. 

    Then its easy to provide solution 

    Amish shah

    http://blog.sqltechie.com

    Wednesday, September 11, 2013 8:29 AM