I'll check the site mentioned, thank-you so much!
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
PB/
-
/
Ben A. Hilleli wrote:
So you are talking about parent-child relationships in a single table,
or in the technical jargon
reflexive
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 17, 2008 5:26 PM
To: Stut; mysql@lists.mysql.com
|
|5 | 17 |
+--+--+
PB
US Data Export wrote:
Well, 5.x accepted the query. It's been running for awhile, now, so I'll
find out later if it did what I need.
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 08, 2008 5:25 PM
Rob,
I need the distinct, lowest values that relate to each other,
not necessarily in the same row.
First, the data representation does not appear to represent the problem
adequately. Second, I do not understand what relate to each other, nor
necessarily in the same row means. Third, I do not
Is there any terminal display program in Linux that
has a horizontal scroll bar that I can use?
TheUsual/PHP (http://www.artfulsoftware.com/theusualReadMe.html) do and
phpMyAdmin do that in web interfaces.
PB
Alex Katebi wrote:
Hi,
When selecting (example: select * from mysql.db ) some
I'm looking at using the @ symbol
Don't. Restrict yourself to alphanums and '_'.
PB
Res wrote:
Hi All,
Does anyone have a reference to what is regarded a legal valid chars
for the MySQL database username?
You can imagine what google shows me, everything totally irrelevant, as
usual. The
Is there any elegant way of finding the gaps?
You'll find some ideas under (and near) Find missing numbers in a
sequence at http://www.artfulsoftware.com/infotree/queries.php.
PB
-
Stut wrote:
On 17 Sep 2008, at 22:12, Jerry Schwartz wrote:
I have records that should be sequentially
Thanks, but is there any technical reason where using @ might break
something?
However if there's even the slightest risk of an implosion, naturally
we will not.
Eh? Did you read the manual page on identifiers?
PB
Res wrote:
Hi Peter,
On Wed, 17 Sep 2008, Peter Brawley wrote:
I'm looking
How do you escape a dash in a table name such as temp-08-08-28?
Best not to use them at all. If you must for some odd reason, use
backticks round the name.
PB
Dan O'Keefe wrote:
How do you escape a dash in a table name such as temp-08-08-28?
Thanks,
Dan
--
MySQL General Mailing List
David,
My goal is to create a report, that lists the Top 100 most expensive
BookNames, for every CategoryId in this table.
I think you can map the example under Within-group quotas (Top N per
group) at http://www.artfulsoftware.com/infotree/queries.php to your
requirement.
PB
David
1) SELECT N questions and the related answers from each category.
See Within-group quotas (Top N per group) at
http://www.artfulsoftware.com/infotree/queries.php.
PB
Kevin Waterson wrote:
I have 3 tables (schema below) with categories, questions and answers.
Each category can of course
wrote:
This one time, at band camp, Peter Brawley [EMAIL PROTECTED] wrote:
See Within-group quotas (Top N per group) at
http://www.artfulsoftware.com/infotree/queries.php.
Yes, I have seen that, very clever. How does it relate to my situation?
Simply point to vague references
quota selection
principle applies to top or any other quota per group so long as the
selection criterion can be written in valid SQL. Have a nice day.
PB
-
Kevin Waterson wrote:
This one time, at band camp, Peter Brawley [EMAIL PROTECTED] wrote:
Vague? Not in the slightest. General
how to Get file modified time and date of file by using builtin
function or procedure in sql?
On Codd's rules, it oughtn't to be possible---it'd be a backdoor. If
there is a need to know the datetime of the last mod to a table, that
info ought to be in a column in a table.
PB
--
MySQL
to get the latest value for each id i have queries like:
select * from tab A where timestamp = (select max(timestamp) from tab B where
B.id=A.id) group by id ;
See Within-group aggregates at
http://www.artfulsoftware.com/infotree/queries.php.
PB
walter harms wrote:
hi list,
i have
A text field -Lets call it 'field1'- contains datas seperated by
commas(,)
like this (123,5764,8795,9364,11,232,.
The solution is to normalise the data.
PB
Ali Deniz EREN wrote:
Hi all,
I have a problem as below:
A text field -Lets call it 'field1'- contains datas seperated by
Craig,
Is there any simple way to create a calendar table?
See Make a calendar table at
http://www.artfulsoftware.com/infotree/queries.php.
PB
Weston, Craig (OFT) wrote:
Hi there,
Is there any simple way to create a calendar table? For example I want
to create a reference calendar
Jeff,
Table2.ticket = table1.ID
Table2 is a many to 1 relationship to table1
I need to delete all records from table1 where created
unix_timestamp(date_sub(now(), interval 3 month))
And all rows from table2 where Table2.ticket = Table1.ID
(of the deleted rows..)
Like this (untested)?
.
Thank you.
Tina
Peter Brawley wrote, On 6/26/08 6:11 PM:
Tina,
Even if I do this simple query, while hardcoding in a catalog_number:
SELECT subject, catalog_number FROM course_subject
WHERE (catalog_number = 520) AND
((subject = 'ME') OR ((course_offer_number = 1) AND (subject NOT
LIKE 'ME
What I want is ONLY the 'ME' row (if a row exists with a subject of
'ME').
If an 'ME' subject row does not exist, then I want the other one.
Ill be offline for awhile so I'll assume answers not available, ie allow
='ME' dupes and 'ME' dupes if they exist. A one-query answer is to
union (i)
Tina
Basically, if the subject is ME, then I want to select that row.
If there is no row for that catalog_number that has a subject of ME,
then I want to grab the row that has a course_offer_number of '1'
and a subject that is not equal to ME.
Is this what you mean?
SELECT ...
FROM
pulled
all of the rows. So I've been trying to come up with another solution.
Any other ideas?
Thanks for the reply.
Tina
Peter Brawley wrote, On 6/26/08 2:12 PM:
Tina
Basically, if the subject is ME, then I want to select that row.
If there is no row for that catalog_number that has
) AND
((subject = 'ME') OR ((course_offer_number = 1) AND (subject NOT LIKE
'ME')))
I still get two rows back:
subjectcatalog_number
ME520
MSE520
So I'm not sure what else I need in my Where Clause
Thanks bunches.
Tina
Peter Brawley wrote, On 6/26/08 3:33 PM:
Tina,
for some
Kip,
What can I do to optimize this query?
For more efficient alternatives see Within-group aggregates at
http://www.artfulsoftware.com/queries.php.
PB
-
Kip Turk wrote:
I'm having problems optimizing a series of subselects. I have the
following sample table:
mysql select * from
David,
What I am trying to limit this query to is the top 100 details ordered by
SUM(Volume) DESC for each unique LongDescription
For some solutions see 'Within-group quotas (Top N per group)' at
http://www.artfulsoftware.com/infotree/queries.php
PB
-
David Perron wrote:
Hello MySQL
userId long
picture MeduimBlob
datePosted DateTime
A userId can have many pictures posted. I want to write a
query that returns a distinct userId along with the most
recent picture posted. Can someone suggest an elegant and
fast query to accomplish this?
Latest pic for user N:
SELECT
CK,
How can we manage the history of changed rows in the database.
Point-in-time architecture. For a bit of discussion see
http://www.artfulsoftware.com/infotree/tip.php?id=547
PB
-
C K wrote:
Hi all.
How can we manage the history of changed rows in the database. I have some
idea
Jonathan,
Jake provided useful suggestions answered your question. For
elaboration of Jake's tip, see the manual page for GROUP BY for how
joins affect grouping.
PB
-
Jonathan Mangin wrote:
- Original Message - From: Jake Peavy [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL
Phil,
If in the 2nd query you want teams with the highest count per cpid found
in the first query, I think you can map the 'Avoiding repeat
aggregation' pattern
(http://www.artfulsoftware.com/infotree/queries.php) to your problem
PB
-
Phil wrote:
Hi all,
got a simple problem I'm
i'm not trying to make trouble or bother someone with this
information
Misinformation---the first five items I found in this article make false
claims:
1.
CREATE TABLE creature (name VARCHAR(3) NOT NULL);
INSERT INTO creature SET name = 'caterpillar';
Query OK, 1 row affected, 1 warning
Andrew,
I have successfully install mysql and is is running in the background
as a service ( I believe) as in msconfig lists it in the services. Is
there something I should see in the task bar down at the bottom.
A Windows service is not a task. If Windows services were to show as taskbar
Velen,
My problem is that it is displaying a.sale_id but different customer_name
as it is taking sale_id from d and matching cust_code with b
Any non-aggregate SELECTed value that does not have a 1:1 relationship
with your GROUP BY column will show arbitrary results, so the first
thing to get
Laurent,
I'd like to be able to track changes made to the attribute of one record,
without wanting to duplicate the complete record each time . How an I
achieve this?
It often turns out that trying to make a SQL-level audit trail of such
changes is more expensive in time and code than a
Kelly,
I'm not married to using SQL: are there other efficient solutions to
store directed graphs? Could I hack something up in Perl or Ruby and
then serialize my in-memory graph to a file (for efficient
saving/reloading)?
Did you look at Dijkstra's algorithm?
PB
--
MySQL General Mailing
//How would you implement this in PL/SQL ?
Have a look at
http://hansolav.net/blog/ImplementingDijkstrasAlgorithmUsingTSQL.aspx?
PB
[EMAIL PROTECTED] wrote:
Good Evening Peter-
//so if I look at this algorithm from wikopedia I see something like
//Java/C#/C++ no problem
//How would you
Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count
FROM login_table b WHERE a.username = b.username) FROM user_list a
Try ...
SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
FROM user_list a
JOIN login_table
not on the liste and if the user
has logged in more than once the result is 1 (because of the group by
...).
Thankyou
Peter Brawley a écrit :
Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS
count
FROM login_table b WHERE a.username
Yves
Is there some way to get only the headlines ...
For brief discussion some examples see 'The [Not] Exists query
pattern' at http://www.artfulsoftware.com/infotree/queries.php.
PB
Yves Goergen wrote:
On 11.02.2008 19:51 CE(S)T, Perrin Harkins wrote:
On Feb 10, 2008 5:30 PM, Yves
which implies there are positive values which provide access, but your
original query used the condition
readaccesskeylist /is not null/
as a test for access /refusal/, which seems to contradict what you now say.
PB
-
Yves Goergen wrote:
On 11.02.2008 17:32 CE(S)T, Peter Brawley wrote
:18 CE(S)T, Peter Brawley wrote:
Unclear.
Okay. Then the long form.
My application is a messaging application that supports multiple
users, messages with revisions, tags and access control.
A user is identified by a UserId which I also call key. (Imagine it
like the key you have for your
;
+---+--+--+--+
| TableName | i| j| k|
+---+--+--+--+
| Table a |3 | 30 | 300 |
| Table b |3 | 30 | 301 |
+---+--+--+--+
PB
-
James Eaton wrote:
From: Peter Brawley
I'd like to run a query to find the records that
are present
James
I'd like to run a query to find the records that
are present in one database but not the other.
See 'Compare data in two tables' at
http://www.artfulsoftware.com/infotree/queries.php.
PB
James Eaton wrote:
I have two different databases on the same 5.0 server that have the
same
Yves
it will rather find messages that have no tag with a keylist
which does not include the currently logged in user's UserId
or one of this user's additional keys, which are again stored
in a keylist.
Unclear.
PB
Yves Goergen wrote:
On 11.02.2008 00:29 CE(S)T, Peter Brawley wrote:
message
Yves,
My problem is that the sub-select in line 7
(SELECT 1) takes a rather long time
It might be possible to simplify. Do I have the schema right?
message (messageID)
keylist (keylistID)
tag ( tagID, readaccesskeylist references keylist(keylistID) )
message_revision_tag ( ???, messageID
Magne,
I want to create a stored procedure that runs a query using the IN
operator ...
See 'Variable-length argument for query IN() clause' at
http://www.artfulsoftware.com/queries.php
PB
-
Magne Westlie wrote:
Dear List,
I want to create a stored procedure that runs a query using
.
Also, I was moving the query into a stored procedure because I wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE
Andrew,
I have a dbase3 database that I would like to convert to mysql.
A dBASE III database isn't a database but rather a single ASCII
fixed-field-length data table with a binary header describing data
layout. I think these are your main choices: (i) if you have an xbase
product on hand
Postgres has PostGIS
Oracle has OracleSpatial
MySQL has ?
OpenGis.
PB
Martin Gainty wrote:
Michael-
I can certainly understand the upgrade based on the performance advantages
of MySQL
What would NASA use for its mapping GIS/Mapping software?
Postgres has PostGIS
Oracle has OracleSpatial
Is there anyway to combine these 2 queries into 1?
Is this what you mean?
-- list all prods-cats
SELECT p.prod_id, c.category
FROM product_table p
LEFT JOIN catproduct_table cp ON cp.product_id = p.product_id
JOIN product_table p ON p.product_id = cp.product_id;
... or this ...
-- group cats
Malki,
which gives the error:
ERROR 1054 (42S22): Unknown column 'depts.dept_code' in 'on clause'
See the JOINs page of the manual. As of 5.0.12, MySQL improved ISO SQL
compliance in query parsing; ambiguities due to comma join syntax became
errors. It's highly recommended to switch from
Does that mean what I did should have worked?
I'd want to know (i) the result of executing those cmds in a mysql
client, and (ii) what sqlresult acountinfo contain after each cmd.
PB
[EMAIL PROTECTED] wrote:
Thank you for your response. I am using InnoDB (picked that out of the docs).
I would like to wrap my updates top MySQL in transactions.
Use InnoDB tables.
PB
[EMAIL PROTECTED] wrote:
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the
Anders,
I also want to find out the user's position relative to others
depending on the result.
For a given pUserID, something like this?
SELECT userid,result,rank
FROM (
SELECT o1.userid,o1.result,COUNT(o2.result) AS rank
FROM object o1
JOIN object o2 ON o1.result o2.result OR
Marcus,
I've managed to do this with a Perl-DBI script, but
would much prefer to do it completely with MySQL instead.
You could port it to a recursive stored procedure. It would probably be
slower, and what would you have gained?
PB
Marcus Claesson wrote:
Hi!
I have a SQL query
Anoop,
It's an edge list tree, so unless you can specify max recursion depth,
you need an sproc. See listing 7c at
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
PB
Anoop kumar V wrote:
The resultant table should be this:
+--+---+--+
| id |
I tried this but it is not working. I'm not very
familiar with subqueries as you can see.
insert into table_2 ( id, value ) values ( (select id from table_1), '1' );
insert into table_2 (id,value)
select id,1 from table_1;
PB
Ben Wiechman wrote:
I need help writing what is probably a
Marc
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or
READS SQL DATA in its declaration and binary logging is enabled (you *might*
want to use the less safe log_bin_trust_function_creators variable)
It has to do with whether the fnc is deterministic and how its results
are in
decreasing order.
For example:
109k7 1.79
s3x6 1.34
sxmns 1.21
wt57 0.93
I could use these numbers in the query as well if it helps.
PaPa
On 10/30/07, Peter Brawley [EMAIL PROTECTED] wrote:
I.e. the ideal output would be:
+---+-+
| id| start_date
Chris,
What I want to do is find all the groups where the inserts all
happened with in say 10 seconds. So my group by would be more like..
Perhaps the easiest solution is to make a temp table of datetime ranges
from the resultset, then join from and group by those rowIDs.
PB
-
How about this ...
update schedule s
join directory d on s.email = replace(d.email, '@wnc.edu', '@wncc.edu')
set s.email=replace(s.email, '@wncc.edu', '@wnc.edu' );
PB
-
Kevin Murphy wrote:
I'm trying to do a join on two pieces of data that are not quite
exactly the same. Basic story, I
I.e. the ideal output would be:
+---+-+
| id| start_date |
+---+-+
| 109k7 | 2007-10-07 12:06:58 |
| s3x6 | 2007-10-07 08:58:20 |
| wt57 | 2007-10-07 15:57:37 |
| sxmns | 2007-10-06 02:17:30 |
+---+-+
What
Thanks.. It doesn't seem to work though.. I did verify I am on 5.0
Try lose the space after group_concat.
PB
Andrey Dmitriev wrote:
Thanks.. It doesn't seem to work though.. I did verify I am on 5.0
mysql select service_names.name as 'Service',
- group_concat (hosts.name)
- from
Neil,
Do you mean ...
SELECT DATE(datetimecol) AS date,colourcol,COUNT(*)
FROM tbl
GROUP BY date,colourcol;
PB
--
Neil Tompkins wrote:
Hi,
I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key.
Aaron
An exclusion join:
SELECT a.col
FROM a
LEFT JOIN b ON a.col=b.col
WHERE b.col IS NULL;
PB
Aaron Fischer wrote:
Greetings!
I have a problem that it seems would best be solved using subqueries.
However, I am working on a server that is running MySQL 3.23.58, so
subqueries are not
, at 11:37 AM, Peter Brawley wrote:
Aaron
An exclusion join:
SELECT a.col
FROM a
LEFT JOIN b ON a.col=b.col
WHERE b.col IS NULL;
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When will I be able to do something seemingly
so basic as this re-use of an alias?
Do you know an implementation of SQL which allows this?
PB
Daevid Vincent wrote:
When will I be able to do something seemingly so basic as this re-use of an
alias?
SELECT DATE_ADD('2007-10-23', INTERVAL
Ryan,
Why is it so much faster?
Subquery optimisation in MySQL is a problem. For ideas see 'The
unbearable slowness of IN()' at
http://www.artfulsoftware.com/infotree/queries.php.
PB
Ryan Bates wrote:
I'm trying to determine why a subquery is slower than running two
separate queries. I
Wagner,
SHOW PROFILE[S] is documented at
http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html.
5.0 Community edition only. Perhaps it will appear in 5.1 Community?
They do not say so.
PB
Wagner Bianchi wrote:
Hi friends,
Somebody here in this list have or knows where i get or read
Miroslav
My goal is to sum 7 greatest results for each person.
Have a look at 'Within-group quotas (Top N per group)' at
http://www.artfulsoftware.com/infotree/queries.php.
PB
-
Miroslav Monkevic wrote:
Hello,
MySQL 4.1
I have query:
SELECT SUM(points) as ranking FROM results GROUP
Rapthor,
Try ...
SELECT u.name
FROM user u
JOIN user_role ur ON u.id=ur.user_id AND (u.id=1 OR u.id=2)
JOIN user_group ug ON u.id=ug.user_id AND (u.id=1 OR u.id=2)
HAVING COUNT( DISTINCT ur.role_id ) = 2
AND COUNT( DISTINCT ug.group_id ) = 2;
BTW you can't name a table 'group'; it's a
does anyone have a nicer solution for this?
How about comparing ereg_replace( [[:punct:]],, $colvalue ) with
ereg_replace( [[:punct:]],, $comparisonvalue )?
PB
mysql wrote:
hi listers
we have a mysql based application, wherein phone numbers may be stored
and searched for. it is not the
You might like to compare the performance of ...
SELECT t1.data1, t1.data2, MAX(t1.occurrence)
FROM t1
GROUP BY data1,data1
ORDER BY occurrence;
with...
SELECT t1.data1, t1.data2,t1.occurrence
FROM t1
LEFT JOIN t1 AS t2 ON t1.data1=t2.data2 AND t1.data2=t2.data2 AND
t1.occurrence
Mike,
What I'd love to do is pull all children (and grandchildren, etc) per
each, such that I'd end up with the following result set or something
See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
for theory examples.
PB
Mike Johnson wrote:
This one may end up dead in
Kevin,
To link articles keywords wouldn't you want a table like this?
CREATE table articles_keywords(
AK_id int auto_increment PRIMARY KEY,
AK_article_id int NOT NULL,
AK_keyword_id int NOT NULL
);
I don't understand why you want the sort of 'dynamic table' you describe.
PB
-
Kevin
Naz,
That query logic runs without a error on the server I have to hand
(5.0.37), but it has three issues:
(i) unless there is an exceptionless 1:1 relationship between
group_post_mod_option.option_id and group_post_mod_option.option_name,
results for the latter column will be meaningless
Craig,
Right you are, here is a corrected func:
DROP FUNCTION IF EXISTS BizDateTimeDiff;
DELIMITER |
CREATE FUNCTION BizDateTimeDiff( d1 DATETIME, d2 DATETIME )
RETURNS CHAR(30)
DETERMINISTIC
BEGIN
DECLARE dow1, dow2, days, wknddays INT;
DECLARE tdiff CHAR(10);
SET dow1 = DAYOFWEEK(d1);
SET
Craig,
I am working on Martin Minka's date diff function as found
at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful
thing. However, I am trying to alter it or identify a similar function
that instead of giving me the number of days between two dates it
returns the number of
Craig
How do I pull out all the non-duplicate records?
With an exclusion join. To find non-duplicates with respect to one key...
SELECT keycandidate
FROM tbl t1
LEFT JOIN tbl t2 ON t1.keycandidate=t2.keycandidate
WHERE t2.keycandidate IS NULL;
To expand the comparison to multiple keys...
Richard
I have table1 containing : message, senderid, reference
and table2 contains: senderid, name, address
I want to do a query that gives me : message, reference and name ...
Do you mean ...
SELECT t1.message, t1.reference, t2.name
FROM tbl1 t1
JOIN tbl2 ON t1.senderid=t2.senderid;
PB
or should I try another aproach?
Thanks :)
Richard
Peter Brawley a écrit :
Richard,
In ...
SELECT t1.message, t1.reference, t2.name
FROM tbl1 t1
JOIN tbl2 ON t1.senderid=t2.senderid
WHERE t1.reference = '$reference';
t1 and t2 are table aliases used to simplify table references.
The comma join
list and the table two is the members
information list.
So I need the same row to be joined to all the message rows with the
same senderid ...
I guess I will have to manage this with the php code and not get the
result directly from the sql query; thanks anyway !
Peter Brawley a écrit
:
MESSAGE | NAME
---
message1 text| Mr Smith
message3 text | Mrs Harrison
message4 text| Mr Smith
Is this clearer ?
Thanks :)
Peter Brawley a écrit :
Richard,
This is elementary---you most definitely do not need to do
Matt
I'd like to store paths to specific destinations...
See
-- Tropashko's 'materialized modell' eg
http://www.dbazine.com/oracle/or-articles/tropashko4
-- the airports example at
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
PB
-
Matt Juszczak wrote:
Hi all,
I'd like to represent our organization's chain of command (i.e. who is
whose
boss) in a database.
For some ideas see
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
PB
David T. Ashley wrote:
I'd like to represent our organization's chain of command (i.e. who is
whose
Reynier,
Normally with PHP I add every field, I mean (eval1, eval2,
eval3, eval4, eval5) and then divide this result by 5, like
a average. I know that SQL can do this directly using AVG
function but I don't know how. Can any help me?
AVG computes an average across rows. If I understand you
# of days since the first of the month from last month
datediff(now(),date(concat(period_add(date_format(now(),'%Y%m'),-1),'01')))
the # of days since the end of last month (e.g. from 5/31/07)
datediff(now(),date_sub(concat(date_format(now(),'%Y-%m-'),'01'),INTERVAL
1 DAY))
PB
-
Amer
I'm having no luck getting MySQL 5 installed under Windows 2000
Professional.
The service does not start, either automatically on bootup, or
manually. Reading
through the manual does not address the issue, at least that I could
find.
From %mysql_home%\bin try running it first in a cmd
Mike
I have a query that selects a list of results, ordering them by the status
field. However, I want to further sort that by the type of status, that is:
Undefined
Ready for Review
Top Priority
Priority
Completed
ORDER BY FIELD( columnname, 'Undefined', 'Ready for Review', ... )
PB
Nasir,
I am wondering if there is any way to execute external
application from MySQL procedures/trigger/event scheduler.
Like as we do in php with !,exec and system.
No, SQL isn't meant to be a complete computing language, and in any
event such capability would be a huge security headache.
Leelu,
The above Query's subselect query doesn't work properly, it lists
even the posts whose forum id is in subselect query.
Your `post LEFT JOIN thread ON post.threadid=thread.threadid` asks for
post rows whether they have matching thread rows or not. Are you sure
you want that? It sounds
Chris,
Does Mysql support groups of users?
It does not implement groups, but they are relatively easy to implement
by linking a usergroups table to mysql.users.
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL
tbt
the number of levels in this table is unknown and the query should work
for any number of levels
please provide a sample 'select' query in mysql
That's a graph, which is recursive, so you need an sproc. See edge list
sprocs at
Naz,
*Really* big sites don't ever have referential integrity. Or if the
few spots
they do (like with financial transactions) it's implemented on the
application
level (via, say, optimistic locking), never the database level.
Mebbe that view was common in the MySQL community in the time of
a tutorial on the subject, as this is about the
most asked question in DB relational data modeling.
http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/
Enjoy :)
- Naz.
Peter Brawley wrote:
tbt
the number of levels in this table is unknown and the query should work
for any
method I know, I'd love to
learn of a better one.
- Naz.
Peter Brawley wrote:
Naz writes
The definitive answer to anything that
requires trees in SQL is nested sets.
They are not definitive when the tree is large and must be updated
frequently.
PB
-
Naz Gassiep wrote
Erich,
Is there a way to set a prefix for each table so that
the results come out like tablename.column?
Use a scripting or application language to automate parameterise query
generation. SQL is just a partial computing language.
PB
-
Erich C. Beyrent wrote:
I have three tables, all
Brian,
I think the answer is to create a sub query,
Without your tables I can't test this transcription, but the trick is
straightforward: if the first query includes the column(s) required to
join it correctly to the 2nd query, replace the avgscore table reference
in the second query with
DB the server won't respond immediately
causing the conenct to time-out..
Does your script start with an information_schema query? The frist one
of those is painfully slow with MySQL.
Did you try set_time_limit( 0 )?
PB
-
JM wrote:
after trying it again ang again.. maybe for the 5th
I know about SHOW COLUMNS FROM tabname, but am looking for something
more SQL-y, because I want to use the results as part of a larger SQL
statement. For example, I want to find all the tables in a given db
that don't have a column named 'timestamp'.
I thought the 'mysql' system db may have this
201 - 300 of 889 matches
Mail list logo