Flash Website Builder- Trendy Site Builder is a Flash Site Building tool that helps users build stunning websites. Check Out Custom Custom Logo Design by LogoBee. Website Design and Free Logo Templates available.
 CodingForums.com Problem with "distinct"

Before you post, read our: Rules & Posting Guidelines

Enjoy an ad free experience by logging in. Not a member yet? Register.
 06-20-2002, 11:58 PM PM User | #1 Justin Ba New Coder   Join Date: Jun 2002 Posts: 19 Thanks: 0 Thanked 0 Times in 0 Posts Problem with "distinct" SELECT DISTINCT a.field1, c.field2, a.field3, left(d.field4,15) FROM tbl_Table1 a LEFT JOIN tbl_Table2_R c ON a.field1 = c.FKField1 LEFT JOIN tbl_Table3_R d ON a.Field1= d.FKField1 WHERE 1=1 AND a.field3= 'text' ORDER BY a.field3 is returning duplicate a.field1 values where there are many d.field4s for the 1 a.field1. I'm totally stumped as i need it to return only 1 record per a.field1... I'm running SQL server 7.0 thanks in advance.
 06-21-2002, 12:49 AM PM User | #2 Justin Ba New Coder   Join Date: Jun 2002 Posts: 19 Thanks: 0 Thanked 0 Times in 0 Posts SELECT DISTINCT a.field1, c.field2, a.field3, left(d.field4,15) FROM tbl_Table1 a LEFT JOIN tbl_Table2_R c ON a.field1 = c.FKField1 LEFT JOIN tbl_Table3_R d ON a.Field1= d.FKField1 WHERE 1=1 AND a.field3= 'text' ORDER BY a.field3, a.field1 Same problem any more ideas?
 06-21-2002, 07:54 PM PM User | #3 Justin Ba New Coder   Join Date: Jun 2002 Posts: 19 Thanks: 0 Thanked 0 Times in 0 Posts GOT IT!! Ok guys... if anyone else has the same problem... after much thinking, i came up with a cheater way to accompish what i wanted!! here is the solution! SELECT DISTINCT a.field1, c.field2, a.field3, MAX(left(d.field4,15)) FROM tbl_Table1 a LEFT JOIN tbl_Table2_R c ON a.field1 = c.FKField1 LEFT JOIN tbl_Table3_R d ON a.Field1= d.FKField1 WHERE 1=1 AND a.field3= 'text' GROUP BY a.field3, a.field1, c.field3 Just add max to all the fields that might be Many -> 1 with your primary table.
 06-27-2002, 05:43 AM PM User | #4 Gozirra New to the CF scene   Join Date: Jun 2002 Location: Illinois Posts: 2 Thanks: 0 Thanked 0 Times in 0 Posts Let me splain Just so you know, distinct selects distinct rows, not a distinct column. Thus, select distinct col1, col2 from x will give you all distinct rows. If col1 = x 100 times but col2 = x+1 within each row, you will get 100 rows in your result set. Peace, Love, and Darth Vader.

 Bookmarks

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home :: Client side development     JavaScript programming         DOM and JSON scripting         Ajax and Design         JavaScript frameworks         Post a JavaScript     HTML & CSS     XML     Flash & ActionScript         Adobe Flex     Graphics and Multimedia discussions     General web building         Site reviews         Building for mobile devices :: Server side development     Apache configuration     Perl/ CGI     PHP         Post a PHP snippet     MySQL         Other Databases     Ruby & Ruby On Rails     ASP     ASP.NET     Java and JSP     Other server side languages/ issues         ColdFusion         Python :: Computing & Sciences     Computer Programming     Computer/PC discussions     Geek News and Humour Web Projects and Services Marketplace     Web Projects         Small projects (quick fixes and changes)         Medium projects (new script, new features, etc)         Large Projects (new web application, complex features etc)         Unknown sized projects (request quote)         Vacant job positions         Looking for work/ for hire         Project collaboration/ partnership         Paid work offers and requests (Now CLOSED)     Career, job, and business ideas or advice     Domains, Sites, and Designs for sale         Domains for sale         Websites for sale         Design templates and graphics for sale :: Other forums     Member Offers     Forum feedback and announcements

All times are GMT +1. The time now is 12:43 AM.