selecting top column1 with matching column2

sorry for asking this, but i’m runnin’ out of ideas

i have this table:

  • converting sql server rowversion to long or ulong?
  • Uploading a file into SQL Server
  • SQL Server - Selecting a Record With MAX Value
  • What port is a remote SQL Server 2005 running on?
  • Split Single Column into multiple and Load it to a Table or a View
  • Validate telephone number in SQL Server 2000
  • [id]    [pid]    [vid]
    1        4        6844
    1        5        6743
    2        3        855
    2        6        888

    how to i query this eg.table to get the following result:

    [id]    [pid]    [vid]
    1        5        6743
    2        6        888

    i want to get the highest [pid] for an [id] with the [vid] matching to this [pid]

    any ideas?

    i’m using mssql 2008

    3 Solutions collect form web for “selecting top column1 with matching column2”

    I would use Common Table Expressions (CTE). This offers lots of possibilities like so:

    WITH Result (RowNumber, [id], [pid], [vid])
        SELECT Row_Number() OVER (PARTITION BY [id]
                                  ORDER     BY [vid] DESC)
          FROM MyTable
    SELECT [id]
      FROM Result
     WHERE RowNumber = 1

    one way

    select t1.* from
    (select id,max(pid) as Maxpid
    from yourtable
    group by id) t2
    join yourtable t1 on =
    and t2.Maxpid =

    Since you’re using Microsoft SQL Server 2008, then I’d recommend Common Table Expressions and the OVER clause to accomplish dividing the results into groups by id, and returning just the top row in each group (ordered by pid). Bliek’s answer shows one way to do that.

    (This same basic approach, by the way, is very useful for more efficient paging, as well.)

    There isn’t a fantastically great way to do this with “standard” SQL. The method show in SQLMenace’s answer will only work in databases where you can use a subquery as a table. It’d be one way to accomplish this in SQL Server 2000, for example, but may not work in every mainstream RDBMS.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.