Advanced selection help

[gbrian]

Fields: contactID, siteID

Each site may be associated with 1 to 500 contactID's. What I am trying to do is select 2 contact ID's for each siteID.

Any ideas?

 

[lespaul]

something like:

SELECT SiteID, ContactID FROM [b]TableName[/b] A Where ContactID in (SELECT TOP 2 ContactID FROM [b]TableName[/b] B Where A.SiteID = B.SiteID)

copy and paste in the SQL view and change the TableName.

Leslie

 

[gbrian]

That helped a little--the problem is that will select 2 contact ID's each time it finds that site ID...how can I use a distinct list of my site id's to do this?

 

[lespaul]

I don't understand what you mean.

Can you show some sample data, what results you are getting compared to the results you want.


Leslie

 

[gbrian]

Here is an example of one site that is returned:

SITE_ID CONTACT_ID
0000241027 00000000558349
0000241027 00000000558347
0000241027 00000000558347
0000241027 00000000558349
0000241027 00000000558349
0000241027 00000000558349
0000241027 00000000558347
0000241027 00000000558347

 

[PHV]

You may try SELECT DISTINCT

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

May be two distinct(asc/desc) query and a union query of the distict queries.
Or someone might have an easier way.

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[lespaul]

like PHV stated:

SELECT DISTINCT SiteID, ContactID FROM TableName A Where ContactID in (SELECT TOP 2 ContactID FROM TableName B Where A.SiteID = B.SiteID)

Leslie

 

[gbrian]

Thanks- I am good to go!