It depends what you mean by "the first occurrence of that IDNum". Remember that data in a relational database has no inherent order. Why do you want to arbitrarily select one Description out of the many possible ones? Anyway, let's assume you mean the row with the first Description by alphabetical order, then: select IDNum, min(Description) from myTable group by IDNum should do it Nick -----Original Message----- From: Paul Sinclair [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 01, 2001 4:48 PM To: CF-Talk Subject: SQL: How to get distinct number where other fields not distinct I've run into this problem several times before and never took the time to figure out how to deal with it properly - I've just sort of kludged around it. Now I've got the same situation arising and would like to resolve it properly. It is a problem trying to use a distinct select query where: (1) I also need to pull several other fields and (2) the other fields are themselves distinct. A simplified example db is like this: IDNum Description ----- ----------- 1 red apple 1 red aple 1 red apples 2 green apple 2 green delicious apples 3 plum I want to select just the distinct IDNums from the table and display them in a select box with the distinct IDNums and the Description for the first occurrence of that IDNum. So for the above table, I want a select list like: 1 - red apple 2 - green apple 3 - plum Since the _Description_ field for the instances of IDNum 1 and 2 are distinct, I get 3 rows with IDNum 1 and 2 rows with IDNum 2 if I do a SQL query such as: select distinct IDNum, Description from myTable How do you do a select query that will give you just 1 row for each distinct IDNum in this type of situation? Thanks, Paul Sinclair ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
