Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split data Expand / Collapse
Author
Message
Posted Monday, July 8, 2013 12:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1471045
Posted Monday, July 8, 2013 5:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 29, 2013 10:41 PM
Points: 8, Visits: 55
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.
Post #1471128
Posted Monday, July 8, 2013 6:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
-- 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




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1471134
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse