Sql Server 2008 R2, Select One Value Of A Column For Each Distinct Value Of Another Column

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

Solution 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

Solution 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.

Solution 3:

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

Solution 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