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 |
|
tstoner98133
Starting Member
2 Posts |
 Posted - 2008-04-06 : 16:51:01
|
| I am trying to return a result from a select statement where I want unique (distinct) records but i want to return other columns as well as the distinct column. for exampleselect distinct field1, field2 from mytableI want unique records for field1 but for those records returned I also want their field2 value. It seems distinct is not the correct choice for this. I have a hard time imagining there is not a standard solution for this. Can anyone tell me? Thanks in advance!!!!Tim |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-06 : 17:24:52
|
| provide some sample data and the result you wish to achive with that sample data.what you need is probably use of group by clause._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
tstoner98133
Starting Member
2 Posts |
Posted - 2008-04-06 : 17:42:09
|
| two fields, myid and fromaddress. fromaddress is as email address. id is a record id which is unique for that record. I want to return a table which does not have any duplicate email addresses, but I want the nyid field also so I have a record id for the records returned.Is that enough data? Thanks!As long as I am asking, here is what I am actually trying to do: I have about 10k email from my inbox. I want to list all the email addresses along with a number of how many times they occur in the list, e.g.1234 mom@mom.com1003 husband@work.com 734 girlfriend@herwork.com.........1 somespam@spamhost.comI am just a novice, seeing what other people and figuring out how things are done. I can do this programattically using a script but is there a logical way to do it using SQL. This DISTINCT problem is a learning exercise as much as anything else. It just seems there should be an easy way to do this.Thanks so much in advance for your help!!Tim |
|
|
|
soorajtnpki
Posting Yak Master
231 Posts |
Posted - 2008-04-07 : 01:33:29
|
| try this select max(myid) as id,fromaddress from table_name group by fromaddress.this will generate distinct ids with distinct emails.otherwise let me know please.. okthanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-07 : 04:01:22
|
Or, if you want to know the number of occurancies, select count(*), fromaddress from yourtable group by fromaddress
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
DISTINCT or some other solution