Goethals
If I got u properly, Follow these easy steps.. 1. Send the output of your first query to another table say t1 2. Send the output of finding min(Created) date to another table t2 3. Replace the value to TYPE field with for all rows which matches table t1 data 4. in the last replace the field TYPE those matches you t1 and t2 data with '' blank or whatever. Seems it will solve your problem.. there are solution too, but this one is simplest and easy to understand . Cheers :-) Neeraj Black Bits _____ From: Ruben Goethals [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 8:54 PM To: mysql@lists.mysql.com Subject: Selecting doubles in a table and setting all but the first Hi, everyone, This is something I can't seem to solve. Does anyone know how to do this ? I don't know whether I described this right. Anyhow, jumping right in, I have the following query which gives me all the LicenseNumbers that have a different Host-ID, but the same packageID (and with ValidThru < 3 which means I don't want to count the ones where validThru is greater than 2). What I want to do next is set another column "Type" for all the specific rows with these LicenseNumbers from this first query, EXCEPT the first row (if ordered in time)!!!! The first can be found by a column Created, which is a date. SELECT COUNT( DISTINCT `HostID` ) AS am, licensekeys. * FROM `licensekeys` WHERE ( CHAR_LENGTH( `ValidThru` ) <3 ) GROUP BY PackageID, `LicenseNumber` HAVING am >1 In practice: The above query gives me this (2068 results): am LicenseNumber PackageID 8 049-02-00011835 100 8 049-02-00009949 100 8 049-02-00010117 903 7 049-02-00015799 112 7 049-02-00010117 905 7 049-02-00010767 100 7 049-02-00000382 100 6 049-02-00009949 112 .. Checking Lic. Nr 049-02-00011835 with PackageID=100 for example gives this (8 results as given already above): HostID Created 803d2ed1 2/9/2004 14:26:30 cbac7a9 25/8/2005 9:22:35 9cb24cdd 8/2/2005 9:06:53 3453a0b0 2/9/2004 14:51:58 2cb4d794 15/9/2004 5:08:40 c0058a89 30/9/2004 13:21:39 94244c2f 6/1/2006 8:43:04 ac21b9ad 8/7/2004 8:38:26 What I would want now is that from the last query (just above this sentence) all rows, EXCEPT the earliest one (that would be the one with HostID=ac21b9ad because its Created date is oldest), get Type="replacement". The result would be then: HostID Created Type 803d2ed1 2/9/2004 14:26:30 replacement cbac7a9 25/8/2005 9:22:35 replacement 9cb24cdd 8/2/2005 9:06:53 replacement 3453a0b0 2/9/2004 14:51:58 replacement 2cb4d794 15/9/2004 5:08:40 replacement c0058a89 30/9/2004 13:21:39 replacement 94244c2f 6/1/2006 8:43:04 replacement ac21b9ad 8/7/2004 8:38:26 How in the name of... can I do this for ALL license Numbers from the first Query automatically ? (Please don't give me the solution for 1 LicenseNumber, but for all the ones form the first query at once or twice, but not in 2068 times) Much appreciated any help!!! Cheers, Ruben Goethals +32-(0)479/316655 [EMAIL PROTECTED] www.e-builds.com <http://www.e-builds.com/> <http://www.e-builds.com/e-builds.vcf> Get my vcard <http://www.e-builds.com> e-builds web development logo
