On Jul 31, 9:47 pm, weileo...@gmail.com wrote:

You can do this

select group , max(value) as value
from table
group by group

If you're using SQL Server 2005 or later, you can select these rows
from a partitioned row_number:

with Numbered as (
 select
   *,
   row_number() over (
     partition by group_num
     order by group_num -- since you don't care about the order
   ) as rn
 from yourTable
)
 select
   <columns you need>
 from Numbered
 where rn = 1

With SQL Server 2000 or earlier, you will have to decide
just which row you want for each group and can specify it
with an aggregate or not exists:

select group_num, max(value) as value
from yourTable
group by group_num

Note that in this query, if you have additional columns beyond [value],
you may not want to use max() on each one, because then the results
you obtain may not be rows of the original table. Max(value) may
come from a different row than the Max(some_other_column), for
example.

Better is something like this for 2000:

select <columns>
from yourTable
where not exists (
 select * from yourTable T
 where T.group_num = yourTable.group_num
 and T.primaryKey > yourTable.primaryKey
)

Steve Kass
Drew University
http://www.stevekass.com