Select Random Row for Each Group

Select random row for each group

select distinct on (id) id, attribute
from like_this
order by id, random()

If you only need the attribute column:

select distinct on (id) attribute
from like_this
order by id, random()

Notice that you still need to order by id first as it is a column of the distinct on.

If you only want the distinct attributes:

select distinct attribute
from (
    select distinct on (id) attribute
    from like_this
    order by id, random()
) s

Select a random row from each group SQL Server

select top 1 with ties id,code,age 
from
table
order by row_number() over (partition by id order by rand())

Update: as per this Return rows in random order, you have to use NEWId,since RAND() is fixed for the duration of the SELECT on MS SQL Server.

 select top 1 with ties id,code,age 
 from
 table
order by row_number() over (partition by id order by NEWID())

How to select a random record for each group

You can do it with distinct on:

select distinct on (a) a, b, c, d
from test t;

Here is a Demo

With DISTINCT ON, You tell PostgreSQL to return a single row for each
distinct group defined by the ON clause.

More about that subject here: https://www.geekytidbits.com/postgres-distinct-on/

Random row with group by?

You need filtering rather than aggregation.

In MySQL 8.0, you can use window functions:

SELECT *
FROM (
    SELECT 
        i.*, 
        s.*,
        row_number() over(partition by s. SetCategorie Order by rand()) rn
    FROM images i
    INNER JOIN set s ON i.ImageSetId = s.SetId
) t
WHERE rn = 1

In earlier versions, it is much more complicated. Here is a hacky solution using a partial group by:

SELECT *
FROM (
    SELECT 
        i.*, 
        s.*
    FROM images i
    INNER JOIN set s ON i.ImageSetId = s.SetId
    ORDER BY RAND()
) t
GROUP BY SetCategorie

How to select a random row with a group by clause?

What you need is a Random aggregate function. Usually there are no such functions in the current RDBMSs.

Similar question has been asked.

So the basic idea is shuffle the elements, then group by, and then for every group just select the first row for every group. If we modify one of answers provided on the link we get this.

select object_id, name, image_path
from
  (SELECT images.image_path AS image_path, objects.id AS object_id, objects.name
  FROM objects LEFT JOIN images ON images.object_id = objects.id
  ORDER BY RAND()) as z
group by z.object_id, z.name

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

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

Select one random row by group (Oracle 10g)

Use a subquery:

select person_id, purchase_date
from (select df.*,
             row_number() over (partition by person_id order by dbms_random.value()) as seqnum
      from df
     ) df
where seqnum = 1;

Randomly select a row from each group using pandas

Use groupby with apply to select a row at random per group.

np.random.seed(0)
df.groupby(['Month', 'Day'])['mnthShape'].apply(np.random.choice).reset_index()

   Month  Day  mnthShape
0      1    1   1.016754
1      1    2   0.963912
2      1    3   1.099451

If you want to know what index the sampled rows come from, use pd.Series.sample with n=1:

np.random.seed(0)
(df.groupby(['Month', 'Day'])['mnthShape']
   .apply(pd.Series.sample, n=1)
   .reset_index(level=[0, 1]))

   Month  Day  mnthShape
2      1    1   0.963912
3      1    2   1.016754
6      1    3   1.016754

Select random row for each group in a postgres table

Try something like:

SELECT DISTINCT ON (category) *
FROM table 
ORDER BY category, random();

Or with window functions:

SELECT * 
FROM (
SELECT *, row_number() OVER (PARTITION BY category ORDER BY random()) as rn
FROM table ) sub
WHERE rn = 1;

---

Related Topics