Query (duplicate rows)

Question

Post reply

Query (duplicate rows)

echobox888

Mr or Mrs. 500

Points: 573
More actions
June 3, 2008 at 6:17 am

#191299

I'm new in SQL, please help with this query. How can i remove the duplicates and it should show only the row with latest date?
Query: This doesn't work as multiple names are there.
Select E.Number, E.Name, Max(E.Date) As Date, M.Text1
From Entries As E Inner Join Main As M On E.Number = M.Number
Group By E.Number, E.Name, M.Text1
Result should be like this :
6469482 MariaC 2008-05-06 08:26:27.553
6469549 HillaryL 2008-05-15 10:28:09.333
6469560 AbdulS 2008-02-05 10:30:14.727
6469647 SureshB 2008-03-17 09:45:17.280
6469674 MarivicM 2008-04-16 14:31:59.753
Entries Main
Number (varchar (20)) Number (varchar (20))
Name (varchar(25)) Text1 (varchar(100))
Date (datetime)
Entries
6469482 AbdulS 2007-11-28 10:10:40.457
6469482 MariaC 2008-05-06 08:26:27.553
6469549 HillaryL 2008-05-15 10:28:09.333
6469549 SureshB 2007-11-21 08:44:36.470
6469560 AbdulS 2008-02-05 10:30:14.727
6469560 AbdulS 2007-11-15 10:57:14.773
6469647 SureshB 2008-03-17 09:45:17.280
6469674 AbdulS 2007-11-27 15:53:55.260
6469674 MarivicM 2008-04-16 14:31:59.753
Main
6469482 6363769
6469482 6363769
6469549 5673941
6469549 5673941
6469560 9419837
6469560 9419837
6469647 0837555
6469674 0849910
6469674 0849910

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 1

try to write a code for value <1, it will give you all duplicate rows

Manoj

MCP, MCTS (GDBA/EDA)

GSquared SSC Guru Points: 260824 More actions · Answer 2

Try this, see if it does what you need:

;with LastEntry (N, D) as

(select name, max(date)

from dbo.entries

group by name)

Select E.Number, E.Name, E.Date, M.Text1

From Entries As E

Inner Join Main As M

On E.Number = M.Number

Inner Join LastEntry

on E.Name = LastEntry.N

and E.Date = LastEntry.D

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Please do not make duplicate posts. It just annoys us and wastes everyones time.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

rbarryyoung (6/3/2008)
Please do not make duplicate posts. It just annoys us and wastes everyones time.

Same topic posted three times. Only 885 more to go.

^{“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