June 3, 2008 at 1:42 am
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
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
June 3, 2008 at 2:19 am
I think this is what you want:
(I grouped both separately to remove the duplicates from each table.)
Select
E.Number, E.Name, E.Date As Date, M.Text1
From
Entries As E
INNER JOIN (SELECT Number , MAX(Date) AS Date FROM Entries GROUP BY Number ) E2 ON E.Number = E2.Number
AND E.Date = E2.Date
Inner Join (SELECT Number , MAX(Text1) AS Text1 FROM Main GROUP BY Number ) M On E.Number = M.Number
June 3, 2008 at 3:02 am
HI,
Try this and confirm whether this is what you are looking for,
Select E.Number, E.Name, E.Date As Date, M.Text1
From Entries As E Inner Join Main As M On E.Number = M.Number
where E.Date =(select max(date) from entries)
Rajesh
June 3, 2008 at 3:41 am
Sorry Mr tertiusdp and Mr raja_saminathan but both queries don't work.
Mr tertiusdp - the query shows an error when i run it.
Mr raja_saminathan - the query will only show 1 row.
June 3, 2008 at 4:35 am
select Number, Text1, [name], max(date) from
(select distinct e.Number, m.Text1, e.[name], e.date
from dbo.Entries e
inner join dbo.Main m
on e.Number = m.Number
) a
group by Number, Text1, [name]
(8 row(s) affected)
6469482 6363769 AbdulS 2007-11-28 10:10:40.457
6469482 6363769 MariaC 2008-05-06 08:26:27.553
6469549 5673941 HillaryL 2008-05-15 10:28:09.333
6469549 5673941 SureshB 2007-11-21 08:44:36.470
6469560 9419837 AbdulS 2008-02-05 10:30:14.727
6469647 0837555 SureshB 2008-03-17 09:45:17.280
6469674 0849910 AbdulS 2007-11-27 15:53:55.260
6469674 0849910 MarivicM 2008-04-16 14:31:59.753
June 3, 2008 at 5:50 am
Sir the number should show only 1 row which has latest date.
6469482 should show only 1.
6469482 MariaC 2008-05-06
June 3, 2008 at 7:14 am
select distinct i.number, m.text1, i.name, i.date
from dbo.Main m
inner join (
select e.number, e.name, e.date
from dbo.Entries e
inner join (
select Number, max(date) as date
from dbo.Entries
group by Number
) s
on s.Number = e.Number
and s.[date] = e.date
) i
on i.Number = m.Number
(5 row(s) affected)
6469482 6363769 MariaC 2008-05-06 08:26:27.553
6469549 5673941 HillaryL 2008-05-15 10:28:09.333
6469560 9419837 AbdulS 2008-02-05 10:30:14.727
6469647 0837555 SureshB 2008-03-17 09:45:17.280
6469674 0849910 MarivicM 2008-04-16 14:31:59.753
June 3, 2008 at 9:00 am
Try this:
;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
June 4, 2008 at 1:38 am
Thanks Mr Suresh, it works.
Thanks also Mr GSquared, shall i just copy paste that query?
April 3, 2012 at 3:19 am
Old post but still usefull, thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply