Select Top 1 - 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

General SQL Server Forums

New to SQL Server Programming

Select Top 1

Author

Topic

HGClubTec
Starting Member

12 Posts

Posted - 2009-03-30 : 11:06:11

I have a table - CorrEemail that includes multiple email addresses for each Entity. I don't care which one I pull, I just need one (if there is one and if there isn't I want the null value).

This is what I have (one of my many attempts), but I can't seem to get the results I'm looking for.

SELECT
E.ID,
E.FirstName,
E.LastName,
CE.CorrEemail

FROM
Entity E LEFT OUTER JOIN
Type T ON E.TypeID = T.ID LEFT OUTER JOIN
CorrEmail CE ON CE.EntityID = (SELECT TOP 1 CE.EntityID FROM CorrEmail WHERE E.ID = CE.EntityID)

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-30 : 11:46:23

Does this work?

SELECT E.ID,E.FirstName,E.LastName,CE.CorrEemail
FROM Entity E
LEFT OUTER JOIN
Type T ON E.TypeID = T.ID
LEFT OUTER JOIN
CorrEmail CE ON CE.EntityID = E.ID

just looking again - why the join to Type - your not using it?

HGClubTec
Starting Member

12 Posts

Posted - 2009-03-30 : 11:55:16

You can rid of the type join. I narrowed down my query to what was relevant (I'll add it back after I figure out the deal)

The query you sent still gives me the problem and I guess I should be more clear.

I am still getting duplicate CorrEmail addresses. I only want one - don't care which one I get -I just want one.

Thanks!

matty
Posting Yak Master

161 Posts

Posted - 2009-03-31 : 00:29:46

SELECT E.ID,E.FirstName,E.LastName,CE.CorrEemail
FROM Entity E
LEFT OUTER JOIN
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY EntityID ORDER BY EntityID) AS Rownum FROM CorrEmail
)CE
ON CE.EntityID = E.ID AND CE.Rownum = 1

HGClubTec
Starting Member

12 Posts

Posted - 2009-04-01 : 09:16:25

This works AWESOMELY (is that a word)? And worked out great on my server, but when I transferred it over, I realized they are on SQL 2000. Thank you so much, but is there any solution to do this in 2000?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 12:43:07

[code]
SELECT E.ID,E.FirstName,E.LastName,CE.CorrEemail
FROM Entity E
LEFT OUTER JOIN
(
SELECT CE1.EntityID,CE1.CorrEemail
FROM CorrEmail CE1
JOIN (SELECT EntityID,MAX(EmailID) AS Latest
FROM CorrEmail
GROUP BY EntityID)CE2
ON CE2.EntityID=CE1.EntityID
AND CE2.Latest=CE1.EmailID
)CE
ON CE.EntityID = E.ID
[/code]

i'm assuming EmailID is primary key of CorrEmail. if not,replace it by actual pk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources