Select Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Query

Author

Topic

latiful
Starting Member

10 Posts

Posted - 2007-09-23 : 06:53:24

I am trying to write one TSQL query which will return the duplicate if EID, NID, Starttime, Endtime are same. Output will be same as below. Can anyboy help me with this.

Any help will be appreciated.

Sample data:

EID- NID- Starttime- Endtime

120625- 122283- 830- 2230
120625- 122284- 830- 2230
120625- 203355- 1730- 2230
129464- 409710- 745- 1600
129464- 409710- 745- 1600
129464- 492954- 745- 1600
129464- 492954- 745- 1600
129504- 310950- 900- 1500
130157- 117829- 615- 1500
130157- 117829- 615- 1500
130157- 132225- 700- 1500
130157- 132225- 700- 1500
130280- 410720- 500- 1530
130280- 410720- 500- 1830
130280- 410720- 800- 1830

Output:

129464- 409710- 745- 1600
129464- 409710- 745- 1600
129464- 492954- 745- 1600
129464- 492954- 745- 1600
130157- 117829- 615- 1500
130157- 117829- 615- 1500
130157- 132225- 700- 1500
130157- 132225- 700- 1500
130280- 410720- 500- 1530
130280- 410720- 500- 1830
130280- 410720- 800- 1830

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-23 : 09:06:39

[code]select s.*
from sample s
inner join
(
select starttime, endtime
from sample
group by starttime, endtime
having count(*) > 1
) d on s.starttime = d.starttime and s.endtime = d.endtime [/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 03:34:00

[code]SELECT EID,
NID,
StartTime,
EndTime
FROM (
SELECT EID,
NID,
StartTime,
EndTime,
COUNT(*) OVER (PARTITION BY EID, NID, StartTime, EndTime) AS Items
FROM @Sample
) AS d
WHERE Items > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

latiful
Starting Member

10 Posts

Posted - 2007-09-25 : 00:50:38

Thanks a lot for the reply. I want to take this little bit further. At the moment it is returning all of them, which is fine, but can I modify it so that it will only return the duplicate one, forexample if two duplicates then return only one row and if 3 duplicates then only return 2 rows,

Input:
129464- 492954- 745- 1600
129464- 492954- 745- 1600
130157- 117829- 615- 1500
130157- 117829- 615- 1500
130157- 117829- 615- 1500

Output:
129464- 492954- 745- 1600
130157- 117829- 615- 1500
130157- 117829- 615- 1500

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 01:29:35

Why didn't you say that rom the beginning?

SELECT	EID,
	NID,
	StartTime,
	EndTime
FROM	(
		SELECT	EID,
			NID,
			StartTime,
			EndTime,
			ROW_NUMBER() OVER (PARTITION BY EID, NID, StartTime, EndTime ORDER BY EID) AS RecID
		FROM	@Sample
	) AS d
WHERE	RecID > 1

E 12°55'05.25"
N 56°04'39.16"

latiful
Starting Member

10 Posts

Posted - 2007-09-25 : 17:50:20

Cool. Sorry I did not realize at the first place.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources