Ordering the result set randomly

Sometimes you may need to show some data randomly. You can use NEWID() function to get data randomly

Let us consider the following data

Create table #countries (country_name varchar(100), city_name varchar(100))

insert into #countries
select 'India', 'Chennai' union all
select 'India', 'Bangalore' union all
select 'India', 'New Delhi' union all
select 'India', 'Hyderabad' union all
select 'India', 'Mumbai' union all
select 'United States', 'New York' union all
select 'United States', 'Boston' union all
select 'United States', 'Miami' union all
select 'Canada', 'Toronto' union all
select 'Canada', 'North bay'

You can order the result set randomly using NEWID() function as shown below

1 Order entire result set randomly.

select country_name,city_name from #countries
order by newid()

 

The result is

country_name           city_name
-------------------    --------------------------
United States          New York
India                  Chennai
United States          Miami
India                  Bangalore
United States          Boston
Canada                 North bay
India                  Mumbai
India                  Hyderabad
Canada                 Toronto
India                  New Delhi

2 Order city name randomly for each country.

select country_name,city_name from #countries
order by country_name, newid()

The result is

country_name                 city_name
---------------------------  --------------------
Canada                       North bay
Canada                       Toronto
India                        Hyderabad
India                        Bangalore
India                        Mumbai
India                        Chennai
India                        New Delhi
United States                New York
United States                Boston
United States                Miami

3 Get one city randomly for each country.

select country_name,city_name from
(
select *,
row_number() over ( partition by country_name order by country_name, newid()) as sno
from #countries
) as t
where sno=1

There result is

country_name                 city_name
---------------------------  --------------------
Canada                       Toronto
India                        Chennai
United States                Miami
Advertisements

2 thoughts on “Ordering the result set randomly

  1. Just thought I’d try a few slightly different methods of getting the same result:
    This query is more efficient:
    ;with CTE AS
    (
    select distinct A.country_name
    from #countries A
    ),
    CTE2 AS
    (
    select B.*
    from CTE
    cross apply (select top(1) country_name,city_name
    from #countries B
    where B.country_name = CTE.country_name
    order by newid()) B
    )
    SELECT *
    FROM CTE2

    SCRIPT:

    set statistics io,time off
    set nocount on
    go
    if object_id(‘tempdb..#countries’) IS NOT NULL
    DROP TABLE #countries

    Create table #countries (country_name varchar(100), city_name varchar(100))
    go
    insert into #countries
    select ‘India’, ‘Chennai’ union all
    select ‘India’, ‘Bangalore’ union all
    select ‘India’, ‘New Delhi’ union all
    select ‘India’, ‘Hyderabad’ union all
    select ‘India’, ‘Mumbai’ union all
    select ‘United States’, ‘New York’ union all
    select ‘United States’, ‘Boston’ union all
    select ‘United States’, ‘Miami’ union all
    select ‘Canada’, ‘Toronto’ union all
    select ‘Canada’, ‘North bay’
    go 100000
    set statistics io,time on
    go
    GO
    print ‘Query1’
    select country_name,city_name from
    (
    select *,
    row_number() over ( partition by country_name order by country_name, newid()) as sno
    from #countries
    ) as t
    where sno=1
    GO

    print ‘Query2’

    ;with CTE AS
    (
    select *,
    row_number() over ( partition by country_name order by country_name, newid()) as sno
    from #countries
    )
    select country_name,city_name
    from CTE
    where sno=1
    GO

    print ‘Query3’

    select distinct A.country_name, B.city_name
    from #countries A
    cross apply (select top(1) city_name
    from #countries B
    where B.country_name = a.country_name
    order by newid()) B

    GO

    print ‘Query4’

    ;with CTE AS
    (
    select distinct A.country_name
    from #countries A
    ),
    CTE2 AS
    (
    select B.*
    from CTE
    cross apply (select top(1) country_name,city_name
    from #countries B
    where B.country_name = CTE.country_name
    order by newid()) B
    )
    SELECT *
    FROM CTE2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s