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