July 8, 2013 at 12:08 am
Hi all, I was trying to split the values using DENSE RANK but I faced some issues...
here is the problem below:
Create table tbl1
(
ID INT identity primary key,
voucherno varchar(10),
modifieddate datetime
)
insert into tbl1
values
('V00001','2013-07-07 11:32:44.810'),
('V00002','2013-07-07 11:32:44.810'),
('V00003','2013-07-08 12:32:44.810'),
('V00004','2013-07-08 12:32:44.810'),
('V00005','2013-07-07 11:32:44.810')
OUTPUT
MinVoucher MaxVoucher Modifieddate
V00001 V00002 2013-07-07 11:32:44.810
V00003 V00004 2013-07-08 12:32:44.810
V00005 V00005 2013-07-07 11:32:44.810
I tried using DENSE_RANK but i didnt get my desired output.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 8, 2013 at 5:43 am
CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))
INSERT INTO Emp VALUES ('Abhinav',40,'S')
INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')
SELECT Dense_RANK() OVER (ORDER BY Age) AS [Rank by Age],
EmpName,
Age
FROM Emp
Like this model u try this example.
July 8, 2013 at 6:05 am
-- Extend the sample data set:
DROP table #tbl1
Create table #tbl1
(
ID INT identity primary key,
voucherno varchar(10),
modifieddate datetime
)
insert into #tbl1
values
('V00001','2013-07-07 11:32:44.810'),
('V00002','2013-07-07 11:32:44.810'),
('V00003','2013-07-08 12:32:44.810'),
('V00004','2013-07-08 12:32:44.810'),
('V00005','2013-07-07 11:32:44.810'),
('V00006','2013-07-07 11:32:44.810'),
('V00007','2013-07-09 11:32:44.810'),
('V00008','2013-07-09 11:32:44.810'),
('V00009','2013-07-08 11:32:44.810')
-- examine the result of this
SELECT *,
rn1 = ROW_NUMBER() OVER(ORDER BY voucherno),
rn2 = ROW_NUMBER() OVER(ORDER BY modifieddate, voucherno)
FROM #tbl1
-- to see how this works
SELECT
ID,
voucherno,
modifieddate,
rn = ROW_NUMBER() OVER(PARTITION BY GroupBy ORDER BY voucherno)
FROM (
SELECT
ID,
voucherno,
modifieddate,
GroupBy = ROW_NUMBER() OVER(ORDER BY voucherno)-
ROW_NUMBER() OVER(ORDER BY modifieddate, voucherno)
FROM #tbl1
) d
ORDER BY voucherno
-- your results are rn=1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply