you'll need to write a recursive routine to call the same sql statement
and iterate from the largest value and attenuate that value by 1 each time
# file: test.pl been a few years but this should work
#initialize your variable that you will attenuate
my $global = 9876543210;
#number of rows
Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items
the tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
mysql SELECT DISTINCT items.title from items inner join taggings on
(items.id = taggings.item_id) inner join tags on (tags.id =
Ingo Weiss schrieb:
Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items the
tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
oh ... and ..., i missred
SELECT DISTINCT items.*
FROM items
INNER JOIN taggings
ON
Ingo Weiss schrieb:
Hi all,
I have an application where items can be tagged. There are three tables
'items', 'taggings' and 'tags' joined together like this:
items inner join taggings on (items.id = taggings.item_id) inner join
tags on (tags.id = taggings.tag_id)
Now I have been
Is it just this line I need to change?
INNER JOIN url_categories uc ON uc.ID=bt.category_ID;
Would it change to something like:
INNER JOIN url_categories uc ON CAST(uc.ID as CHAR)=delimit(bt.category_ID)
Just guessing!
Thanks - that's what I thought. I really don't have much experience with
Thanks, that's interesting. Actually the uc.ID column is still type tinyint
as it holds only one number, but are you saying if I change this to varchar
my query will work e.g. 15 = 15:17 would work?
What is the type of the 'uc.ID' column? If it's varchar, your match
will work fine. If
Thanks - that's what I thought. I really don't have much experience with
mySQL. If it's not too much trouble, could someone give me a bit more help
on how to do that please?
Ed.
no, those won't match based on just the datatype change.. you will
have to define a user defined function to
Thanks Shawn,
Believe you me, I share your reaction to this architecture...I had to spend
2 hours coding a ruby script to get the data into the kludgy form needed for
the data import (though I do find that thing kind of fun...but it's not the
best use of my time on the job). Fortunately the
eth1 [EMAIL PROTECTED] wrote on 03/28/2006 03:04:13 PM:
Hi All,
I'm migrating to a contact relationship management system (CRM) for
one of
my clients from a proprietary Access database. The CRM system can
import
our donor's contact history, but only in a non-normalized format with up
Hello.
Do you want something similare to this:
SELECT SUM(IF(moving like 'Move-',-moved_quantities,moved_quantites))
FROM DB;
Have a look here:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
Mester József wrote:
Hy
I want to sum quantites but there
Hy
If you know which values are supposed to be negative, wouldn't it be
easier to do updates to your data to change all of those values to
negatives? That should only need to be done once. Then use the normal
SQL sum() function to add all of the values together.
Thank you. Actually my first
- Original Message -
From: Mester József [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com
Sent: Friday, January 06, 2006 12:07 PM
Subject: Re: SQL Question
Hy
If you know which values are supposed to be negative, wouldn't it be
easier to do updates
.
mysql SELECT ABS(2);
- 2
mysql SELECT ABS(-32);
- 32
This function is safe to use with BIGINT values.
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Friday, January 06, 2006 12:19 PM
To: Mester József; mysql
Subject: [SPAM] - Re: SQL Question - Bayesian
.
mysql SELECT ABS(2);
- 2
mysql SELECT ABS(-32);
- 32
This function is safe to use with BIGINT values.
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Friday, January 06, 2006 12:19 PM
To: Mester József; mysql
Subject: [SPAM] - Re: SQL Question - Bayesian
Hi All!
I have a MySQL database (I have them using MySql at work for more stuff
now!), and the definition is as follows:
uid mediumint(6) NOT NULL auto_increment,
ym varchar(6) default NULL,
fileid varchar(8) default NULL,
off char(3) default NULL,
PRIMARY KEY (`uid`)
TYPE=MyISAM
uid is not
At 11:08 AM 11/29/2004, you wrote:
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called
random_position. Currently I am creating a position list (based on the count
of the members), shuffle it, then while iterating through the
On 11/29/04 12:27 PM, mos [EMAIL PROTECTED] wrote:
Mike,
Your solution is way too complicated (it makes my head hurt).g
Try this:
set @n=0;
update tmp set rnd = @n := @n + 1 order by RAND()
Mike
I'll give this a shot. Follow-up question:
I've had a lot of trouble with RAND() on
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, November 29, 2004 12:08 PM
Subject: SQL question Trying to improve upon my PHP solution.
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a
On 11/29/04 1:26 PM, Rhino [EMAIL PROTECTED] wrote:
I don't think this is a question about SQL at all; I think you already know
how to write the SQL to select, insert, update or delete rows.
I think that what you really want to know is if there is a more efficient
way to shuffle your rows
At 11:53 AM 11/29/2004, you wrote:
On 11/29/04 12:27 PM, mos [EMAIL PROTECTED] wrote:
Mike,
Your solution is way too complicated (it makes my head hurt).g
Try this:
set @n=0;
update tmp set rnd = @n := @n + 1 order by RAND()
Mike
I'll give this a shot. Follow-up question:
I've had a
I had a similar problem, but my criteria for selecting the
value of f1 was different; it's a date field and I wanted
only the rows with the most recent date value in that field,
so only the latest of otherwise identical entries got inserted.
I ended up doing something like this:
create temporary
How about
INSERT INTO original_table
SELECT MAX(f1), f2, f3 FROM new_table GROUP BY f2, f3;
Michael
Stephen E. Bacher wrote:
I had a similar problem, but my criteria for selecting the
value of f1 was different; it's a date field and I wanted
only the rows with the most recent date value in
It all depends on which values of f1 you want to ignore.
f1 f2 f3
- - --
val1-1 val2 val3
val1-2 val2 val3
val1-3 val2 val3
Which value of f1 would you want in your new table? Which ones to ignore?
Are there other columns (beyond these 3) to move as well?
On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said:
It all depends on which values of f1 you want to ignore.
f1 f2 f3
- - --
val1-1 val2 val3
val1-2 val2 val3
val1-3 val2 val3
Which value of f1 would you want in your new table? Which ones to
Let me see if I can explain it a little betterIf you need to move all
3 columns to the new table but you only want *1* row where f2 and f3 have
a unique combination of values, how do you want to choose *which* value of
f1 to move over with that combination? Do you want the minimum value,
On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said:
Let me see if I can explain it a little betterIf you need to move all
3 columns to the new table but you only want *1* row where f2 and f3 have
a unique combination of values, how do you want to choose *which* value
of
f1 to
You were perfectly clear. We understand that you only want to test f2 and
f3 for uniqueness. The question is, which of the possible values of f1 do
you want to get. Do you see? For a particular unique f2, f3 combination,
there may be multiple f1 values. How should we choose which one to
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen
[EMAIL PROTECTED] said:
You were perfectly clear. We understand that you only want to test f2
and f3 for uniqueness. The question is, which of the possible values
of f1 do you want to get. Do you see? For a particular unique f2, f3
Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target
table, leave it out of the SELECT, and let it auto-generate IDs. Something
like this:
INSERT INTO original_table (f2, f3)
SELECT DISTINCT f2, f3 FROM new_table;
I did that in the same order as your original message,
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen
[EMAIL PROTECTED] said:
You were perfectly clear. We understand that you only want to test f2
and f3 for uniqueness. The question is, which of the possible values
of f1 do you want to get. Do you see? For a particular unique f2, f3
Disregard by last message it's a repeat. THANKS for the help!
On Mon, 16 Aug 2004 14:32:27 -0400, Michael Stassen
[EMAIL PROTECTED] said:
Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target
table, leave it out of the SELECT, and let it auto-generate IDs.
Something
Maybe something like:
Select LIKE pkg_name%, LIKE site_%, version from table group by LIKE
pkg_name% , LIKE site_%;
but I'm still a beginner.
Respectfully,
Ligaya Turmelle
[EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello all,
I've a table like this :
site_1 pkg_name_1
row | foo
1 | a
2 | c
3 | b
4 | c
5 | a
6 | d
the statement would return me rows 1, 2, 4, and 5.
CREATE TEMPORARY TABLE temptable SELECT *
FROM test
GROUP BY foo
HAVING COUNT( * ) 1
ORDER BY foo ASC ;
SELECT *
FROM test, temptable
WHERE test.foo = temptable.foo
ORDER BY
Keith Schuster [EMAIL PROTECTED] wrote:
Mysql 3..
I can't figure this one out
I need to move data from one mysql table to another
The hurdle for me is adding additional column values.
Here is what I have.
insert into mytable (column1, column 2, column3)
(Select thiscolumn
From
you want to do
insert into mytable (column1, column2, column3)
(select thiscolumn, '1', now() from anotherTable);
Mysql 3..
I can't figure this one out
I need to move data from one mysql table to another
The hurdle for me is adding additional column values.
Here is what I
At 12:29 -0500 7/18/03, Tom O'Neill (MySQL User) wrote:
Hi,
Is there a way I can run a query that will delete all items that are not in
a list? For example I have a bunch of records in a table and I want to
remove all of them that are not in a comma delimited list that I have
recieved from
Jake Johnson wrote:
This is one quick way to get the newest records of a group if you are
grouping by the sku and stock.
select stock, sku, qty
from table
where concat(dt_tm,stock,sku) IN (
select concat(max(dt_tm), stock, sku)
from table
group by stock, sku
)
Another approach (also assuming a
Nice approach Bruce, but I too won't have any problems with your
case because I am grouping by sku and stock in the sub-query.
Regards,
Jake Johnson
[EMAIL PROTECTED]
--
Plutoid - http://www.plutoid.com
Shop Plutoid for the best
Jake Johnson wrote:
Nice approach Bruce, but I too won't have any problems with your
case because I am grouping by sku and stock in the sub-query.
You're right; you do avoid the problem with the specific sample data I
gave you. Sorry about that! But, there are still potential problems
because
This is one quick way to get the newest records of a group if you are
grouping by the sku and stock.
select stock, sku, qty
from table
where concat(dt_tm,stock,sku) IN (
select concat(max(dt_tm), stock, sku)
from table
group by stock, sku
)
Regards,
Jake Johnson
[EMAIL PROTECTED]
Luca,
In Oracle I could create a view from the initial table, what about MySQL?
MySQL will support views as of version 5.1. I cannot find it in the
todo (http://www.mysql.com/doc/en/TODO.html) but I saw it elsewhere.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
iConnect GmbH
I have 2 tables in our MySQL database like this:
TABLE: customers
+--+---+--+-+-+-
---+
| Field| Type | Null | Key | Default |
Extra |
Fax: +49 30 7970948-3
- Original Message -
From: Darren Young [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, December 16, 2002 11:24 PM
Subject: RE: SQL Question
I have 2 tables in our MySQL database like this:
TABLE: customers
On 16 Dec 2002, at 23:50, Stefan Hinz, iConnect (Berlin wrote:
I need to construct a query to find out what customers in the database
have not
booked shipments with us. That means there would be no
records in the shipment table for a given customer id.
If I get this right, it should
John,
Tuesday, November 05, 2002, 1:50:32 AM, you wrote:
JJ I have a callers table and a citylist table. Both tables have a field
JJ 'town' and both tables have a field called 'zipcode'.
JJ The citylist is a list of cities and their zip codes.
JJ citylist.city and citylist.zipcode
JJ The
Try:
SELECT fieldname FROM table ORDER BY 0+fieldname;
Regards
John Almberg wrote:
That gives a syntax error, unfortunately.
-- JOhn
-Original Message-
From: Mihail Manolov [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 26, 2002 9:51 PM
To: Mysql
Subject: Re: SQL question
To: Mysql
Subject: Re: SQL question
Try:
SELECT fieldname FROM table ORDER 0+fieldname;
Hope it helps.
Mihail
- Original Message -
From: John Almberg [EMAIL PROTECTED]
To: Mysql [EMAIL PROTECTED]
Sent: Thursday, September 26, 2002 8:04 PM
Subject: SQL question
I'm trying to sort
I don't know that there is knowing the way sorts usually work I'd say no.
This is my first posting to this group and I am just in the initial phases
of supporting MySQL as a backend for out application.
If the numbers always precede the letters I would split them off into two
separate columns
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 27, 2002 9:52 AM
To: John Almberg
Cc: Mysql
Subject: Re: SQL question
ORDER BY 0 + fieldname
if this is not quit right try
ORDER BY 0 + fieldname , fieldname
John Almberg wrote
I just tried it and it works fine. It doesn't solve my similar problem,
which is related to letters coming *before* numbers, but the parser doesn't
reject it.
Wish I could offer a solution, but I'm not very knowledgable about MySQL
internals config.
-Derek
-Original Message-
From:
of mask.
BobJ
- Original Message -
From: William McCormick [EMAIL PROTECTED]
To: John Almberg [EMAIL PROTECTED]; Mysql [EMAIL PROTECTED]
Sent: Friday, September 27, 2002 10:08 AM
Subject: RE: SQL question
I don't know that there is knowing the way sorts usually work I'd say no.
This is my
Try:
SELECT fieldname FROM table ORDER 0+fieldname;
Hope it helps.
Mihail
- Original Message -
From: John Almberg [EMAIL PROTECTED]
To: Mysql [EMAIL PROTECTED]
Sent: Thursday, September 26, 2002 8:04 PM
Subject: SQL question
I'm trying to sort a table on a character-type field
create table newTableOne
select A.name as groupA, B.name as groupB, C.name as groupC
from tableOne, tableTwo as A, tableTwo as B, tableTwo as C
where tableOne.groupA = A.ref_id
and tableOne.groupB = B.ref_id
and tableOne.groupC = C.ref_id;
Then re-add your indices and integrity constraints,
Calculating a connectivity matrix from this sort of data is not what the
relational database is best designed to do, but it's a common problem - it
applies to family tree databases, used-on lists and so on. (What you're
actually wanting to do, if I remember the jargon correctly from school, is
Thanks, My book that I have been studying from, only show commands and no
examples. MySQL web had want I wanted at the very top.
I have finish my page now.
Chuck
on 4/6/02 11:46 PM, Georg Richter at [EMAIL PROTECTED] wrote:
On Sunday, 7. April 2002 05:56, Chuck \PUP\ Payne wrote:
Hi,
I
: SQL Question...
Thanks, My book that I have been studying from, only show commands and no
examples. MySQL web had want I wanted at the very top.
I have finish my page now.
Chuck
on 4/6/02 11:46 PM, Georg Richter at [EMAIL PROTECTED] wrote:
On Sunday, 7. April 2002 05:56, Chuck \PUP
On Sunday, 7. April 2002 05:56, Chuck \PUP\ Payne wrote:
Hi,
I am trying to set up a SQL statement using NOW(), what I am wanting to do
is to use NOW() then do a search on any date less than 7 days. Before I get
several e-mails asking why. I am trying to a news base database for the
company
On Sat, 06 Apr 2002 22:56:11 -0500, Chuck \PUP\ Payne wrote:
Hello again,
I am trying to set up a SQL statement using NOW(), what I am wanting
to do
is to use NOW() then do a search on any date less than 7 days.
Before I get
several e-mails asking why. I am trying to a news base database for
the
I asked almost the exact same question just a few days ago.
As of MySQL 3.23.2 you can use COUNT and DISTINCT together:
SELECT COUNT(DISTINCT ipAddress) ...
On 9/3/02 at 1:16 pm, Jeff Kilbride [EMAIL PROTECTED] wrote:
I have a table with 3 fields:
initDate datetime not null
id int
Aha! Figured it out...
SELECT COUNT(DISTINCT ipAddress)
FROM table
WHERE initDate = DATE_ADD(now(), INTERVAL -60 MINUTE)
AND id = [whatever id I'm looking for...];
Works semantically like it sounds. Probably the only variation I didn't try
before posting! (always works that way...)
Thanks,
Thanks, Rob. Yeah, I just figured it out myself. I think I convinced myself
that it couldn't be that easy!
--jeff
- Original Message -
From: Rob [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Sent: Saturday, March 09, 2002 1:26 PM
Subject: Re: SQL
At 9:57 PM -0400 9/4/01, Lance Rochelle wrote:
SQL question which I am new to.
How would I count the number of times a specific entry is in field. For
instance I have a table that has the following two fields
numberhostname
1 10.1.1.1
2 10.1.1.2
3
SQL question which I am new to.
How would I count the number of times a specific entry is in field. For
instance I have a table that has the following two fields
numberhostname
1 10.1.1.1
2 10.1.1.2
3 10.1.1.3
4
select count(hostname), hostName from tableName group by hostname
Cal
http://www.calevans.com
-Original Message-
From: Lance Rochelle [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 04, 2001 8:57 PM
To: [EMAIL PROTECTED]
Subject: SQL question
SQL question which I am new to.
How
From: Lance Rochelle [EMAIL PROTECTED]
SQL question which I am new to.
How would I count the number of times a specific entry is in field. For
instance I have a table that has the following two fields
select count(*) as number, hostname
from your_table
group by hostname;
---
Rodney
Hi,
Could you create a linked list of the links- i.e. have a 'Next' field,
which contains the ID of the next URL in order. You will also need a
field to store if the link is the first (i.e. root) of the list. Then
you can just iterate though the rows, using the next field to tell you
which URL
Dave Rigby wrote:
Hi,
Could you create a linked list of the links- i.e. have a 'Next' field,
which contains the ID of the next URL in order. You will also need a
field to store if the link is the first (i.e. root) of the list. Then
you can just iterate though the rows, using the next
Marie-Christine Fauvet [EMAIL PROTECTED] wrote:
I'd like to program in the SQL language supported by MySQL the following
query:
select R.A, R.B from R
minus
select S.A, S.B from S
It doesn't work (syntax error, set operators are not supported).
So, I've tried:
select R.A, R.B from
I'm having a lot of difficulty trying to figure this out. I have a table
with a list of projects that I would like to arrange and view as a tree.
This is my table:
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra
I don't think it's possible purely with SQL using MySQL. I tried researching
it just a few days ago, and found Oracle supports a clause CONNECT BY ...
PRIOR (more info at http://www.arsdigita.com/books/sql/trees.html) which
does this for you, but it's still in the MySQL todo list
70 matches
Mail list logo
