As an academic exercise, Would this also work? Select agName, agURL from agents as t where t.agName in (Select distinct agName from agents)
I'm just being curious. I prefer James' solution and I'm guessing 'IN' is less efficient, but would be interested to know if it would still meet the requirements? -Nate > -----Original Message----- > From: James Holmes [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 04, 2006 9:22 AM > To: SQL > Subject: Re: SELECT DISTINCT > > > If we assume the PK of the record is agID, how about > > SELECT agName, agURL > from tbl_sometable t > where t.agID = ( > SELECT MIN(agID) as myID > FROM tbl_sometable > where t.agName= tbl_sometable.agName > GROUP BY agName > ) > > This picks the one with the lowest PK in each group of agName > and returns the name and URL from that one. > > On 7/4/06, Claude Schneegans <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I can't figure how to do this in SQL: > > I have a query: > > SELECT agName, agURL > > FROM agents > > In the table I may have several identical agName with > different agURL. > > What I want is only one record per agName, with whatever agURL, > > doesn't matter which one, but at least one of them. > > > > If I use > > SELECT DISTINCT agName, agURL > > FROM agents > > I get all identical agName with different agUrl. I would > need only the > > first of each group with the same agName. Is it possible? > > > > -- > > _______________________________________ > > REUSE CODE! Use custom tags; > > See http://www.contentbox.com/claude/customtags/tagstore.cfm > > (Please send any spam to this address: [EMAIL PROTECTED]) > > Thanks. > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2493 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
