Ordering the result set randomly

Posted on by madhivanan

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

Total days in a Year

Posted on by madhivanan

Pinal Dave, Blogger and Speaker, posted about “SQL SERVER – Trivia – Days in a Year“. There are two methods shown in the post to find out total number of days for a given year. Here is one more method

DECLARE @year AS INT
SET @year=2012
select 365+
case
when @year%400=0 then 1
when @year%100=0 then 0
when @year%4=0 then 1
else 0
end

The result is 366.

The logic is that every year has 365 days. We need to find if it is a Leap year. If it is add 1 to it

Data validation using TRY_CONVERT function

Posted on by madhivanan

One of the T-SQL functions introduced in SQL Server 2012 is TRY_CONVERT. It is used to convert a value into a specific data type.

A simple example is

SELECT TRY_CONVERT(DATETIME,'20000101')

The result is 2000-01-01 00:00:00.000

This function can also be used to validate the data

Let us consider the following dataset

create table #t (data varchar(100))
insert into #t (data)
select '20000101' union all
select '20009' union all
select '19-feb-2012' union all
select '200.45' union all
select 'test' union all
select '11.11.2007'

1 Find out only valid dates

select data from #t
where try_convert(datetime,data) is not null

The result is

data
--------------
20000101
19-feb-2012
11.11.2007

2 Find out only integers

select data from #t
where try_convert(int,data) is not null

The result is

data
--------------
20000101
20009

3 Find out only numbers

select data from #t
where try_convert(float,data) is not null

The result is

data
-------------
20000101
20009
200.45

Fun with DOT

Posted on by madhivanan

The dot (.) is used to separate database name,table name and column name when you use JOINs. The simple example is

select cust.customer_name,sum(detail.total_spend) as total_spend from
customers as cust inner join sales as detail
on cust.customer_id=detail.customer_id
group by cust.customer_name

As you see dot is used to separate the table name and column name. But did you know you can use dot in some other ways too?

create table #test(number int)
insert into #test(number) select 400
select * from #test

The result is 10. But what surprises you is that you can use the dot in the following ways too

select * from .#test
select * from ..#test
select * from …#test

The result is 10 for all the statements. Did you expect error? When dots are used without prefixed by objects, what happens is that

  • When a single dot is used, by default the current user is considered so it becomes username.tablename.
  • When two dots are used, by default the current database and user are considered so it becomes dbname.username.tablename.
  • When three dots are used, by default the current server, database and user are considered so it becomes servername.dbname.username.tablename

Now, what do you think the following return?

select * from .#test[………….]
select * from ..#test[….]
select * from …#test[…]

The result is 10 for the above queries. The dots in square braces become the alias name for the table #test