scotrail: random_apology

Custom SQL query returning 6 rows (hide)

with apology as (
    select Transcription, mp3 from announcements where
        Category = 'Apology'
    order by random() limit 1
),
train_company as (
    select Transcription, mp3 from announcements where
        Category = 'Train operating company'
        and Transcription like '%to%'
    order by random() limit 1
),
destination as (
    select Transcription, mp3 from announcements where
        Category = 'Destination'
    order by random() limit 1
),
cancelled as (
    select Transcription, mp3 from announcements where
        Transcription = 'has been cancelled'
    limit 1
),
due_to as (
    select Transcription, mp3 from announcements where
        Transcription = 'due to'
    limit 1
),
reason as (
    select Transcription, mp3 from announcements where
        Category = 'Reason'
    order by random() limit 1
),
combined as (
  select 1 as sort, * from apology
  union select 2, * from train_company
  union select 3, * from destination
  union select 4, * from cancelled
  union select 5, * from due_to
  union select 6, * from reason
)
select * from combined order by sort

Edit SQL