SQLTeam.com | Weblogs | Forums

Grouping with replacing repeat value


#1

I have two joined tables that return the results
Primary key, foreign key, date and repeating date the results are grouped by the foreign key.
Id id, date1 date2
1 1 1/1/10 6/1/15
2 1 1/1/10 6/1/15
3 1 1/1/10 6/1/15
4 2 1/1/10 2/1/14
5 2 5/1/10 2/1/15
6 3 5/1/10 4/1/15
7 3 6/7/10 4/1/15
I would like the results to return
Id id, date1 date2
1 1 1/1/10
2 1 1/1/10
3 1 1/1/10 6/1/15
4 2 1/1/10
5 2 5/1/10 2/1/15
6 3 5/1/10
7 3 6/7/10 4/1/15

I would like the results to return results but the last column replace repeating dates with a null except for the last date in each group.


#2

In the following query, I am assuming that the second column is named Id2 (because in your example both first and second columns are labeled Id)

;WITH cte AS 
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY id2 ORDER BY Id DESC) AS RN
	FROM yourCurrentQuery
)
SELECT
	Id, 
	Id2,
	date1,
	CASE WHEN RN > 1 THEN NULL ELSE date2 END AS date2
FROM
	cte
ORDER BY
	id2,
	id;

#3

Where do you want to show the data? If you use a reporting tool, use "Suppress if duplicated" feature