Re: Why does a group_concat on a join change aggregate values?

Should have showed the whole thing. Take a look here (click image to see full output): http://www.pintumbler.org/tmp On Thu, May 7, 2015 at 4:11 PM, shawn l.green wrote: > Hi Paul, > > On 5/7/2015 10:17 AM, Paul Halliday wrote: > >> Fighting a bit with this one... >> &

Why does a group_concat on a join change aggregate values?

apart. What is happening behind the scenes? Thanks! -- Paul Halliday http://www.pintumbler.org/

Re: Help with REGEXP

; > this should come close: > > 10[.]\d{1,3}[.](224|225|226|227|228|229|23\d))[.]\d{1.3} > > On Thu, Mar 19, 2015 at 9:39 AM, Paul Halliday > wrote: > >> I am trying to pick out a range of IP addresses using REGEXP but >> failing miserably :) >> >> The p

Help with REGEXP

I am trying to pick out a range of IP addresses using REGEXP but failing miserably :) The pattern I want to match is: 10.%.224-239.%.% The regex I have looks like this: AND INET_NTOA(src_ip) REGEXP '\d{1,3}\\.\d{1,3}\.(22[4-9]|23[0-9])\\.\d{1,3}' but, go fish. Thoughts? Thanks!

InnoDB error 5

27;read' returned OS error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? Thanks. -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list ar

Re: InnoDB error 5

at 9:46 AM, Manuel Arostegui wrote: > 2013/11/21 Reindl Harald > >> >> Am 21.11.2013 13:51, schrieb Paul Halliday: >> > Had a system crash this morning and I can't seem to get mysql back up >> > and running. This is the error: >> > >> >

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

I am so, so glad that someone finally said what I think each time I see a message from you Mr. James.  Original message From: Rick James Date: 06-12-2013 8:45 PM (GMT-04:00) To: Daevid Vincent ,mysql@lists.mysql.com Subject: RE: How do I select all rows of table that have

Re: query help

er by the_ids DESC LIMIT 1 > > Is there a way to pull this off without going thru this step? Would substring work? SELECT SUBSTRING(recid, 13, ) AS numbers FROM table ORDER BY numbers DESC -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: Group expansion as part of the result

On Tue, Mar 27, 2012 at 3:43 PM, Dan Nelson wrote: > In the last episode (Mar 27), Paul Halliday said: >> Say I have: >> >> SELECT COUNT(name), name, COUNT(DISTINCT(status)) FROM table GROUP BY name >> >> and it returns: >> >> 20 paul 5 >> 19

Delete from another table on update.

Is it possible to wrap a DELETE statement in an ON DUPLICATE KEY UPDATE? Something like: ON DUPLICATE KEY UPDATE host="b1" (DELETE FROM another_table WHERE host="b1") ? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For

Re: Inconsistent query result.

On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley wrote: > On 10/11/2011 8:11 AM, Paul Halliday wrote: >> >> I have the following query: >> >> SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, >> INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip)

Inconsistent query result.

' depending on user input so I check both table fields against their input. On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux 5.1.54), 'US' appears in the results. Is there a better way to write this? Thanks. -- Paul Halliday http://www.squertprojec

Substring confusion.

| 4 03:00 | | 6 | 4 03:01 | | 1 | 4 03:02 | | 2 | 4 03:03 | +---+-+ Why is this? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Design question.

ist1|list2|list9|list20 and then just breaking it out in the code. The entire table would of course need to be scanned each day to check whether or not an address had been taken off a list. (efficiency?) Any comments/suggestions would be greatly appreciated. Thanks. -- Paul Halliday http://www.pint

Help with query.

ourse, but I am just curious if I can pull it off with a single query. Thanks! -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Lowest non-zero number

On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodge wrote: > Given a table containing a range of INT values, is there any easy way to > select from it the lowest non-zero number? > SELECT number FROM table WHERE number > 0 ORDER BY number ASC LIMIT 1; ? -- Paul Halliday Ideation | Indiv

Re: Missing results.

> Presumably those records were absorbed into your 'group by' clause, since > there was an entry, from a later time, which had the same values for all the > group by fields. > > > -- > Simcha Younger > Geez, how obvious. I was thinking on a completely different plane. I feel pretty silly now. :

Missing results.

2010-11-03 13:18:37 Where did 13:41:46, 13:19:20 and 13:22:39 go? Thanks! -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Can this query be done w/o adding another column?

nd m2.ip). > > > > -Travis > > > > From: Paul Halliday [mailto:paul.halli...@gmail.com] > Sent: Tuesday, October 12, 2010 11:37 AM > To: Travis Ard > Cc: mysql@lists.mysql.com > Subject: Re: Can this query be done w/o adding another column? > > > > On

Re: Can this query be done w/o adding another column?

On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard wrote: > You could join your mappings table twice, once on src_ip and again on > dst_ip: > > SELECT COUNT(signature) AS count, >MAX(timestamp) AS maxTime, > INET_NTOA(src_ip), >m.cc as src_cc, > INET_NTOA(dst_ip), > m2.

Can this query be done w/o adding another column?

mappings table to have src_ip and dst_ip just duplicating the ip column but I have a nagging feeling that that probably isn't necessary. Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org

Re: Can this be done with a single query?

On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers wrote: > I would try: > > SELECT DISTINCT(e.src_ip) > FROM event AS e > LEFT JOIN mappings AS m ON e.src_ip=m.src_ip > WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' > AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%' > AND INE

Can this be done with a single query?

2.16.%.%' AND INET_NTOA(src_ip) NOT LIKE '192.168.%.%'; but, within that somewhere also check to see if src_ip exists in mappings. If it does, do not return it. Is this possible? Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org

Is conversion required?

mysql to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? Thanks! -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org

Re: Join on a where clause.

On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe wrote: > Hi everybody! > > > Neil Aggarwal wrote: >> Paul: >> >>> SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), >>> mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN >>> '2009-12-06 20:00' and '2009-12-07 20:00:00' AND event

Join on a where clause.

I have 2 tables: 1) Event Data 2) Mappings The query should return something like this: Hits IP Country Code 20213.136.52.29 SE I am trying this: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE event.tim

Table advice.

I have a database that I am (will) be using to track URL's. The table structure looks like this: CREATE TABLE event ( eid INT UNSIGNED NOT NULL AUTO_INCREMENT, timestamp INT(10) UNSIGNED NOT NULL DEFAULT 0, ipINT(10) UNSIGNED NOT NULL DEFAULT 0, fqdn VARC

open_files_limit problem.

I am trying to change this value and it doesn't seem to work. Looking at the processes I have: mysql 21752 0.0 0.1 1652 1092 p3 I 3:50PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysq

Re: Strange query.

2 - 0 3 - 6 Is this possible? On 10 Jan 2007 19:55:09 -, Felix Geerinckx <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] ("Paul Halliday") wrote in news:[EMAIL PROTECTED]: > I am trying to deal with empty values so that I can graph data over a > 24 hour period without g

Re: Strange query.

ERE timestamp BETWEEN '2007-01-09 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour - Original Message - From: "Paul Halliday" <[EMAIL PROTECTED]> To: Sent: Wednesday, January 10, 2007 8:39 AM Subject: Strange query. > Hi, > > I am

Strange query.

Hi, I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. I created a table called HOURS which simply has 0->23 and I am trying to do a join on this to produce the desired results. I think that the DATE_FORMAT in the query is screwing things up. The

Re: If exists query.

On 4/23/06, John Hicks <[EMAIL PROTECTED]> wrote: > Paul Halliday wrote: > > I am doing queries that produce a table that looks something like this: > > > > Count | IP Address| First Seen| Last Seen | Days > > 5000 10.0.0.1 2005-12-1

Re: If exists query.

ECTED]> wrote: > Paul Halliday wrote: > > I am trying to formulate a query that will increment if a condition is true. > > > > For example, if I do a select (just let me know if there was data on > > this day, if so increment count by 1 and check the next day) where >

If exists query.

I am trying to formulate a query that will increment if a condition is true. For example, if I do a select (just let me know if there was data on this day, if so increment count by 1 and check the next day) where timestamp between jan and feb. Thanks. -- MySQL General Mailing List For list archi

Re: Can't search words of three letters?

Why not try regex? http://dev.mysql.com/doc/refman/4.1/en/regexp.html On 4/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi to all, > I have to build a search of products on web catalog site. It has to search > a whole words to avoid when somebody search for a pin and get "*pin*k > shirt"

Insuring select returns the last record for a given day.

Hi, I have a table that looks somthing like this: ID timestampcampusIDS ePOinbound outbound statinfo 2289411143430287MA0 0424526713 284590944 0 NULL 2289401143430002ST 2 02902485

Re: Merge tables.

On 3/14/06, Martijn Tonies <[EMAIL PROTECTED]> wrote: > Hello Paul, > > I suggest you reply to the mailinglist :-) ... > > > The developer insists that for scalability issues, this was the > > answer. It is likely, for example in my deployment, that these tables > > would see upwards of 10 million

Merge tables.

Hi, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 3

problem with excluded days in date range query.

I have the following query: select count(*) as cnt, date(timestamp) as day from table where date_sub(curdate(),interval 14 day) <= timestamp group by day; If there are no data for a particular day, it is not included. Is there a way to include all days even if the result is 0? I saw an example th

Re: Conditional insert or update.

Unfortunately they're available only in MySQL 5.0.x, which is still > > beta. > > > > > > Paul Halliday <[EMAIL PROTECTED]> wrote: > > > Hi,=20 > > > > > > I have a table that looks something like this: > > > > >

Conditional insert or update.

, I would also like to roll the values too. For example if the MAC changes again, put mac_change into mac_current... Thanks. -- Paul Halliday "Diplomacy is the art of saying "Nice doggie!" till you can find a rock." -- MySQL General Mailing List For list archives: http://l

Solution to slow queries (Resolved, kinda)

scripts are just doing selects, no updates, but there is the possibility that the program which populates the db was running at the same time too. Is there a way to force the table drop? (Without adding checks to the shell script) Thanks. _ Paul Halliday http://dp.penix.org

Re: Solution to slow queries

On 5/10/05, Roger Baklund <[EMAIL PROTECTED]> wrote: > Paul Halliday wrote: > > srcaddr VARCHAR(15), > > dstaddr VARCHAR(15), > > Are these ip-adresses? If they are, consider using UNSIGNED INT columns > and the INET_NTOA() and INET_ATON() funti

Solution to slow queries

probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? Any thoughts, or a pointer in the right direction would be greatly appreciated. Thanks. -- _ Paul Halliday http://dp.penix.org "Diplomacy is the a