Help Required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help Required

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-09-03 : 07:01:11

Hai,

I'm using SQL Server 2005

DECLARE @Sample TABLE
(
ContNo Varchar(7),Refno Varchar(10),
Refdt SmallDatetime,Amount Real
)

INSERT Into @Sample
Select 'C1','R100','2010/04/01',10000
Union All
Select 'C1','R212','2010/05/01',9000
Union All
Select 'C1','R344','2010/05/01',9500
Union All
Select 'C2','R433','2010/03/01',9080
Union All
Select 'C2','R534','2010/04/01',9000
Union All
Select 'C2','R234','2010/05/01',9400
Union All
Select 'C2','R236','2010/06/01',9400

I want to find the max refdt of a particular contno and its receipt particulars

Output Expected
C1,R344,2010/05/01,9500
C2,R236,2010/06/01,9400

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-03 : 07:28:11

What is the logic to select such output ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-03 : 11:58:33

How do you want to handle ties? Meaning mutiple records for the same date..?

Here are a couple queries that might help get you on your way:

-- Query 1
SELECT
	ContNo, 
	Refno, 
	Refdt, 
	Amount
FROM
( 
	SELECT *, 
	ROW_NUMBER() OVER (PARTITION BY ContNo ORDER BY RefDt DESC) AS RowNum
	FROM @Sample
) AS T
WHERE
	RowNum = 1

-- Query 2
SELECT
	ContNo, 
	Refno, 
	Refdt, 
	Amount
FROM
( 
	SELECT *, 
	RANK() OVER (PARTITION BY ContNo ORDER BY RefDt DESC) AS RowNum
	FROM @Sample
) AS T
WHERE
	RowNum = 1

--Query 3	
SELECT 
	A.*
FROM 
	@Sample AS A
INNER JOIN
	(
		SELECT ContNo, MAX(Refdt) AS MaxDate
		FROM @Sample
		GROUP BY ContNo
	) AS B
	ON A.ContNo = B.ContNo
	AND A.RefDt = B.MaxDate

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-09-04 : 01:45:24

Thanks Lamprey

I slightly modified your query and it works for me.

Thanks one again

-- Query 1
SELECT
ContNo,Refno,Refdt,Amount
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY ContNo ORDER BY RefDt DESC,Refno Desc) AS RowNum
FROM @Sample
) AS T
WHERE
RowNum = 1

Nirene

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources