selecting random value from column based on distinct values

Question

I have following data in table :-

| item    | rate |
-------------------
| a       | 50   |
| a       | 12   |
| a       | 26   |
| b       | 12   |
| b       | 15   |
| b       | 45   |
| b       | 10   |
| c       | 5    |
| c       | 15   |

and i need a query which return following output :

| item no | rate |
------------------
| a       | 12 |  --from (26 , 12 , 50)
| b       | 45 | --from (12 ,15 , 45 , 10)
| c       | 5  | --from (5 , 15)

i.e item_no should be distinct and with randomly one rate value..

Thanks in advance

Answer 1

WITH
CTE
AS
(
    SELECT DISTINCT
        item
    FROM T
)
SELECT
    CTE.item
    ,A.rate
FROM
    CTE
    CROSS APPLY
    (
        SELECT TOP(1) 
            T.rate
        FROM T
        WHERE T.item = CTE.item
        ORDER BY CRYPT_GEN_RANDOM(4)
    ) AS A
;

Index on (item) include (rate) would help.

Answer 2

One approach is to use the row number window function with item as the partition. Then use ORDER BY NEWID() within each partition to generate a random ordering, retaining the first row of each item partition arbitrarily.

SELECT t.item,
       t.rate
FROM
(
    SELECT item,
           rate,
           ROW_NUMBER() OVER(PARTITION BY item ORDER BY NEWID()) AS rn
    FROM yourTable
) t
WHERE t.rn = 1

Answer 3

HERE NEWID() is used for partition to generate a random ordering,
within partition , it will retain the first row of each item.

with cte as
    (

        SELECT item,
               rate,
               ROW_NUMBER() OVER(PARTITION BY item ORDER BY NEWID()) AS rn
        FROM #Table1
        )
        select item,rate from cte where rn=1

Answer 4

; WITH tb( item, rate)AS(
    SELECT 'a',50 UNION
    SELECT 'a',12  UNION
    SELECT 'a', 26 UNION
    SELECT 'b',12  UNION
    SELECT 'b', 15 UNION
    SELECT 'b',10 UNION
    SELECT 'c',5  UNION
    SELECT 'c',15 
    ) 
    SELECT * FROM (
        SELECT *,ROW_NUMBER()OVER( PARTITION BY item ORDER BY NEWID())  AS rn FROM tb
    ) t WHERE t.rn=1

Answer 5

Try this query once.

select * into #temp  from (
        SELECT 'a' item,50 rate 
        UNION ALL
        SELECT 'a',12  
        UNION ALL
        SELECT 'a', 26 
        UNION ALL
        SELECT 'b',12  
        UNION ALL
        SELECT 'b', 15 
        UNION ALL
        SELECT 'b',10 
        UNION ALL
        SELECT 'c',5  
        UNION ALL
        SELECT 'c',15 )as a

    select * from (
    select *,row_number()over(partition by item  ORDER BY newid() )as runm from #temp
    ) as a
    where runm=1

Note: The above query gives every time the new output as the newid() generates a new id for every execution .