2

I have a table in Redshift where I have following records for a sample ID 71082:

id       trm_num        start_time
71082   PCMAMGA759551   2012-05-02 09:41:54
71082   PCMAMGA759551   2015-06-02 13:23:39
71082   PCMAMGA759551   2015-09-03 13:23:39
71082   PCMAMGA759551   2015-12-11 07:25:25
71082   PCMAMGA759551   2017-01-10 09:03:22

I want to choose only 1 random record for every id. For this I tried the query : 

select * from mytable where id=71082 order by random limit 1;

it fetched me random record.But the table has 1000s of different ids.How can I modify my query for the other ids?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
AKSHAY SHINGOTE
  • 407
  • 1
  • 8
  • 22

1 Answers1

6

Use the window function ROW_NUMBER with random order per ID:

select id, trm_num, start_time
from
(
  select
    id, trm_num, start_time,
    row_number() over (partition by id order by random()) as rn
  from mytable
) numbered
where rn = 1;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73