September 2008 - Posts

Return TOP N rows

The TOP Clause returns top rows from the table based on the number or percentage value
What if you want to have TOP N rows for each group?
The following explains it

(
The purpose is to return top 3 orders for each customer based on the
most recent orderdate from the table Orders in Northwind database
)

1 Use IN

select
        o.*
from 
        northwind..orders as owhere orderdate in 
        (select top 3 orderdate from northwind..orders 
        where customerid=o.customerid order by orderdate desc
        )order by customerid, orderdate desc

2 Dynamically generate serial number for each customer

select 
       
*
from 
        northwind
..orders as o
where 
       
(select count(*) from northwind..orders where customerid=o.customerid 
        and orderdate>=o.orderdate)<=3
        order by customerid,orderdate desc

3 Use Row_number() function

select * from
(
        select *, row_number() over(partition by customerid order by customerid,orderdate desc) as sno 
        from northwind..orders
) as t
where sno<=3

4 Use UDF and Cross Apply Operator

create
function dbo.top_orders
(
@customerid nchar(10),
@limit int
)
returns table
as
return
(
        select top (@limit) orderdate from northwind..orders 
        where customerid=@customerid order by orderdate desc
)
GO

select distinct o.* from northwind..orders as o
cross apply dbo.top_orders(o.customerid,3) as t
where o.orderdate=t.orderdate
order by customerid,orderdate desc

Posted by Madhivanan with 3 comment(s)
Filed under: , ,

Splitting delimited data to columns - Set based approach


In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns

Simon in his post Set-based-splitting-of-delimited-strings-to-columns showed how to use that in a set based approach which would work on a set of data.In his approach he has limited it to maximum of four columns
I have modified his approach to work for any number of columns

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')

DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT
        @pivot
=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM
        master
..spt_values where type='p' and
        number
<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT
    @select
='
        select p.*
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, '','' + data +'','' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('
+@pivot+'))p'

EXEC(@select)

DROP TABLE #test

Also Refer
csv to multiple columns
Dynamic pivot in sql server 2005

Posted by Madhivanan with no comments
Filed under: , , , ,

Should alias names be preceded by AS? - Part 2

In the Part 1, I expressed my opinion on having the alias names preceded by the keyword AS

Adding to the examples given at the link, consider this example too

SELECT
        10number
,
        10.number,
        10 number,
        10 [number],
        10[number],
        10 as number

It gives six columns with the result 10 and alias name number. Note that the first two expressions seem to be completely misleading

Am I right in insisting that SQL Server should force the usage of AS before the alias name?

Posted by Madhivanan with 1 comment(s)
Filed under: , ,

Fun with GO

In this blog post, I explained different uses of GO command

Here is a Fun when you use GO as object name

CREATE PROCEDURE GO
(
    @I INT
)
AS
    SELECT @I AS number

Well. The procedure is created and let us try to execute

GO 2

What we see is

Command
(s) completed successfully

Becuase it becomes a syntax to execute a batch for two times

EXECUTE GO 2

Result is 2

Now see what the results are for the following

Execution 1
[GO] 2

Execution 2
[GO] 2
GO 2

Execution 3
[GO] 2
GO
EXECUTE GO 2
GO

Execution 4
[GO] 2
GO 2
GO 2

Execution 5
[GO] 2
GO
GO

Execution 6
EXECUTE GO 2
EXECUTE GO 2
GO 2

Execution 7
[GO] 2
GO
GO 2
GO 2
EXECUTE GO 2
GO
GO 2
GO 2
GO 2

Execution 8
GO;

Execution 9
GO;2

Execution 10
GO 2;

 

Posted by Madhivanan with 3 comment(s)
Filed under: , ,

Empty string and Default values

Often newbies use empty string and consider it as NULL. But when an empty string is passed, it may not be stored as empty string or NULL. It actually depends on the datatype of the column

Consider this example

DECLARE @t TINYINT,@s SMALLINT,@i INT,@big BIGINT,@f FLOAT,@bit BIT, @sm SMALLMONEY,@m MONEY,@d DATETIME

 

SELECT

@t='',

@i='',

@big='',

@f='',

@bit='',

@sm='',
@m='',

@d=''

SELECT
@t as tinyint_col,

@i as int_col,

@big as bigint_col,

@f as float_col,

@bit as bit_col,

@sm as smallmoney_col,

@m as money_col,

@d as datetime_col

 

The result is Zero for all the variables except @d which has the date value of 1900-01-01 00:00:00.000





So beware of this and always use NULL if you want NULL values stored in the column
and dont use empty string
Posted by Madhivanan with no comments