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]
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]) 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
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
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.