Re: Max size and row numbers
Olaf, That's a very broad question depending upon your exact requirements to be honest. So long as you've considered file size limitations and you've opted for a decent RAID system there's not too much more to worry about, try and buy the fastest disks possible obviously i.e. 15,000 RPM ones that are specifically designed for RAID setups etc... Regards, Phil There is a RAID now an there will definitely one in the new setup. As far as the max file sizes from the file systems go I am not worried after all I read. Thanks for those links btw. Besides the size though, what should I pay attention to when selecting the file system Thanks Olaf On 12/29/06 11:31 AM, "Philip Mather" <[EMAIL PROTECTED]> wrote: Olaf, Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf The "best" is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Olaf, Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf The "best" is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf On 12/29/06 2:25 AM, "ViSolve DB Team" <[EMAIL PROTECTED]> wrote: > Hi, > > The maximum effective table size for MySQL databases is usually determined by > operating system constraints on file sizes, not by MySQL internal limits. > > If you need a MyISAM table that is larger than 4GB in size (and your operating > system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH > and MAX_ROWS options. > > The InnoDB storage engine maintains InnoDB tables within a tablespace that can > be created from several files. This allows a table to exceed the maximum > individual file size. The tablespace can include raw disk partitions, which > allows extremely large tables. The maximum tablespace size is 64TB. > > On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the > Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches > also exist for ReiserFS to get support for big files (up to 2TB). However, > the maximum available file size still depends on several factors, one of them > being the filesystem used to store MySQL tables. > > You can check the maximum table size for a table with the SHOW TABLE STATUS > statement or with myisamchk -dv tbl_name. If your large table is read-only, > you can use myisampack to compress it. myisampack usually compresses a table > by at least 50%, so you can have, in effect, much bigger tables. > > Thanks > > Visolve DB Team > > - Original Message - > From: "Olaf Stein" <[EMAIL PROTECTED]> > To: > Sent: Friday, December 29, 2006 4:14 AM > Subject: Max size and row numbers > > >> Hey everyone >> >> I have more of a general question regarding your experience with large >> tables. >> >> I currently have a table (MyISAM, 6 columns, lots of reading access, some >> writing) with about 70.000.000 records, using 2.5GB of diskspace. I am >> running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). >> >> I just read that the max size for a table is 256TB in a default >> installation. I have basically no experience with tables that big and mine >> is potentially growing to several hundred million records. >> >> First of all, are there theoretical limitations (if the 256TB are correct I >> would be fine with that I guess) in size and number of records? >> >> More importantly, what are the practical limitations and/or pitfalls? Is >> ext3 as filesystem a limiting factor? >> >> If you have experience or know of good links regarding this topic, please >> let me know >> >> Thanks in advance >> >> Olaf >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Hi, The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB. On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). However, the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. Thanks Visolve DB Team - Original Message - From: "Olaf Stein" <[EMAIL PROTECTED]> To: Sent: Friday, December 29, 2006 4:14 AM Subject: Max size and row numbers > Hey everyone > > I have more of a general question regarding your experience with large > tables. > > I currently have a table (MyISAM, 6 columns, lots of reading access, some > writing) with about 70.000.000 records, using 2.5GB of diskspace. I am > running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). > > I just read that the max size for a table is 256TB in a default > installation. I have basically no experience with tables that big and mine > is potentially growing to several hundred million records. > > First of all, are there theoretical limitations (if the 256TB are correct I > would be fine with that I guess) in size and number of records? > > More importantly, what are the practical limitations and/or pitfalls? Is > ext3 as filesystem a limiting factor? > > If you have experience or know of good links regarding this topic, please > let me know > > Thanks in advance > > Olaf > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Max size and row numbers
Hey everyone I have more of a general question regarding your experience with large tables. I currently have a table (MyISAM, 6 columns, lots of reading access, some writing) with about 70.000.000 records, using 2.5GB of diskspace. I am running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). I just read that the max size for a table is 256TB in a default installation. I have basically no experience with tables that big and mine is potentially growing to several hundred million records. First of all, are there theoretical limitations (if the 256TB are correct I would be fine with that I guess) in size and number of records? More importantly, what are the practical limitations and/or pitfalls? Is ext3 as filesystem a limiting factor? If you have experience or know of good links regarding this topic, please let me know Thanks in advance Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding row numbers to a select.
Chris W wrote: Is there an easy way to add row numbers to the result of a select statement? With a user variable: SET @i = 0; SELECT @i:= @i + 1 AS 'Row', ... Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding row numbers to a select.
Is there an easy way to add row numbers to the result of a select statement? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.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?
[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 FROM table_name WHERE ID>= 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, FROM table_name WHERE ID>= ORDER BY ID LIMIT 3 ) UNION ( SELECT ID, FROM table_name WHERE ID < 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]
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 FROM table_name WHERE ID>= 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, FROM table_name WHERE ID>= ORDER BY ID LIMIT 3 ) UNION ( SELECT ID, FROM table_name WHERE ID < 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?
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?
[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?
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?
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?
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: > 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?
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: > 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?
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: 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?
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?
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?
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]
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: [Fwd: row numbers, jumping rows]
If I understand your question correctly you want to group results for statistical analysis. For instance if you have 2000 results (sequenced in some way) you want to be able to split those results into 10 equal (or nearly equal) groups of results and re-aggregate your results based on the new "generated" group number (like a decile or percentile) Let's say, as an example, you want to know how many scores are in each decile (tenth) of scores out of a possible maximum of 1600 per test. That means you want to count how many people scored from 0-159, 160 - 319, ... , 1440 -1600. There are several ways to do this but I choose to make a table of my decile ranges (groups) for this example. This also allows you to modify the size of each group individually. CREATE TABLE AnalysisGroups ( groupID int not null auto_increment , grouplow int not null , grouphigh int not null , primary key(id) , key(grouplow, grouphigh) ) INSERT AnalysisGroups (grouplow, grouphigh) VALUES (0,159), (160, 319), (320,479 ), (480,639), (640,799), (800,959), (960,1119), (1120,1279), (1280,1439), (1440,1600) The test scores are stored in another table. If you want to collect how many scores fall into each category you just join them together and total up the matches SELECT ag.groupID as decile , count(tr.testID) as scores FROM AnalysisGroups ag INNER JOIN TestResults tr ON tr.score >= ag.grouplow AND tr.score <= ag.grouphigh GROUP BY ag.groupID If you want to group by population you have another trick you can use similar to this one. First we need to create a table that can store the results of the query but that also has two additional columns. One of those columns is an auto_increment column (so that we number each element of the result) and the second will be which "-ile" (percentile, decile, quartile) the row falls into. Sticking with our current example (test scores) assume we need to compute the average score for each percentile of test takers. We could do this by first ordering the results from lowest score to highest score (or by any other useful measure) then dividing the list into 100 equal groups, eventually averaging the score for each group. CREATE TEMPORARY TABLE tmpAnalysis( ID int auto_increment , percentile int , testID int , score float key(percentile) ); INSERT tmpAnalysis(testID, score) SELECT id, score FROM TestResults ORDER BY score; SELECT @Pctl := count(id)/100 from tmpAnalysis; UPDATE tmpAnalysis SET percentile = FLOOR((ID-1)/@Pctl) * @Pctl; SELECT percentile, AVG(score) as pctl_mean FROM tmpAnalysis GROUP BY percentile; DROP TEMPORARY TABLE tmpAnalysis; I added an "extra" column to the temp table so that you could see that you could use that table for multiple purposes. Once I have assigned the percentile numbers to each row, I could have identified which tests (and which test takers) fell into each percentile. SELECT tr.taker, tr.score FROM TestResults tr INNER JOIN tmpAnalysis a on a.testID = tr.id Where a.percentile >= 95 Assuming you haven't dropped the temp table yet, that query will give you the list of who scored in the top 5% on that particular test. >>Disclaimer<< I haven't had time to test any of this with live data. If these examples don't work the first time, it's probably because I made a typing error. Apologies in advance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine João Borsoi Soares <[EMAIL PROTECTED]> wrote on 11/23/2004 06:30:26 AM: > No body has answered my question so far. Does that mean there is no way > to retrieve current row numbers in a query or no way to retrieve results > jumping steps? > > I would appreciate any help. > > Thanks, > Joao. > > -Mensagem encaminhada----- > > From: João Borsoi Soares <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > > Subject: row numbers, jumping rows > > Date: Sun, 21 Nov 2004 11:23:41 -0200 > > > > Hello list, > > > > I wonder if someone could help me to get the fastest result on my query. > > I need to classify one entity, according to a specific attribute (let's > > call it X), looking at a sample extracted from a database table. > > First I need to sort my sample ordered by a specific table field (X). > > Then I should divide my sorted sample in 10 equal groups (same number of > > rows). Finally, I should classify my entity (1 to 10), finding out at > > which group its attribute X fits in. > > > > Let me explain my thoughts. First I should make a "SELECT COUNT(*).." to > > find the total number of rows in my sample. Then I make queries using > > LIMIT until I find which group the X attribute fi
Re: [Fwd: row numbers, jumping rows]
João Borsoi Soares <[EMAIL PROTECTED]> wrote on 23/11/2004
12:34:01:
> First thanks for the answer Alec. But I think you didn't understood my
> problem. Maybe nobody replied because of that. Let me try again.
>
> Suppose I make a select which returns 100 ordered rows. I only want to
> read rows number 10, 20, 30, 40, 50, 60, 70, 80, 90 and 100.
>
> To read the 10th row I would make "SELECT FROM ORDER
> BY LIMIT 10". To read the 20th it would be "SELECT
> FROM ORDER BY LIMIT 10,10". And so on..
>
> What I want is to make all of these queries in only one.
>
> That's why I said if I could get the row number retrieved from the
> query, I could do: "SELECT FROM WHERE (rowNumber %
> (tableSize/10)) = 0 ORDER BY "
I am not a real MySQL wizard, so there may be better ways. But the way I
would do it would be with a temporary table. This may sound cumbersome,
but as far as I can see MySQL would have to create a temporary table
internally to satisfy your request anyway.
CREATE TEMPORARY TABLE temp
{row INT AUTOINCREMENT NOT NULL,
) ;
INSERT INTO temp SELECT NULL< FROM ORDER BY
;
SELECT FROM temp WHERE row % 10 = 0 LIMIT ;
DROP TABLE temp ;
A bit clunky, I agree, but the only way I can see of solving your problem
;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: row numbers, jumping rows]
First thanks for the answer Alec. But I think you didn't understood my problem. Maybe nobody replied because of that. Let me try again. Suppose I make a select which returns 100 ordered rows. I only want to read rows number 10, 20, 30, 40, 50, 60, 70, 80, 90 and 100. To read the 10th row I would make "SELECT FROM ORDER BY LIMIT 10". To read the 20th it would be "SELECT FROM ORDER BY LIMIT 10,10". And so on.. What I want is to make all of these queries in only one. That's why I said if I could get the row number retrieved from the query, I could do: "SELECT FROM WHERE (rowNumber % (tableSize/10)) = 0 ORDER BY " Thanks again, Joao. Em Ter, 2004-11-23 às 09:57, [EMAIL PROTECTED] escreveu: > I think the reason nobody has replied is that the term "row number" does > not really have any meaning in a DBMS. How the database stores rows > inteneally is the DBMS's private business, and should not be visible to > you. I think it is true that MySQL does not *have* an internal row number, > so there is nothing to skip by. All it stores in MyISAM table is file > offsets. However, even if I am wrong, it doesn't matter: that is an > internal implementation detail and should not be visible to you. > > However, if I read you rightly, what you want it to extract a random tenth > of your table. You could do this by something like > SELECT FROM ORDER BY rand() LIMIT ; > > > João Borsoi Soares <[EMAIL PROTECTED]> wrote on 23/11/2004 > 11:30:26: > > > No body has answered my question so far. Does that mean there is no way > > to retrieve current row numbers in a query or no way to retrieve results > > jumping steps? > > > > I would appreciate any help. > > > > Thanks, > > Joao. > > > > -Mensagem encaminhada- > > > From: João Borsoi Soares <[EMAIL PROTECTED]> > > > To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > > > Subject: row numbers, jumping rows > > > Date: Sun, 21 Nov 2004 11:23:41 -0200 > > > > > > Hello list, > > > > > > I wonder if someone could help me to get the fastest result on my > query. > > > I need to classify one entity, according to a specific attribute > (let's > > > call it X), looking at a sample extracted from a database table. > > > First I need to sort my sample ordered by a specific table field (X). > > > Then I should divide my sorted sample in 10 equal groups (same number > of > > > rows). Finally, I should classify my entity (1 to 10), finding out at > > > which group its attribute X fits in. > > > > > > Let me explain my thoughts. First I should make a "SELECT COUNT(*).." > to > > > find the total number of rows in my sample. Then I make queries using > > > LIMIT until I find which group the X attribute fits in. In the worst > > > case I will have to do 10 queries. Which I think should take too long > in > > > my case. > > > > > > I wanted to make it in only one query. It could be possible if I could > > > make a query which returns result jumping rows, like "STEP N" where > "N" > > > could be the number of items in each group. Is there anything in mysql > > > that can give me that? I also thought if I could have the row numbers > in > > > the query result, I could solve it with a simple condition like, > "WHERE > > > rowNum % N = 0". > > > > > > Any ideas? > > > > > > Thanks. > > > > > > > > > > > > > > > -- > > 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: [Fwd: row numbers, jumping rows]
I think the reason nobody has replied is that the term "row number" does not really have any meaning in a DBMS. How the database stores rows inteneally is the DBMS's private business, and should not be visible to you. I think it is true that MySQL does not *have* an internal row number, so there is nothing to skip by. All it stores in MyISAM table is file offsets. However, even if I am wrong, it doesn't matter: that is an internal implementation detail and should not be visible to you. However, if I read you rightly, what you want it to extract a random tenth of your table. You could do this by something like SELECT FROM ORDER BY rand() LIMIT ; João Borsoi Soares <[EMAIL PROTECTED]> wrote on 23/11/2004 11:30:26: > No body has answered my question so far. Does that mean there is no way > to retrieve current row numbers in a query or no way to retrieve results > jumping steps? > > I would appreciate any help. > > Thanks, > Joao. > > -Mensagem encaminhada- > > From: João Borsoi Soares <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > > Subject: row numbers, jumping rows > > Date: Sun, 21 Nov 2004 11:23:41 -0200 > > > > Hello list, > > > > I wonder if someone could help me to get the fastest result on my query. > > I need to classify one entity, according to a specific attribute (let's > > call it X), looking at a sample extracted from a database table. > > First I need to sort my sample ordered by a specific table field (X). > > Then I should divide my sorted sample in 10 equal groups (same number of > > rows). Finally, I should classify my entity (1 to 10), finding out at > > which group its attribute X fits in. > > > > Let me explain my thoughts. First I should make a "SELECT COUNT(*).." to > > find the total number of rows in my sample. Then I make queries using > > LIMIT until I find which group the X attribute fits in. In the worst > > case I will have to do 10 queries. Which I think should take too long in > > my case. > > > > I wanted to make it in only one query. It could be possible if I could > > make a query which returns result jumping rows, like "STEP N" where "N" > > could be the number of items in each group. Is there anything in mysql > > that can give me that? I also thought if I could have the row numbers in > > the query result, I could solve it with a simple condition like, "WHERE > > rowNum % N = 0". > > > > Any ideas? > > > > Thanks. > > > > > > > > > -- > 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]
[Fwd: row numbers, jumping rows]
No body has answered my question so far. Does that mean there is no way to retrieve current row numbers in a query or no way to retrieve results jumping steps? I would appreciate any help. Thanks, Joao. -Mensagem encaminhada- > From: João Borsoi Soares <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > Subject: row numbers, jumping rows > Date: Sun, 21 Nov 2004 11:23:41 -0200 > > Hello list, > > I wonder if someone could help me to get the fastest result on my query. > I need to classify one entity, according to a specific attribute (let's > call it X), looking at a sample extracted from a database table. > First I need to sort my sample ordered by a specific table field (X). > Then I should divide my sorted sample in 10 equal groups (same number of > rows). Finally, I should classify my entity (1 to 10), finding out at > which group its attribute X fits in. > > Let me explain my thoughts. First I should make a "SELECT COUNT(*).." to > find the total number of rows in my sample. Then I make queries using > LIMIT until I find which group the X attribute fits in. In the worst > case I will have to do 10 queries. Which I think should take too long in > my case. > > I wanted to make it in only one query. It could be possible if I could > make a query which returns result jumping rows, like "STEP N" where "N" > could be the number of items in each group. Is there anything in mysql > that can give me that? I also thought if I could have the row numbers in > the query result, I could solve it with a simple condition like, "WHERE > rowNum % N = 0". > > Any ideas? > > Thanks. > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
row numbers, jumping rows
Hello list, I wonder if someone could help me to get the fastest result on my query. I need to classify one entity, according to a specific attribute (let's call it X), looking at a sample extracted from a database table. First I need to sort my sample ordered by a specific table field (X). Then I should divide my sorted sample in 10 equal groups (same number of rows). Finally, I should classify my entity (1 to 10), finding out at which group its attribute X fits in. Let me explain my thoughts. First I should make a "SELECT COUNT(*).." to find the total number of rows in my sample. Then I make queries using LIMIT until I find which group the X attribute fits in. In the worst case I will have to do 10 queries. Which I think should take too long in my case. I wanted to make it in only one query. It could be possible if I could make a query which returns result jumping rows, like "STEP N" where "N" could be the number of items in each group. Is there anything in mysql that can give me that? I also thought if I could have the row numbers in the query result, I could solve it with a simple condition like, "WHERE rowNum % N = 0". Any ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row numbers: finding a particular row in a table
Jens Porup wrote: G'day, A different tack on my problem. mysqldump is giving me the following error: rack002:~# mysqldump -u root -p rt2 > rt2.dump Enter password: mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table 'Attachments' at row: 36763 How can I find row 36763 and possibly correct this problem? I've looked at the autoincrement field, id # 36763, but that doesn't seem to be a problem record. How can I locate the record referenced by that row number? you mean SELECT * from Attachments WHERE id='36763'; ? It seems to me that you've got a very big blob somewhere that is bigger than the max_allowed_packet in you my.cnf -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Row numbers: finding a particular row in a table
G'day, A different tack on my problem. mysqldump is giving me the following error: rack002:~# mysqldump -u root -p rt2 > rt2.dump Enter password: mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table 'Attachments' at row: 36763 How can I find row 36763 and possibly correct this problem? I've looked at the autoincrement field, id # 36763, but that doesn't seem to be a problem record. How can I locate the record referenced by that row number? This is on a Debian Stable server, running: rack002:~# mysqldump --version mysqldump Ver 8.22 Distrib 3.23.54, for pc-linux-gnu (i686) Thanks, Jens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row numbers
just as:
$myquery = "select * from sometable";
$myresult = mysql_query($myquery, $mysql_link);
$rows = $mysql_fetch_array($myresult)
$count = 1;
while ($rows)
{
print(" $count$rows[0]$rows[1]...
$count++;
}
I would do the same thing in perl, C++, or Java.
Curtis
On Sun, 16 Feb 2003, Michael T. Babcock wrote:
> Luc Foisy wrote:
>
> >I didn't say it had nothing to do with the data, I said it had nothing to do with
>the data in the database.
> >
>
> I'm making the assumption as a DBA that _all_ the data relevant is in
> the database; so the comment I made was equivalent to the above.
>
> >As I said, sure I could make external functions to do the job. But why? (I did
>because I have no choice at the moment)
> >
>
> If the row number is not _used_ but only for show, it _shouldn't_ be
> done by the database to properly sequester and segment your data and
> program models. However, if you're going to use that row number in any
> way (like: $row data)
> then it should be an auto_increment value from the database itself
> (although you may very well display a fake programmatically generated ID
> as well).
>
> >1. There are a lot of functions that return values that have nothing to do with
>data contained in the database. Math functions for one, they calculate return values
>using data that is in the database. And many many other functions. Why not one more.
> >
>
> They calculate results based on data in the database; the row number is
> a meta-value. Unless its a _rowid type value, you're talking about a
> cosmetic value that isn't data-related.
>
> >In the 4.x series the developers implemented SQL_CALC_FOUND_ROWS and FOUND_ROWS(),
>something that could just have easily been handled by external programming...
> >
>
> Those values can be useful within a query though. Also, someone may
> have paid for those features (hint).
>
> >3. Going through the archives, I have seen many many people ask for it.
> >
>
> Most of those people are now using the methods everyone else on here has
> described because they're correct. Lots of people have asked for other
> things that may never be done by the MySQL team as well.
>
> >4. Those numbers probably already exist, how else does it ORDER BY, it has to put
>the results in an array of some kind
> >
>
> I have my doubts, I bet they don't exist :)
>
> For a pseudo-php example:
>
> $results = Select("id, name from names order by name limit $start,
> $maxperpage");
> print "";
> while ($row = mysql_fetch_array($results))
> {
> ?> $row['name'] ?> }
> echo "
>
> You'll see how the 'ol' tag provides me with numbered results up to the
> number of $maxperpage on each display, but I use the actual unique ID
> value from the database as a reference value. The 'ol' values are just
> as easily put in the PHP itself, of course, since they have nothing to
> do with the data (no association to that data).
>
>
-
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: Row numbers
Luc Foisy wrote:
I didn't say it had nothing to do with the data, I said it had nothing to do with the data in the database.
I'm making the assumption as a DBA that _all_ the data relevant is in
the database; so the comment I made was equivalent to the above.
As I said, sure I could make external functions to do the job. But why? (I did because I have no choice at the moment)
If the row number is not _used_ but only for show, it _shouldn't_ be
done by the database to properly sequester and segment your data and
program models. However, if you're going to use that row number in any
way (like: $row data)
then it should be an auto_increment value from the database itself
(although you may very well display a fake programmatically generated ID
as well).
1. There are a lot of functions that return values that have nothing to do with data contained in the database. Math functions for one, they calculate return values using data that is in the database. And many many other functions. Why not one more.
They calculate results based on data in the database; the row number is
a meta-value. Unless its a _rowid type value, you're talking about a
cosmetic value that isn't data-related.
In the 4.x series the developers implemented SQL_CALC_FOUND_ROWS and FOUND_ROWS(), something that could just have easily been handled by external programming...
Those values can be useful within a query though. Also, someone may
have paid for those features (hint).
3. Going through the archives, I have seen many many people ask for it.
Most of those people are now using the methods everyone else on here has
described because they're correct. Lots of people have asked for other
things that may never be done by the MySQL team as well.
4. Those numbers probably already exist, how else does it ORDER BY, it has to put the results in an array of some kind
I have my doubts, I bet they don't exist :)
For a pseudo-php example:
$results = Select("id, name from names order by name limit $start,
$maxperpage");
print "";
while ($row = mysql_fetch_array($results))
{
?>
}
echo "
You'll see how the 'ol' tag provides me with numbered results up to the
number of $maxperpage on each display, but I use the actual unique ID
value from the database as a reference value. The 'ol' values are just
as easily put in the PHP itself, of course, since they have nothing to
do with the data (no association to that data).
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
-
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: Row numbers
On Fri, Feb 14, 2003 at 01:04:00PM -0500, Luc Foisy wrote: > I didn't say it had nothing to do with the data, I said it had > nothing to do with the data in the database. The data in the > recordset returned from the database referenced by row is important. [...] > 1. There are a lot of functions that return values that have nothing > to do with data contained in the database. Math functions for one, > they calculate return values using data that is in the database. And > many many other functions. Why not one more. You're not just asking for a function whose input doesn't come from the database, you're asking for one whose input comes from a later pass in data processing. After the row is inserted into the final result set, this value has to be updated. > In the 4.x series the > developers implemented SQL_CALC_FOUND_ROWS and FOUND_ROWS(), > something that could just have easily been handled by external > programming... I'm sure they weighed the utility of the change (saves a lot of traffic and time, when you need it) against the complexity of its implementation (probably very little, given what it does) and decided it was worth it. Every addition to a language or library has to be made very carefully because they generally will be there forever. The function you describe requires a row to be updated where currently there is no updating going on, adding complexity where there is none now. > 2. There is a use for it. since... > > 3. Going through the archives, I have seen many many people ask for >it. I've seen people ask for a ROWNUM() function or somesuch, which reveals the implementation's ordering of records in the source table; that request shows up once in a while (and usually reveals a misunderstanding of rdbms, imo). But in the result set? We're talking about $resultNum++ as you loop through the results. (Or the @x solutions other people have mentioned.) I've never heard someone ask for the functionality you want before. Even if there were many such requests, assigning serial numbers to sequential output, if that's what you want, is what general purpose languages are for, not what relational databases are for. Imo. Extreme example: Why not add "SELECT ... INTO EMAIL [EMAIL PROTECTED]" to the language so the server can email you results? (You may find examples where the folks at MySQL have chosen to add non-rdbms features into their server, but that doesn't mean they want to add anything whatsoever and then maintain it forever.) > 4. Those numbers probably already exist, how else does it ORDER BY, >it has to put the results in an array of some kind I believe those numbers are unknown when generating the row values. -- Pete Harlan, who doesn't speak for the MySQL developers of course. [EMAIL PROTECTED] - 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: Row numbers
> > On Thu, Feb 13, 2003 at 03:52:18PM -0800, Steve Edberg wrote: > > >That means no field exists or should exist in the > database. I only want to > > >generate at query time. > > >I can't use an autoincrement field since that wont work > very well with > > >results that are returned out of order and maybe not with > all the data. > > > > > >Using variables is the best response to my question. I > just dislike using > > >them cause they are ugly to work with because of the > session persistance > > >and because I have to issue multiple queries to do the job. > > If the value isn't being used to even select a row, why not > wrap the lines > with ...results... or perhaps just use PHP/Perl/? to > do an $i++ for > display? > > Why put this in the query at all, if it has nothing to do > with the data? I didn't say it had nothing to do with the data, I said it had nothing to do with the data in the database. The data in the recordset returned from the database referenced by row is important. As I said, sure I could make external functions to do the job. But why? (I did because I have no choice at the moment) 1. There are a lot of functions that return values that have nothing to do with data contained in the database. Math functions for one, they calculate return values using data that is in the database. And many many other functions. Why not one more. In the 4.x series the developers implemented SQL_CALC_FOUND_ROWS and FOUND_ROWS(), something that could just have easily been handled by external programming... 2. There is a use for it. since... 3. Going through the archives, I have seen many many people ask for it. 4. Those numbers probably already exist, how else does it ORDER BY, it has to put the results in an array of some kind - 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: Row numbers
On Thu, Feb 13, 2003 at 03:52:18PM -0800, Steve Edberg wrote: > >That means no field exists or should exist in the database. I only want to > >generate at query time. > >I can't use an autoincrement field since that wont work very well with > >results that are returned out of order and maybe not with all the data. > > > >Using variables is the best response to my question. I just dislike using > >them cause they are ugly to work with because of the session persistance > >and because I have to issue multiple queries to do the job. If the value isn't being used to even select a row, why not wrap the lines with ...results... or perhaps just use PHP/Perl/? to do an $i++ for display? Why put this in the query at all, if it has nothing to do with the data? -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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: Row numbers
At 12:31 PM 2/13/03 , Luc Foisy wrote: > > I still don't understand do you mean the "actual" row > number or just a > > display number. > > > There is no relevant data or use to this number. > > > It is the row number of the returned result set, purely > > > for display. That means no field exists or should exist in the database. I only want to generate at query time. I can't use an autoincrement field since that wont work very well with results that are returned out of order and maybe not with all the data. Using variables is the best response to my question. I just dislike using them cause they are ugly to work with because of the session persistance and because I have to issue multiple queries to do the job. I only want to issue one query. Extending my previous email, you could use the fact that undefined variables are null to combine set @x=0; select (@x:=@x+1) as row_number,name,tag_length from library_master; into one query: select if(isnull(@x),@x:=1,@x:=@x+1) as row_number,name,tag_length from library_master; Of course, you'll need to rest @x back to null (or 0) if you want to run this query again in the same session... And as far as ugly, well - eye of the beholder, and all that stuff ;) -steve ++ | Steve Edberg [EMAIL PROTECTED] | | Database/Programming/SysAdmin(530)754-9127 | | University of California, Davis http://pgfsun.ucdavis.edu/ | +-- Gort, Klaatu barada nikto! --+ - 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: Row numbers (I thought of a new feature)
I thought of a new feature... XSLT's "position()"-like functionality in the new releases of MySQL. position(), in XSLT, returns position of the node in the document tree. In MySQL it would return position of the record in the returned result set (from SELECT query): Example: SELECT POSITION(), first_name, last_name WHERE last_name LIKE "a%"; Return value might be: 1 | Alpha | Beta | 2 | Aqua | Gamma | ... etc.. By default numbering would start with 1. But it can be alterled by passing argument to POSITION(). I believe it is an exciting feature. sherzod - 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: Row numbers
On 13 Feb 2003, at 15:31, Luc Foisy wrote: > Using variables is the best response to my question. I just dislike > using them cause they are ugly to work with because of the session > persistance and because I have to issue multiple queries to do the > job. Using variables seems to be the way to do what you want in MySQL. I agree with you that it's ugly, but then I don't understand why you insist on using MySQL to get the numbers. This seems like something that's trivial to do in your application, using Perl or PHP or C or whatever language you're using. Just increment a variable in the loop where you're retrieving and printing your results. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: Row numbers
> > I still don't understand do you mean the "actual" row > number or just a > > display number. > > > There is no relevant data or use to this number. > > > It is the row number of the returned result set, purely > > > for display. That means no field exists or should exist in the database. I only want to generate at query time. I can't use an autoincrement field since that wont work very well with results that are returned out of order and maybe not with all the data. Using variables is the best response to my question. I just dislike using them cause they are ugly to work with because of the session persistance and because I have to issue multiple queries to do the job. I only want to issue one query. Is there a function to give me a number that increments by one for every row returned? If the answer is no, then no need to go any further other than me putting in a request to implement such ( maybe whatever [EMAIL PROTECTED] was talking about with Oracles ROWNUM ) Thanks for your effort guys... - 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: Row numbers
Auto-inc column would be the obvious answer, but that's and intrusive answer. Jerry - Original Message - From: "Jerry" <[EMAIL PROTECTED]> To: "Luc Foisy" <[EMAIL PROTECTED]> Cc: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> Sent: Thursday, February 13, 2003 7:10 PM Subject: Re: Row numbers > I still don't understand do you mean the "actual" row number or just a > display number. > > > - Original Message - > From: "Luc Foisy" <[EMAIL PROTECTED]> > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> > Sent: Thursday, February 13, 2003 6:59 PM > Subject: RE: Row numbers > > > > There is no relevant data or use to this number. > > It is the row number of the returned result set, purely for display. > > > > I was hoping there was some kind of function just to drop a number in > there, regarless of any data that is stored in the table or regardless of > the order the resultset appears. > > > > > -Original Message- > > > From: Jerry [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, February 13, 2003 1:48 PM > > > To: Luc Foisy > > > Subject: Re: Row numbers > > > > > > > > > Have to have one in the row and select that along with the > > > query, if your > > > going to use it for some other sql command it probally should > > > be in the > > > table already > > > > > > - Original Message - > > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > > To: "Jerry" <[EMAIL PROTECTED]> > > > Sent: Thursday, February 13, 2003 5:59 PM > > > Subject: RE: Row numbers > > > > > > > > > > No language, just straight mysql > > > > > > > > > -Original Message- > > > > > From: Jerry [mailto:[EMAIL PROTECTED]] > > > > > Sent: Thursday, February 13, 2003 12:53 PM > > > > > To: Luc Foisy > > > > > Subject: Re: Row numbers > > > > > > > > > > > > > > > using what language ? or the mysql client ? > > > > > > > > > > - Original Message - > > > > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > > > > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> > > > > > Sent: Thursday, February 13, 2003 5:48 PM > > > > > Subject: Row numbers > > > > > > > > > > > > > > > > > > > > > > Is there a way to get a row number returned with any > > > select query? > > > > > > > > > > > > > > > > > > > > > > > > > > - > > > > > > 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 > - 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: Row numbers
You can use user variables; example: mysql> describe library_master; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | _id | int(10) unsigned | | PRI | NULL| auto_increment | | code| varchar(20) | | UNI | || | name| varchar(255) | YES | | NULL|| | create_date | datetime | YES | | NULL|| | tag_length | int(11) | YES | | NULL|| | notes | text | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql> set @x=0; Query OK, 0 rows affected (0.00 sec) mysql> select (@x:=@x+1) as row_number,name,tag_length from library_master; ++--++ | row_number | name | tag_length | ++--++ | 1 | Callus | 17 | | 2 | Flower | 17 | | 3 | Leaves | 17 | | 4 | Root | 17 | | 5 | Silique | 17 | | 6 | Wild flowers | 17 | | 7 | Flowers | 17 | | 8 | Flower | 17 | | 9 | Flower | 17 | ++--++ 9 rows in set (0.00 sec) However, since user variables are persistent withing a session, you have to remember to reset the value of @x; otherwise, you'll get something like this: mysql> select (@x:=@x+1) as row_number,name,tag_length from library_master; ++--++ | row_number | name | tag_length | ++--++ | 10 | Callus | 17 | | 11 | Flower | 17 | | 12 | Leaves | 17 | | 13 | Root | 17 | | 14 | Silique | 17 | | 15 | Wild flowers | 17 | | 16 | Flowers | 17 | | 17 | Flower | 17 | | 18 | Flower | 17 | ++--++ 9 rows in set (0.00 sec) -steve At 1:59 PM -0500 2/13/03, Luc Foisy wrote: There is no relevant data or use to this number. It is the row number of the returned result set, purely for display. I was hoping there was some kind of function just to drop a number in there, regarless of any data that is stored in the table or regardless of the order the resultset appears. -Original Message- From: Jerry [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 13, 2003 1:48 PM To: Luc Foisy Subject: Re: Row numbers Have to have one in the row and select that along with the query, if your going to use it for some other sql command it probally should be in the table already - Original Message - From: "Luc Foisy" <[EMAIL PROTECTED]> To: "Jerry" <[EMAIL PROTECTED]> Sent: Thursday, February 13, 2003 5:59 PM Subject: RE: Row numbers > No language, just straight mysql > > > -Original Message- > > From: Jerry [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, February 13, 2003 12:53 PM > > To: Luc Foisy > > Subject: Re: Row numbers > > > > > > using what language ? or the mysql client ? > > > > - Original Message - > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> > > Sent: Thursday, February 13, 2003 5:48 PM > > Subject: Row numbers > > > > > > > > > > Is there a way to get a row number returned with any select query? > > > > > > > > > > > > -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | SETI@Home: 1001 Work units on 23 oct 2002 | | 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... | ++ - 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: Row numbers
I think I know what you are asking for. Create an int field with auto_increment set. Make that field the first field in your table. i.e. CREATE TABLE my_table ( id int(11) auto_increment NOT NULL, other_data ... PRIMARY KEY (id) ); Then, when you want rowid for something use: SELECT _rowid, ... FROM my_table WHERE ... This will return a unique identifier for each row in _rowid. It is not a "raw pointer" like Oracle's ROWID but it will give you what you want (I think). John Griffin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 13, 2003 2:09 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: AW: Row numbers I stated to implement something like Oracle's ROWNUM ... but noone's listening ... therefore I build an UDF ... > -Ursprüngliche Nachricht- > Von: Luc Foisy [mailto:[EMAIL PROTECTED]] > Gesendet: Donnerstag, 13. Februar 2003 20:00 > An: MYSQL-List (E-mail) > Betreff: RE: Row numbers > > > There is no relevant data or use to this number. > It is the row number of the returned result set, purely for display. > > I was hoping there was some kind of function just to drop a > number in there, regarless of any data that is stored in the > table or regardless of the order the resultset appears. > > > -Original Message- > > From: Jerry [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, February 13, 2003 1:48 PM > > To: Luc Foisy > > Subject: Re: Row numbers > > > > > > Have to have one in the row and select that along with the > > query, if your > > going to use it for some other sql command it probally should > > be in the > > table already > > > > - Original Message - > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > To: "Jerry" <[EMAIL PROTECTED]> > > Sent: Thursday, February 13, 2003 5:59 PM > > Subject: RE: Row numbers > > > > > > > No language, just straight mysql > > > > > > > -Original Message- > > > > From: Jerry [mailto:[EMAIL PROTECTED]] > > > > Sent: Thursday, February 13, 2003 12:53 PM > > > > To: Luc Foisy > > > > Subject: Re: Row numbers > > > > > > > > > > > > using what language ? or the mysql client ? > > > > > > > > - Original Message - > > > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > > > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> > > > > Sent: Thursday, February 13, 2003 5:48 PM > > > > Subject: Row numbers > > > > > > > > > > > > > > > > > > Is there a way to get a row number returned with any > > select query? > > > > > > > > > > > > > > > > > > > > > - > > > > > 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 - 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: Row numbers
I still don't understand do you mean the "actual" row number or just a display number. - Original Message - From: "Luc Foisy" <[EMAIL PROTECTED]> To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> Sent: Thursday, February 13, 2003 6:59 PM Subject: RE: Row numbers > There is no relevant data or use to this number. > It is the row number of the returned result set, purely for display. > > I was hoping there was some kind of function just to drop a number in there, regarless of any data that is stored in the table or regardless of the order the resultset appears. > > > -Original Message- > > From: Jerry [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, February 13, 2003 1:48 PM > > To: Luc Foisy > > Subject: Re: Row numbers > > > > > > Have to have one in the row and select that along with the > > query, if your > > going to use it for some other sql command it probally should > > be in the > > table already > > > > - Original Message ----- > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > To: "Jerry" <[EMAIL PROTECTED]> > > Sent: Thursday, February 13, 2003 5:59 PM > > Subject: RE: Row numbers > > > > > > > No language, just straight mysql > > > > > > > -Original Message- > > > > From: Jerry [mailto:[EMAIL PROTECTED]] > > > > Sent: Thursday, February 13, 2003 12:53 PM > > > > To: Luc Foisy > > > > Subject: Re: Row numbers > > > > > > > > > > > > using what language ? or the mysql client ? > > > > > > > > - Original Message - > > > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > > > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> > > > > Sent: Thursday, February 13, 2003 5:48 PM > > > > Subject: Row numbers > > > > > > > > > > > > > > > > > > Is there a way to get a row number returned with any > > select query? > > > > > > > > > > > > > > > > > > > > > - > > > > > 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
AW: Row numbers
I stated to implement something like Oracle's ROWNUM ... but noone's listening ... therefore I build an UDF ... > -Ursprüngliche Nachricht- > Von: Luc Foisy [mailto:[EMAIL PROTECTED]] > Gesendet: Donnerstag, 13. Februar 2003 20:00 > An: MYSQL-List (E-mail) > Betreff: RE: Row numbers > > > There is no relevant data or use to this number. > It is the row number of the returned result set, purely for display. > > I was hoping there was some kind of function just to drop a > number in there, regarless of any data that is stored in the > table or regardless of the order the resultset appears. > > > -Original Message- > > From: Jerry [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, February 13, 2003 1:48 PM > > To: Luc Foisy > > Subject: Re: Row numbers > > > > > > Have to have one in the row and select that along with the > > query, if your > > going to use it for some other sql command it probally should > > be in the > > table already > > > > - Original Message - > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > To: "Jerry" <[EMAIL PROTECTED]> > > Sent: Thursday, February 13, 2003 5:59 PM > > Subject: RE: Row numbers > > > > > > > No language, just straight mysql > > > > > > > -Original Message- > > > > From: Jerry [mailto:[EMAIL PROTECTED]] > > > > Sent: Thursday, February 13, 2003 12:53 PM > > > > To: Luc Foisy > > > > Subject: Re: Row numbers > > > > > > > > > > > > using what language ? or the mysql client ? > > > > > > > > - Original Message - > > > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > > > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> > > > > Sent: Thursday, February 13, 2003 5:48 PM > > > > Subject: Row numbers > > > > > > > > > > > > > > > > > > Is there a way to get a row number returned with any > > select query? > > > > > > > > > > > > > > > > > > > > > - > > > > > 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: Row numbers
There is no relevant data or use to this number. It is the row number of the returned result set, purely for display. I was hoping there was some kind of function just to drop a number in there, regarless of any data that is stored in the table or regardless of the order the resultset appears. > -Original Message- > From: Jerry [mailto:[EMAIL PROTECTED]] > Sent: Thursday, February 13, 2003 1:48 PM > To: Luc Foisy > Subject: Re: Row numbers > > > Have to have one in the row and select that along with the > query, if your > going to use it for some other sql command it probally should > be in the > table already > > - Original Message - > From: "Luc Foisy" <[EMAIL PROTECTED]> > To: "Jerry" <[EMAIL PROTECTED]> > Sent: Thursday, February 13, 2003 5:59 PM > Subject: RE: Row numbers > > > > No language, just straight mysql > > > > > -Original Message- > > > From: Jerry [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, February 13, 2003 12:53 PM > > > To: Luc Foisy > > > Subject: Re: Row numbers > > > > > > > > > using what language ? or the mysql client ? > > > > > > - Original Message - > > > From: "Luc Foisy" <[EMAIL PROTECTED]> > > > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> > > > Sent: Thursday, February 13, 2003 5:48 PM > > > Subject: Row numbers > > > > > > > > > > > > > > Is there a way to get a row number returned with any > select query? > > > > > > > > > > > > > > > > - > > > > 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: Row numbers
: Is there a way to get a row number returned with any select query? Row number is not available for some queries (ex.DELETE FROM table_name) for efficiency. You can disable it with a dummy WHERE clause: DELETE FROM table_name WHERE 1 > 0; It will be slower, but you'll get the desired result - 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
Row numbers
Is there a way to get a row number returned with any select query? - 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
