selecting top column1 with matching column2

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

i have this table:

[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

From stackoverflow

• one way

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

  Andreas Niedermair : sorry for depriving you the points :) but Bliek's solution is much more comfortable and offers a lot more of options...

• 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.

  Andreas Niedermair : sry ... it's mssql 2008

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

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

  Andreas Niedermair : as your solution provides more options, i'll give you the point!