Unique results returned from Select

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

Unique results returned from Select

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-18 : 23:16:22

This has been asked many times before and the more answers I read, the more confused I become.

My table:
lcreator // locid // issue // status

Joe, 12, missing photos, Notify
Jim Bob, 1344, missing information, Notify
Mary Joe, 33, needs to update, Notify
Jim Bob, 444, blurry photos, Notify
Betty, 32, blurry photos, Notify
Jim Bob, 1244, no information on location, Notify

I want to read from mytable, all records where status = 'Notify' (which means the person will be receiving an e-mail to let them know they need to fix their photos).

The 'issue' will be what they need to fix and the 'locid' is a location ID. The 'lcreator' is the location creator's name.

I'm trying to only send out ONE message per user to tell them that they have to fix their photos. Currently I am receiving multiple results for the same name if lcreator has multiple entries of the same person.

eg.
Jim Bob
Jim Bob
Jim Bob
Mary Joe
Betty

This is my code:

strsql = "select distinct lcreator,locid,issue from mytable where status = 'Notify'"

while not objRS.eof
tofld = objrs("lcreator")
lcn = objrs("locid")
reason = objrs("issue")
.....<snip>

It is giving me multiple results for lcreator when I want distinct ones. I believe this is due to SQL looking through all rows so it is including 'locid' and 'issue' in the query. I don't want this.

I need to return the results from locid and issue columns (some of which will be duplicate) but only ONE result per LCREATOR. It doesn't matter which result it is, as long as only ONE Jim Bob, one Betty, one Mary Joe gets returned.

Once the person receives a message, they can view ALL records under their name through another SELECT statement I've got working.

Help would be gretly appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-19 : 02:28:53

Try this:

select t1.lcreator,t1.locid,t1.issue 
from mytable t1
join (select lcreator,max(locid) as locid from mytable where status = 'Notify' group by lcreator)t2
on t1.locid = t2.locid

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources