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!!!
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 |
|
