You can also do Select serialnumber >From inventory Where serialnumber not in(select distinct serialnumber from inventory)
I may have the syntax a little wrong, but look up the IN() syntax and do a subquery. Basically grabs a distinct listing of serial numbers and then grabs all rows that are not in that distinct list. John Burns Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | Web Developer -----Original Message----- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 2:27 PM To: CF-Talk Subject: Re: find identical records SELECT serialnumber, count(*) AS rowCount FROM inventory GROUP BY serialnumber HAVING rowCount > 1 That should give you a list of serials that exist more than once, along with how many instances there are. And update your AVG, it says it's out of date. cheers, barneyb On 9/23/05, Orlini, Robert <[EMAIL PROTECTED]> wrote: > I know this one must be easy, but I'm not a guru w/SQL yet. > > How do I construct an SQL query that will list all records that have an identical field? > > For example, listing all records that contain an identical number in the serialnumber column. > > I did: > > Select distinct serialnumber from inventory order by serialnumber asc > > to get the unique records. > > Robert O. > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219241 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
