using sql to get one random record(row) from each group in database

Question

I want to get one random library card from each group, group are column "state"

    select library card, state
    from [stategov]..library 
    where states in (
    'Ohio',
    'Arizon'
    )
group by state, librarycard

Should I use top 1? or CTE?

Since each state can have more than 10,000 library cards. What is the best way to get one random library cards from each state using group by?

Why are you using `with (nolock)`? You want unpredictable results? — Dale K, Jul 27 '21 at 06:32
@dale k sorry I meant by group by using states. ANd yes nolock is bad habit — sang jiang, Jul 27 '21 at 06:36
The duplicate gives you a random row per group by as requested. However you would benefit from improving your question with sample data and desired results. — Dale K, Jul 27 '21 at 06:46

score 0 · Accepted Answer · answered Jul 27 '21 at 06:39

You can try like following using ROW_NUMBER(). To get random you need to order by newid()

;WITH cte
AS (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY STATE ORDER BY (SELECT newid())) rn
    FROM [stategov]..library WITH (NOLOCK)
    WHERE states IN (
            'Ohio'
            ,'Arizon'
            )
    )
SELECT *
FROM cte
WHERE rn = 1

using sql to get one random record(row) from each group in database

1 Answers1