selecting top column1 with matching column2

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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