May 20, 2008 at 5: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:
select r.Number,f.text1,r.Name,r.Date
from Entries r ,Main f
where f.number = r.number
Results:
Number Text1 Name Date
6469482 6363769AbdulS2007-11-28 10:10:40.457
6469482 6363769MariaC2008-05-06 08:26:27.553
6469549 5673941HillaryL2008-05-15 10:28:09.333
6469549 5673941SureshB2007-11-21 08:44:36.470
6469560 9419837AbdulS2008-02-05 10:30:14.727
6469560 9419837AbdulS2007-11-15 10:57:14.773
6469647 0837555SureshB2008-03-17 09:45:17.280
6469674 0849910AbdulS2007-11-27 15:53:55.260
6469674 0849910MarivicM2008-04-16 14:31:59.753
May 20, 2008 at 6:38 am
[font="Verdana"]If possible post the table structure of both the tables.
Mahesh[/font]
MH-09-AM-8694
May 20, 2008 at 6:47 am
First, look up JOIN syntax in the Books Online. You're using ANSI 89 syntax. Most database systems support ANSI 92 or better.
You must have more than one row in one table that matches a single row in the other. You'll need to, in some fashion, show a tie-breaker to differentiate the rows returned.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2008 at 7:00 am
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
May 20, 2008 at 7:07 am
[font="Verdana"]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
you can use MIN as well depending upon which date's data you need.[/font]
MH-09-AM-8694
May 20, 2008 at 7:17 am
Ok thank you very much... i need to know these joins
May 20, 2008 at 7:20 am
[font="Verdana"]For more information on JOINS refer BOL.[/font]
MH-09-AM-8694
June 3, 2008 at 12:04 am
Mr Mahesh, please check the names as there are different names
June 3, 2008 at 9:15 am
It looks like this same question ended up in three threads in this forum. As with all the rest, 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 8:05 am
Basically the same solution that GSquared offered but without using CTE.
SELECT r.[Number]
,f.[text1]
,r.[Name]
,r.[Date]
FROM [Entries] r
--this join returns the latest Entry record per name
INNER JOIN (--return a recordset with one record per name and latest date for that name
SELECT [Name] AS [Name]
,MAX([Date]) AS [Date]
FROM Entries
GROUP BY [Name]
) s
ON r.[Name] = s.[Name]
AND r.[Date] = s.[Date]
INNER JOIN [Main] f
ON f.[number] = r.[number]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply