SQL server 2008 R2, select one value of a column f

问题:

On SQL server 2008 R2, I would like to select one value of a column for each distinct value of another column.

e.g.

 name   id_num
 Tom      53
 Tom      60
 Tom      27
 Jane     16
 Jane     16
 Bill     97
 Bill     83

I need to get one id_num for each distinct name, such as

 name   id_num
 Tom      27
 Jane     16
 Bill     97

For each name, the id_num can be randomly picked up (not required to be max or min) as long as it is associated with the name.

For example, for Bill, I can pick up 97 or 83. Either one is ok.

I do know how to write the SQL query.

Thanks

回答1:

SELECT 
 name,MIN(id_num)
FROM YourTable
GROUP BY name

UPDATE: If you want pick id_num randomly, you may try this

WITH cte AS (
  SELECT 
    name, id_num,rn = ROW_NUMBER() OVER (PARTITION BY name ORDER BY newid())
  FROM YourTable
)
SELECT *
FROM cte
WHERE rn = 1

SQL Fiddle Demo

回答2:

You could grab the max id like this:

SELECT name, MAX(id_num)
FROM tablename
GROUP BY name

That would get you one id for each distinct name.

回答3:

select name, max(id_num) from [mytable] group by name

回答4:

The (SELECT 1) in the cte does not really order the data in each of the partitions. which should give you the random selection.

CREATE TABLE #tmp
(
    name VARCHAR(10)
    , id_num INT
)
INSERT INTO #tmp
SELECT 'Tom', 53 UNION ALL
SELECT 'Tom', 60 UNION ALL
SELECT 'Tom', 27 UNION ALL
SELECT 'Jane', 16 UNION ALL
SELECT 'Jane', 16 UNION ALL
SELECT 'Bill', 97 UNION ALL
SELECT 'Bill', 83

;WITH CTE AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY (SELECT 1)) AS ID
        , name
        , id_num
    FROM #tmp
)
SELECT *
FROM CTE
WHERE ID = 1