Re: SELECT Row Numbers?
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
oix ppl, now I have a question. I thougth that when you make a SELECT without an ORDER BY, the rows were returned in natural order, that would be some specific order (insertion order, presence in file, I don't know, but the order would be always the same). Is this uncorrect? Is the returning order variable? Thanks, mpneves On Tuesday 10 May 2005 10:37, Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
You should never assume that an SQL query will return data in any specific order *unless* you use an ORDER BY to force the sequence of rows in the result set. Data returned by a query will sometimes appear to come out in a particular order but you should always view this as a lucky coincidence, not something that is guaranteed to happen. Rhino - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 10, 2005 7:14 AM Subject: Re: SELECT Row Numbers? oix ppl, now I have a question. I thougth that when you make a SELECT without an ORDER BY, the rows were returned in natural order, that would be some specific order (insertion order, presence in file, I don't know, but the order would be always the same). Is this uncorrect? Is the returning order variable? Thanks, mpneves On Tuesday 10 May 2005 10:37, Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 10/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Hi, Have you forgotten what's a primary key ? Using order by will sort data, and if it's already sorted, it will be sorted again. Time, memory and maybe disk io. Using the marco example, i gaved a solution considering iy's what he wants. Till now i don't know if it's ok or not. if so, just add : select * from temp order by Id LIMIT 3,4; if no, the primary key index will give you the order. Mathias Selon Rhino [EMAIL PROTECTED]: You should never assume that an SQL query will return data in any specific order *unless* you use an ORDER BY to force the sequence of rows in the result set. Data returned by a query will sometimes appear to come out in a particular order but you should always view this as a lucky coincidence, not something that is guaranteed to happen. Rhino - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 10, 2005 7:14 AM Subject: Re: SELECT Row Numbers? oix ppl, now I have a question. I thougth that when you make a SELECT without an ORDER BY, the rows were returned in natural order, that would be some specific order (insertion order, presence in file, I don't know, but the order would be always the same). Is this uncorrect? Is the returning order variable? Thanks, mpneves On Tuesday 10 May 2005 10:37, Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 10/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Hi, Have you forgotten what's a primary key ? Using order by will sort data, and if it's already sorted, it will be sorted again. Time, memory and maybe disk io. If MySQL really does that, I'd consider this a bug. Using the marco example, i gaved a solution considering iy's what he wants. Till now i don't know if it's ok or not. if so, just add : select * from temp order by Id LIMIT 3,4; if no, the primary key index will give you the order. I dunno what you're talking about, but definitely not MySQL 4.1.11: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
news [EMAIL PROTECTED] wrote on 10/05/2005 15:13:49: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Hi, Have you forgotten what's a primary key ? Using order by will sort data, and if it's already sorted, it willbe sorted again. Time, memory and maybe disk io. If MySQL really does that, I'd consider this a bug. I agree. MySQL knows if the search order implicitly delivers the data in the requested order, and skips the sort phase if so. Using the marco example, i gaved a solution considering iy's what he wants. Till now i don't know if it's ok or not. if so, just add : select * from temp order by Id LIMIT 3,4; if no, the primary key index will give you the order. I dunno what you're talking about, but definitely not MySQL 4.1.11: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. InnoDB would probably do this, but MyISAM probably woudl not. If it chooses to do a fill table scan, it will deliver the results iht the essentially random order it stores them. If it uses and index, it is qitel likely to deliver them in the order of that index - which may not be the primary key. Indeed, the optimiser theoretically might use different indexes for the same query on different days, as the table cnages. It is therefore *never* safe to assume any sort of ordering unless you specify it. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. InnoDB would probably do this, but MyISAM probably woudl not. Incorrect, at least for the MySQL server I tested. If it chooses to do a fill table scan, it will deliver the results iht the essentially random order it stores them. If it uses and index, it is qitel likely to deliver them in the order of that index - which may not be the primary key. Indeed, the optimiser theoretically might use different indexes for the same query on different days, as the table cnages. Yes. I think the difference is not InnoDB vs MyISAM, but SELECT pkey vs SELECT pkey. someothercol. In the first case the result set can be built by just looking at the index (which is of course sorted), whereas the second case also needs to look at the table itself (which is unsorted). It is therefore *never* safe to assume any sort of ordering unless you specify it. That's what I wanted to emphasize. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Here, off the top of my head are situations in mysql where you can trust that the data is ordered in some fasion. 1. Using an order by clause on a query. 2. Using a group by the data will come out in ascending order of the column that was grouped on. 3. alter table order by has been performed and the table hasn't been modified . 4. select key from t; that uses the 'Using Index' in explain will return in the order of the key. Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. InnoDB would probably do this, but MyISAM probably woudl not. Incorrect, at least for the MySQL server I tested. If it chooses to do a fill table scan, it will deliver the results iht the essentially random order it stores them. If it uses and index, it is qitel likely to deliver them in the order of that index - which may not be the primary key. Indeed, the optimiser theoretically might use different indexes for the same query on different days, as the table cnages. Yes. I think the difference is not InnoDB vs MyISAM, but SELECT pkey vs SELECT pkey. someothercol. In the first case the result set can be built by just looking at the index (which is of course sorted), whereas the second case also needs to look at the table itself (which is unsorted). It is therefore *never* safe to assume any sort of ordering unless you specify it. That's what I wanted to emphasize. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
[EMAIL PROTECTED] wrote: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 That can't work as my initial query, because I don't know the location of iTempID: 4 in the query result to find the position 4, the query is : mysql select rk from(SELECT @row:[EMAIL PROTECTED] as rk,iTempID, sTemp - FROM dist) as A - WHERE iTempID=4; +--+ | rk | +--+ |4 | +--+ 1 row in set (0.00 sec) I can see how that gets me the position of my row, but I also need rows adjacent to it. I think I'll probably end up putting the results of my query into a temp table, complete with row numbers, then run my query for that. Or maybe a derived table. Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. Actually I do have an ORDER BY column in my real query, I trimmed it out for the sake of brevity. This column is a unique integer, but there are gaps of between the numbers, so I can't do a purely numerical solution. It must be based on what order the rows were returned from the query as far as I can tell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Chris [EMAIL PROTECTED] wrote on 05/10/2005 12:20:57 PM: Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. Actually I do have an ORDER BY column in my real query, I trimmed it out for the sake of brevity. This column is a unique integer, but there are gaps of between the numbers, so I can't do a purely numerical solution. It must be based on what order the rows were returned from the query as far as I can tell. So do you need just the 4 or 5 records _after_ a target ID or are you looking to bracket the target id (show me the record where ID=4 and the two records before and after it)? If you are only worried about ID+few following records you can say SELECT field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 5 That would give you your ID record and the 5 before immediately after. For your target ID + 2 records on either side you could say ( SELECT ID, field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 3 ) UNION ( SELECT ID, field list FROM table_name WHERE ID target value ORDER BY ID desc LIMIT 2 ) ORDER BY ID; Doing it this way, you don't need to know the position of a row because everything is based off of the row's id. It may not be as fast as some other ways but since your ID value is unique (I hope it's your primary key) then it should be indexed and these queries will be just about as fast as it gets. As everyone has stressed to the point of frustration, the concept of position only has meaning in an ordered set of results and only for the moment in time that the results were created. In the few tenths of a second it would take you to query a table, find a record, notice it's position, then requery a table based on that position, a few dozen records could have been added or deleted making your position-based query inaccurate. Trying to prevent that by locking the table would just make everything else come to a grinding halt until you had found the records you were looking for. Make your queries based on the PK value of the table you are dealing with. That way records can come and go as they please and your positional arithmetic will never be wrong. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SELECT Row Numbers?
[EMAIL PROTECTED] wrote: Chris [EMAIL PROTECTED] wrote on 05/10/2005 12:20:57 PM: Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. Actually I do have an ORDER BY column in my real query, I trimmed it out for the sake of brevity. This column is a unique integer, but there are gaps of between the numbers, so I can't do a purely numerical solution. It must be based on what order the rows were returned from the query as far as I can tell. So do you need just the 4 or 5 records _after_ a target ID or are you looking to bracket the target id (show me the record where ID=4 and the two records before and after it)? In some instances I will need the X records *after* , and in others I'll need the X records *before*, but never both. The target row will always be in the result set, and either be first or last. If you are only worried about ID+few following records you can say SELECT field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 5 That would give you your ID record and the 5 before immediately after. For your target ID + 2 records on either side you could say ( SELECT ID, field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 3 ) UNION ( SELECT ID, field list FROM table_name WHERE ID target value ORDER BY ID desc LIMIT 2 ) ORDER BY ID; Doing it this way, you don't need to know the position of a row because everything is based off of the row's id. It may not be as fast as some other ways but since your ID value is unique (I hope it's your primary key) then it should be indexed and these queries will be just about as fast as it gets. I have a Primary Key (duh), but it's not the number I'm ordering by. I misspoke in my previous email. The order column is supposed to be unique, but due to my need to change the column numbers around at times I can't define it as unique. Despite that this query above definitely appears to be the sort of thing I need. Only difference is that , since I don't need both sides, I'll only need to run either the first of the two queries, or the last (while keeping the UNION ORDER BY to reorder them) As everyone has stressed to the point of frustration, the concept of position only has meaning in an ordered set of results and only for the moment in time that the results were created. In the few tenths of a second it would take you to query a table, find a record, notice it's position, then requery a table based on that position, a few dozen records could have been added or deleted making your position-based query inaccurate. Trying to prevent that by locking the table would just make everything else come to a grinding halt until you had found the records you were looking for. Yeah, I really don't want to do any table locking, I'm doing my best just to get it all in one query. Make your queries based on the PK value of the table you are dealing with. That way records can come and go as they please and your positional arithmetic will never be wrong. That's whjat I'm trying to do. At this moment I'm wishing 4.1.x had Stored procedures, they would make my life a bit easier I think. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks, I appreciate it. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT Row Numbers?
I'm looking for a row numbering in a select statement. Something I can use to determine in whivh row values are returned in a query. I found this insanely old list post: http://lists.mysql.com/mysql/337 That appears to be what I want, but an examination of the changelogs for MySQL 3.23 didn't give me any ideas. Really what I want to do (with PHP / MySQL 4.1.?) is explained below, any advice on that would be welcomed as well. In PHP I have an an ID and a number, which represents number of rows. Taking the base query and table of: mysql SELECT - iTempID, - sTemp - FROM temp; +-+-+ | iTempID | sTemp | +-+-+ | 1 | fred| | 19 | barney | | 3 | wilma | | 4 | betty | | 23 | bam-bam | | 32 | pebbles | | 7 | bart| | 8 | lisa| | 6 | maggie | | 10 | homer | | 12 | marge | +-+-+ 11 rows in set (0.00 sec) I would like to specfify the id of one of the rows and a distance away from it, and return those rows, this is how I'm trying to do it with a row number, I'll use the fake function ROW_NUMBER() to represent the row number. ID: 4 Distance: 3 SELECT iTempID, sTemp FROM temp WHERE ROW_NUMBER() BETWEEN (SELECT ROW_NUMBER() FROM temp HAVING 4=iTempID) AND (SELECT ROW_NUMBER() FROM temp HAVING 4=iTempID) + 3 ; +-+-+ | iTempID | sTemp | +-+-+ | 4 | betty | | 23 | bam-bam | | 32 | pebbles | | 7 | bart| +-+-+ Of course, even if there is a ROW_NUMBER() function, It may not act as I hope in the preceding query due to the sub-queries and/or HAVING clause. Any help would be appreciated, Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Selon Chris [EMAIL PROTECTED]: I'm looking for a row numbering in a select statement. Something I can use to determine in whivh row values are returned in a query. I found this insanely old list post: http://lists.mysql.com/mysql/337 That appears to be what I want, but an examination of the changelogs for MySQL 3.23 didn't give me any ideas. Really what I want to do (with PHP / MySQL 4.1.?) is explained below, any advice on that would be welcomed as well. In PHP I have an an ID and a number, which represents number of rows. Taking the base query and table of: mysql SELECT - iTempID, - sTemp - FROM temp; +-+-+ | iTempID | sTemp | +-+-+ | 1 | fred| | 19 | barney | | 3 | wilma | | 4 | betty | | 23 | bam-bam | | 32 | pebbles | | 7 | bart| | 8 | lisa| | 6 | maggie | | 10 | homer | | 12 | marge | +-+-+ 11 rows in set (0.00 sec) I would like to specfify the id of one of the rows and a distance away from it, and return those rows, this is how I'm trying to do it with a row number, I'll use the fake function ROW_NUMBER() to represent the row number. ID: 4 Distance: 3 SELECT iTempID, sTemp FROM temp WHERE ROW_NUMBER() BETWEEN (SELECT ROW_NUMBER() FROM temp HAVING 4=iTempID) AND (SELECT ROW_NUMBER() FROM temp HAVING 4=iTempID) + 3 ; +-+-+ | iTempID | sTemp | +-+-+ | 4 | betty | | 23 | bam-bam | | 32 | pebbles | | 7 | bart| +-+-+ Of course, even if there is a ROW_NUMBER() function, It may not act as I hope in the preceding query due to the sub-queries and/or HAVING clause. Any help would be appreciated, Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 to find the position 4, the query is : mysql select rk from(SELECT @row:[EMAIL PROTECTED] as rk,iTempID, sTemp - FROM dist) as A - WHERE iTempID=4; +--+ | rk | +--+ |4 | +--+ 1 row in set (0.00 sec) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
