Re: AW: Order By Limit; Count
it's not true, order by is performed before limit. if it was the other
way round , limit would be almost useless
Hadn't thought about this before, but what it means (I guess) is that LIMIT
works on the selection, not on the presentation.
What I'm trying to say is that when you consider the sequence in which a
SELECT statement is carried out by the database, an ORDER BY is the second
but last thing that's done (the last being a further restriction of the
result set through a HAVING clause, if present). At the time of the
ordering you already have a result set for the query, and the database is
now only working on the way this result set is presented to the user.
I presume that LIMIT applies to the gathering of the result set, i.e. you
get the required number of rows from the query up to and including the
WHERE clause. Only then it gets ordered, but if your result set does not
contain the records with an 'A' they can't get ordered either. Don't know
whether I'm explaining this very well, but perhaps you get the idea.
...
Have gone and tested it now (3.23.42 on Win2K). Here is the SQL text and
the results:
--- cut
DROP DATABASE IF EXISTS ordertest;
CREATE DATABASE ordertest;
USE ordertest;
CREATE TABLE letters (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
letter CHAR NOT NULL
);
INSERT INTO letters
(letter)
VALUES
('C'),
('C'),
('C'),
('C'),
('C'),
('C'),
('M'),
('N'),
('T'),
('W'),
('A'),
('B');
SELECT letter
FROM letters
LIMIT 10;
++
| letter |
++
| C |
| C |
| C |
| C |
| C |
| C |
| M |
| N |
| T |
| W |
++
10 rows in set (0.00 sec)
SELECT letter
FROM letters
ORDER BY letter
LIMIT 10;
++
| letter |
++
| A |
| B |
| C |
| C |
| C |
| C |
| C |
| C |
| M |
| N |
++
10 rows in set (0.37 sec)
-- cut
Surprise, surprise, turns out MySQL behaves not the way I thought it would,
but rather the way you thought it should. Are you perhaps running a
different version? And could you perhaps show us your table structure(s)
and query so that we can find out what's really happening there?
Cheers,
Christian Sage
-
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: Confusing SELECT statement
you can use select addresses.id from addresses LEFT JOIN mlist ON addresses.id=mlist.addr_id AND mlist.mlist_id=$mlist_id WHERE mlist.mlist_id IS NULL On Thu 27. September 2001 08:09, Adam Gintis wrote: I have the following table mlist: +--+-+ | mlist_id | addr_id | +--+-+ |1 | 1 | |2 | 2 | |2 | 3 | |1 | 3 | +--+-+ This is the join table of my address book and my mailing lists. So person 1 is in list 1, person 2 is in list 2, and person 3 is in both lists. What I want to do is get a list of everyone that is NOT in a certain list. In other words, if I'm editing list 1, I need a list of all the people in the address book that aren't in list 1 (person 2). If I'm editing list 2, I want person 1. I never want to see person 3, because he is already in both lists. I'm currently trying to do this with a statement like SELECT * FROM mlist GROUP BY addr_id HAVING mlist_id != 'list I'm currently editing'. The problem is that group by addr_id keeps the first row's value for mlist_id. So when I'm editing list 2, it works. But when I'm editing list 1, person 3 still shows up. If the last two rows were the other way around in the table, it would work for list 1 and not for list 2. So the question is, is there a command I can use to look through the aggregate set of mlist_id's, or is there another way to write this query? Thanks! --- Adam Gintis Vanderbilt University - 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: Dates: To Slash or to Hyphen. That is the question
hi, first - using php wouldn't it be much easier to use an integer to store datetime in the database? - i think it's faster and it allows you to manipulate the date much more easily. you could just tokenize the date string (using space,'/' and '-' or any ohter characters as the separators) - you would get an array of numerical values where the first one would be years, second one moths an so on. On Thu 16. August 2001 08:33, Van wrote: Greetings: Building a web form. There are 3 datetimes: RequestDate, FulfillDate, and CancelDate. New record populates a web form with the current date/time in -mm-dd hh:mm:ss format. RequestDate is current date, FullFillDate and CancelDate are -00-00 00:00:00. Updates present them with a record with the -mm-dd hh:mm:ss formatting directly selected from the mysql_query. If the end-user doesn't manipulate the dates, all goes fine. Just updates the record with the current values in the web form. If they want to change one of these dates, they might be inclined to put slashes in their dates rather than hyphens. They also might want to just put the day and the month (no time and no year). Form submission goes through and I need to put it into -mm-dd (optionally) hh:mm:ss format to update the records. I'm sure this is a common problem, but I haven't seen a decent PHP implementation of how to error check this and the UNIX_TIMESTAMP(date) function doesn't like it when the year isn't entered first (for an American implementation). So, if the user types 8/15 and hits update, it goes to -00-00 00:00:00. Best I've come up with is to check for a length of 19 chars, and do some processing in there. Chances are there'll only be one slash so an ereg can determine that and strip the first 2 argv's, pad the rest with zeroes. Anyone out their have a more complete and elegant way of doing this with a good -mm-dd hh:mm:ss ereg check? Would love to do it cleanly. Thanks, Van = Linux rocks!!! http://www.dedserius.com = - 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: installation of mysql on redhat7
look at the access privileges to the file '/var/lib/mysql/mysql.sock' and change them so that you can write to it On Wed 1. August 2001 11:44, Lars Freymark wrote: Hi everyone! I have installed mysql 3.23.22. The Distribution is from the redhat cd. My problem is the following: I can do anything I want, if I am connected as linux root. But if I try to access a database when I am connected to linux as another user I get the error: [freymark@ecs-server freymark]$ mysql -u alten -p eichen Enter password: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/m ysql.sock' (111) thanks Lars Freymark Tel.: 0431/ 66870-0 Fax : 0431/ 66870-29 e-mail: [EMAIL PROTECTED] www.ecs-media.de - 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
number of records in a ResultSet
hi, please is there a way to get the number of records in a JDBC ResultSet ? something like mysql_num_rows in PHP. thanks karel pitra - 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: primary keys with several parameters
the combination is unique so
iyou dont get an error
On Fri 6. July 2001 14:04, you wrote:
Hi all!
Sorry for the dumb question, but am I right in the following suggestion:
I have the following table:
create table tab_1 (
field_1 integer not null,
field_2 integer not null,
primary key (field_1, field_2));
Does it mean that this key requires unique COMBINATION of field_1 and
field_2? For example, at first I add:
insert into tab_1 values ('2', '3');
insert into tab_1 values ('3', '4');
Then I'd like to add the following:
insert into tab_1 values ('3', '2');
Here field_1 is NOT unique, but combination of field_1 and field_2 IS
unique. Will I get an error in this case?
Thanx for the answers!
-
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: Type Mismatch issue when Deleting from table
why do you use Replace if deleteheadline is an integer? On Fri 6. July 2001 14:26, you wrote: Platform: W2k/Advanced Server DB: mySQL 3.23.33 Language: ASP/VBScript Issue: I'm creating a backend administration program that will allow updates, deletes, additions, etc. All is well expect for in one table in particular when I choose to delete a record it gives me a type mismatch Error. I've written this code before on other pages without trouble, but this one table in particular is causing trouble. Here is the SQL statement on the ASP page: DELETE FROM tbl_headline WHERE head_id = + Replace(deleteheadline, ', '') + Now head_id is an Integer, and so is deleteheadline, but it gives me a type mismatch error everytime. I've even modded the code in several ways trying to get around it, like instead of matching the head_id I will match up the headline_name field with the same results or making it a text string by putting apostrophe's in etc.. I know the deleteheadline variable has a numerical value because I response.write the variable before the error occurs. Any help you be greatly appreciated. Thanks, Dave Carter Chief Web Architect Accelerated Business Technologies, Inc. http://www.abti.cc 717.464.2970 - 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
size of a table
please, is there a way to get the size of a mysql table through an SQL query ? thank you karel pitra - 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
mysql + unicode
please, does mysql support unicode? thanks in advance - 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
INSERT question
Hello, i'm writing an application in java and i have the following problem : when i insert a record into a mysql table through the executeUpdate() method of either Statement or PreparedStatement, does the method return immediately or does it wait until MySql has the data written into the database? i'm hesitating whether to use a new thread to do inserts with lower priority which would be necessary if executeUpdate() waits or whether to do them in the main thread. thank you karel pitra - 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
Tree
hello, does mysql allow for recursive SELECTS? is there a way to select a 'path in a tree' in one query ? (the tree is stored in the following form: parent_id int id int ) i know the ID of an element in n-th level of the tree, and i need to get all its parent elements - 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
