How do I select the top 1 results of the if the sa

问题:

I have a data table in sql server 2008 that I would like to select the top 1 out of each identifier:

The results shld looks like this during before and after:

Thus it should only select the 1st results if the same identifier do exist. Thanks a lot.

回答1:

Just distinct them:

select distinct [primary identifier] from tablename

Or by grouping:

select [primary identifier] from tablename group by [primary identifier]

If more columns exist you can rank rows with window function:

;with cte as(select *, row_number() over(partition by [primary identifier] order by (select null)) rn from tablename)
 select * from cte where rn = 1

Change order by (select null) to appropriate ordering column.

回答2:

select distinct [Primary Identifier] from tbl

If you have entire records (other columns) instead of that single column, you can row number them and choose one.

select {list of columns}
  from
  (
select *, rn = row_number over (partition by [Primary Identifier]
                                order by 1/0)
  from tbl
  ) X
 where rn = 1;

order by 1/0 is arbitrary. If you need to choose a specific one from the "duplicates", for example the highest cost, you order by cost descending, i.e.

                               (partition by [Primary Identifier]
                                order by [cost] descending)

回答3:

i think this will be an appropriate solution to your need-

;WITH cte AS
(
 SELECT *,
     ROW_NUMBER() OVER (PARTITION BY [Primary Identifier] ORDER BY [sort   columns]) AS rowid
 FROM [table]
)
SELECT *
FROM cte
WHERE rowid = 1