Hi Jack,
Unless you have some method to identify an interface as "historical" there
will be no way to eliminate the duplicates from your final report. It would
be very easy to have a result row for a router with N physical ports show
more than N logical ports without that kind of "exclude me" flag from those
historical rows.
Assume you added the BIT field "historical" to your interfaces table
(0=current, 1=old), then you could easily eliminate all old dupes by adding
a "WHERE historical=0" clause to your original query. Also, adding a field
could allow the query engine use an index rather than have to do a table
scan or a partial index scan for some substring in the description field.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jack Coxen
<[EMAIL PROTECTED] To: "'[EMAIL PROTECTED]'"
<[EMAIL PROTECTED]>, Jack Coxen
ve.com> <[EMAIL PROTECTED]>
cc: "MySQL List (E-mail)" <[EMAIL
PROTECTED]>
06/01/2004 02:28 Fax to:
PM Subject: RE: DISTINCT query
The output looks like this...
+----------+-----+------+------+ ~snip~ +-------+
| Router | 64K | 128K | 192K | ~snip~ | Total |
+----------+-----+------+------+ ~snip~ +-------+
| router_1 | 0 | 0 | 0 | ~snip~ | 6 |
| router_2 | 1 | 6 | 0 | ~snip~ | 70 |
| router_3 | 0 | 0 | 0 | ~snip~ | 3 |
| router_4 | 0 | 0 | 0 | ~snip~ | 3 |
| router_5 | 0 | 0 | 0 | ~snip~ | 1 |
interface.description isn't listed and I'd like it to stay this way.
interface.description is usually a unique value. However, when an
interface gets moved, a new record gets created with the same
interface.description but different interface.id and interface.name values.
I'd like to eliminate those semi-duplicate records from the count. I
know...I could modify the records when an interface changes but for various
reasons I need to keep both the before and after data.
Jack
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 01, 2004 1:52 PM
To: Jack Coxen
Cc: MySQL List (E-mail)
Subject: Re: DISTINCT query
Jack,
I am not sure what you want your output to look like... If you want the
same columns (the routerid with the counts of how many interfaces operate
at which speed) but broken down by unique descriptions that means to me
that you have multiple descriptions for each unique interface ID... Is this
the case? If it isn't then your existing GROUP BY will already make your
report unique by interface.comment (because there is only 1 comment per ID,
understand?)
If you could provide a prototype header to the report you want to make I
will try my best to help you work it out.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jack Coxen
<[EMAIL PROTECTED] To: "MySQL List
(E-mail)" <[EMAIL PROTECTED]>
ve.com> cc:
Fax to:
06/01/2004 01:12 Subject: DISTINCT query
PM
I'm having trouble with a query and hope someone of the list can help. I'm
running version 4.0.16. I'm trying to query against two tables:
mysql> DESCRIBE router;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| rid | int(11) unsigned | | PRI | NULL | auto_increment |
| name | char(120) | | | | |
| pop | char(10) | | | | |
| popid | tinyint(3) unsigned | | | 0 | |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)
mysql> DESCRIBE interface;
+-------------+---------------------------+------+-----+---------+----------
------+
| Field | Type | Null | Key | Default | Extra
|
+-------------+---------------------------+------+-----+---------+----------
------+
| id | int(11) unsigned | | PRI | NULL |
auto_increment |
| name | char(255) | | | |
|
| rid | int(11) | | | 0 |
|
| speed | bigint(11) | YES | | NULL |
|
| description | char(255) | YES | | NULL |
|
| status | enum('active','inactive') | YES | | active |
|
+-------------+---------------------------+------+-----+---------+----------
------+
6 rows in set (0.02 sec)
using the following query:
SELECT router.name AS Router,
SUM(IF(speed<='64000',1,0)) AS 64K,
SUM(IF(speed='128000',1,0)) AS 128K,
SUM(IF(speed='192000',1,0)) AS 192K,
SUM(IF(speed='256000',1,0)) AS 256K,
SUM(IF(speed='384000',1,0)) AS 384K,
SUM(IF(speed='512000',1,0)) AS 512K,
SUM(IF(speed='768000',1,0)) AS 768K,
SUM(IF(speed='1024000',1,0)) AS 1M,
SUM(IF(speed='1152000',1,0)) AS 1152K,
SUM(IF(speed='1280000',1,0)) AS 1280K,
SUM(IF(speed='1536000'
OR speed='1544000',1,0)) AS 'V/T1',
SUM(IF(speed='3000000',1,0)) AS 3M,
SUM(IF(speed='6000000',1,0)) AS 6M,
SUM(IF(speed='9000000',1,0)) AS 9M,
SUM(IF(speed='10000000',1,0)) AS 10M,
SUM(IF(speed='12000000',1,0)) AS 12M,
SUM(IF(speed='24000000',1,0)) AS 24M,
SUM(IF(speed>'40000000'
AND speed<'46000000',1,0)) AS 'DS3/T3',
SUM(IF(speed='100000000',1,0)) AS 100M,
SUM(IF(speed='155000000',1,0)) AS OC3,
SUM(IF(speed>'599000000'
AND speed<'650000000',1,0)) AS OC12,
SUM(IF(speed='1000000000',1,0)) AS Gigabit,
SUM(IF(speed='115000',1,0)) AS Dialup,
SUM(IF(speed>'64000'
AND speed!='128000'
AND speed!='192000'
AND speed!='256000'
AND speed!='384000'
AND speed!='512000'
AND speed!='768000'
AND speed!='1024000'
AND speed!='1152000'
AND speed!='1280000'
AND speed!='1536000'
AND speed!='1544000'
AND speed!='3000000'
AND speed!='6000000'
AND speed!='9000000'
AND speed!='10000000'
AND speed!='12000000'
AND speed!='24000000'
AND speed NOT BETWEEN '40000000' AND '46000000'
AND speed!='100000000'
AND speed!='155000000'
AND speed NOT BETWEEN '599000000' AND '650000000'
AND speed!='1000000000'
AND speed!='115000',1,0)) AS Other,
COUNT(*) AS Total
FROM router INNER JOIN interface USING (rid)
GROUP BY router.rid
ORDER BY router.name;
The query as written runs fine. My problem is that I want to SELECT only
DISTINCT values for interface.description and not display the descriptions
in the result.
Is this possible? And if so, how do I do it?
Thanks,
Jack
Jack Coxen
IP Network Engineer
TelCove
712 North Main Street
Coudersport, PA 16915
814-260-2705
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
