Re: complicated query
try ORDER BY (Category = 'Other'), Category, Subcategory It should work, (I hope ^_-;;;) - Original Message - From: Jeff Shipman - SysProg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 20, 2001 8:38 PM Subject: complicated query I would like to do something similar to an ORDER BY in one of my select statements, but I'mt not sure how to do something as complicated as this: I have two columns, category and subcategory, that I am retrieving. I would like category and subcategory to be sorted alphabetically. This is easy with an 'ORDER BY 1 2', but I would like categories that are named 'other' to be put off until the end. So, I'd get something like this: abcd ghikj z other Is there a way to do this type of query? Thanks in advance. Jeff Shipman E-Mail: [EMAIL PROTECTED] Systems Programmer Phone: (505) 835-5748 NMIMT Computer Center http://www.nmt.edu/~jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in 3.23.32 or later
As Far as I can remember it is explicitly said in the Manual that inserting
a negative value in an autoincrement field is a Bad Thing
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, April 18, 2001 6:14 AM
Subject: Possible bug in 3.23.32 or later
Description:
When i insert into a negative integer into primary key (id int) field, it
works fine.
However, the next insert i do without specifing the id explicitly and
relying on mysql to
generate the next number in the sequence (should be 1) will use the upper
bound (2^32 - 1)
Then every other insert after that will not insert since there are no id's
available.
How-To-Repeat:
CREATE TABLE category
(
idINT NOT NULL AUTO_INCRE17;6,
name VARCHAR(50) NOT NULL,
code VARCHAR(50) NOT NULL,
description TEXT,
picture VARCHAR(50),
parentINT NOT NULL DEFAULT 0,
activeTINYINT,
members INT NOT NULL DEFAULT 0,
storeid INT,
feature1 INT NOT NULL DEFAULT 0,
feature2 INT NOT NULL DEFAULT 0,
priority INT,
KEY (code),
KEY (name),
PRIMARY KEY (id)
)\g
INSERT INTO category
(id,name,code,description,picture,parent,active,members,storeid,feature1,fea
ture2) VALUES (-10,'NEW','NEW','All new products','none',-1,0,0,1,0,0);
Then try:
INSERT INTO category
(name,code,description,picture,parent,active,members,storeid,feature1,featur
e2) VALUES ('test','test','All test products','none',-1,0,0,1,0,0);
That should make the id 2^32 -1 (or something aroudn there).
That id should be 1!!!
Fix:
Submitter-Id: submitter ID
Originator: Duane Homick
Organization:
Inline Internet Systems, Inc.
MySQL support: none
Synopsis: bug in primary key insert with negative integers
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-3.23.32 (TCX binary)
Environment:
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Display information
- Original Message - From: "Ron Beck" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 16, 2001 7:12 PM Subject: Re: Display information SELECT id,lot_id,lot_type FROM scrap_table ORDER BY id limit 1932,10; seemed promising but it put them in oldest-to-newest order (ascending), not newest-to-oldest order (desc). When the "desc" qualifier was added, I got records 122 thru 113. Again, not exactly what I was looking for. You only have to know the total number of record ,which I think you can easily get SELECT id,lot_id,lot_type FROM scrap_table ORDER BY id DESC limit TOT - 1932,10 at the present time I don't remember if ther is a way to do it directly from SQL, but I don't think it's such a great effort ^^ What I was actually hoping for was something I could add to my original SELECT statement to perform the task if someone entered the ID but would show all (limit 10) if there was no entry. And, I wanted to do it without rewriting my code. Unfortunately, it looks like I may have to do a code rewrite for that section, which means documenting, testing, acceptance... Anyway, thanks for all the suggestions Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP with SQL statement
I think that this will do SELECT -(Sum(Rating-2)) AS Total FROM Rating GROUP BY 1; - Original Message - From: "Mike Podlesny" [EMAIL PROTECTED] To: "Tomi Junnila" [EMAIL PROTECTED]; "mySQL Mailing List" [EMAIL PROTECTED] Sent: Friday, January 19, 2001 4:50 PM Subject: HELP with SQL statement Actually let me reword this because Tomi's answer won't quite work. The table has a field called Rating. A number 1 is stored to represent positive a 2 is stored for neutral and a 3 is stored for negative. I need to query that will query up all the number 1's (positives) and subtract all the number 3's (negatives) to give me a true rating. Any ideas? - Original Message - From: "Tomi Junnila" [EMAIL PROTECTED] To: "mySQL Mailing List" [EMAIL PROTECTED] Sent: Friday, January 19, 2001 10:32 AM Subject: Re: HELP with SQL statement * Mike Podlesny [EMAIL PROTECTED] wrote on 19.01.01 17:25: I have a table called RATING and two fields one called POSITIVE and the other called NEGATIVE. I need to write an SQL statement for my mySQL database that will return the value of the total of POSITIVEs minues the total of the NEGATIVEs. Err, how about select sum(positive-negative) from rating; ? -- Tomi Junnila [EMAIL PROTECTED] http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why does 1st query take so long?
- Original Message - From: [EMAIL PROTECTED] To: "mySQL list" [EMAIL PROTECTED] Sent: Tuesday, January 16, 2001 3:57 PM Subject: Why does 1st query take so long? I've noticed that when developing queries involving complex joins on tables I sometimes seem to get appallingly long times the first time I run a query, but the second and subsequent time I run it - even substituting a different value for a field value I'm matching on - the query runs quickly. Does mySQL do some behind-the-scenes reindexing the first time, resulting in the speed differences I see? If so is there some way I can force it to re-build indices periodically so I can ensure that queries are generally fast? Is this the point I should be going out and buying a book about mySQL? ;-) I think its because the Operating System is caching the data the query are run on - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
