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.
| Author |
Topic |
|
matticusfinch
Starting Member
9 Posts |
 Posted - 2005-09-27 : 14:59:47
|
| I have a table, member_contact_info that has multiple members with the same ARCODE that have multiple EMAIL values. The members are seperated and tied into a specific email address by their FAMNUM. However, there are some EMAIL addresses with the same FAMNUM.Example:ARCODE FAMNUM EMAIL123402 0 jbrinkman@oceanreef.com 123402 0 jonathanbrinkman@yahoo.com123402 2 hottie@jonathanbrinkman.com123402 5 marissa@jonathanbrinkman.com 123402 0 12334357@33dfghe.cin I am trying to run a query in which I can get each family member and their e-mail address, except only the top value if there is more than one. (I.E., the jbrinkman@oceanreef.com address). Using TOP1 or DISTINCT does not work, and MAX or MIN doesn't necessarily give me the top value.Further, I am joining to another MEMBERS table that holds more info by ARCODE, (name, etc.) but if I can get the proper e-mail to display through this query, I can use as a sub-query and build from there. Any Ideas?? |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-09-27 : 15:30:53
|
| What determines if a particular email address is the top one? If you mean the top one as it appears in a select statement, that's not really reliable because the order of the records isn't fixed. You would be better off selecting an address based on MAX, MIN, or some other column (like an entry date/time). If you don't really care which address you choose, then you can just use something like this:SELECT arcode, famnum, MAX(email) FROM member_contact_info GROUP BY arcode, famnum Or MIN, instead of MAX. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-28 : 00:40:29
|
| Try thisSELECT Distinct arcode, famnum, (Select Top 1 email FROM member_contact_info where arcode=T.arcode and famnum=T.famnum) as EmailFROM member_contact_info TMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
This is a tough one (for me)