Test -- This Blogspot Is Under Construction

Test -- This Blogspot Is Under Construction

ROW_NUMBER(), Partition() DENSE_RANK() Sqlserver 2008

Code snipet ROW_NUMBER(), Partition()  DENSE_RANK() Sqlserver 2008.  untuk :
1, Mencari Total Value, alternative dari group by
2. Mencari rangking  dari value
3. RUNNING TOTAL ( CUMULATIVE VALUE)

1. Perbedaan antara ROW_NUMBER, RANK and DENSE_RANK

> Run SqlManagement Studio --> New query --> Copy Paste Code --> Execute (F5)

Buat table salaries :
declare @salaries table( name varchar(50), salary int)
insert into @salaries (name,salary)
SELECT'A',5000 UNION ALL
SELECT 'B',5000 UNION ALL
SELECT 'C',3000 UNION ALL
SELECT 'D',4000 UNION ALL
SELECT 'E',6000 UNION ALL
SELECT 'F',10000 UNION ALL
SELECT 'G',4000

Run Query : 
SELECT name, salary
,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
,rank () OVER (ORDER BY salary DESC) as RANK
,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
FROM @salaries

RANK()  will rank the next record according to the row number (5)
DENSE_RANK will rank the next record by ordering (4)


2. Pemakain kombinasi Rank() over Partition
Buat table test :
declare @test table(id int, nama varchar(50), nilai int)
insert into @test(id,nama,nilai)
select 1,'a',2 union
select 2 ,'b',2 union
select 3 ,'a',3 union
select 4 ,'b',1 union
select 5 ,'c',1 union
select 6 ,'c',3

Basic Query
Run Query : 
select nama,nilai,rank() over(partition by nama order by nilai desc) rnk from @test

Query hanya nilai tertinggi
Run Query : 
select * from (select nama,nilai,rank() over(partition by nama order by nilai desc) rnk from @test) t where t.rnk=1

Query Total Nilai dikelompokan berdasarkan  nama
Run Query : 
select * from (select nama,nilai,sum(nilai) over( partition by nama) as total,rank() over(partition by nama order by nilai desc) rnk from @test) t where t.rnk=1

Query rankng ke dua dari rangking tertinggi
Run Query : 
;WITH  xxx AS (select nama,nilai, rank() over(partition by nama order by nilai desc) rnk from @test )
select * from xxx where rnk=2

Query Grand Total Dikelompokan berdasarkan  nama pad multi join table
Buat table Person :
 declare @Person table(id int, nama varchar(50) )
 insert into @Person(id,nama )
 select 1,'Andri'   union
 select 2 ,'Nur'   union
 select 3 ,'Iwan'


Buat table LoanDetil :
 declare @LoanDetil table (id int ,personid int ,TransactionDate datetime,balance float)
 insert into @LoanDetil (id  ,personid   ,TransactionDate  ,balance  )
 select 1,1,'1/1/2007',1000 union
 select 2,2,'1/1/2007',2000 union
 select 3,1,'2/1/2007',500 union
 select 4,3,'3/1/2007',1200 union
 select 5,1,'2/14/2007',100 union
 select 6,3,'3/10/2007',1000 union
 select 7,2,'3/10/2007',7000

Run Query : 
select P.id,P.nama,X.total,X.rown from @Person P
 inner join
 (select *,  sum(balance) over (partition by personid) total, ROW_NUMBER()over(partition by personid order by id desc) rown  from @LoanDetil) X
 on P.id=X.personid where X.rown=1

3. RUNNING TOTAL ( CUMULATIVE VALUE)
Buat table Accounts :
declare @Accounts table(ID int ,TransactionDate datetime,Balance float)
insert into @Accounts(id,TransactionDate,Balance)
select 1,'1/1/2000',100 union
select 2,'1/2/2000',101 union
select 3,'1/3/2000',102 union
select 4,'1/4/2000',103 union
select 5,'1/5/2000',104 union
select 6,'1/6/2000',105 union
select 7,'1/7/2000',106 union
select 8,'1/8/2000',107 union
select 9,'1/9/2000',108 union
select 10,'1/10/2000',109

Run Query : 
select *,  (select  sum(balance) from @Accounts where  TransactionDate <= acc1.TransactionDate )  as runingtotal from @Accounts acc1

or

select *, runingtotal= (select  sum(balance) from @Accounts where  TransactionDate <= acc1.TransactionDate )   from @Accounts acc1