MySQL problem - GoFuckYourself.com

Tipsy · 05-05-2002, 02:30 PM

I got a simple problem (at least it should be).
I just need to select a record from a table by it's location in the table. ie I may need to look at the 6th record or the 10th record. However, I don't wanna create a field for this. Can this be done? I've looked everywhere and it's either so simple that people haven't bothered to say how or it can't be done with a single command.

Any help extemeley apprecited. BTW - I tried format c: and it didn't help

Fletch XXX · 05-05-2002, 02:33 PM

have a link to MySQl info here http://superbooger.com/sponsors.html

Tipsy · 05-05-2002, 02:36 PM

Thanks Fletch. I've been at that site most of the evening and can now do just about every kind of mega complex search and sorting I want except the (should be simple) thing above. Maybe I need a drink....now where's that French beer gone.....

boldy · 05-05-2002, 02:43 PM

Just end your query with

LIMIT 10, 1
will return the 11th record

LIMIT 10, -1
will return the 11th record through end of table

Cheers!

pipp · 05-05-2002, 02:44 PM

boldy solution was much smarter

Tipsy · 05-05-2002, 02:46 PM

Knew it had to be simple. Ta very much

kai-tracid · 05-06-2002, 04:23 AM

select table
from shithead
where you're fucked up

this will do it!

AdultWire · 05-06-2002, 07:45 AM

Right.. keeping in mind of course, that the order of rows in a table may change arbitrarily at any time.. specially after table optimizations.

Babaganoosh · 05-06-2002, 09:38 AM

Quote:

Originally posted by kai-tracid
select table
from shithead
where you're fucked up

this will do it!

select moron
from shortbus
where comment = 'stupid'
order by rand()

XXXManager · 05-06-2002, 10:00 AM

Tipsy. Your questions is a little misleading...
There is no such thing as a position in tables - in relational databases. You can not say a record is the 10th (or whatever) record in a table (or a select). The fact that a record is the 1st or 10th in a select that is NOT an "ORDER"ed select is a matter a chance and you SHOULD NOT - under any circumstances - count on it being consistently so..
I recommend the folowwing...
1) order(sort) the table by someithing - anything would do if you dont have a valid field (like ID). "ORDER BY ____ [DESC]". ____ is a field name, DESC is optional for reverse ordering
2) LIMIT x,y - like boldy suggested will give you the -1 record after skipping x records. -1 records means: 1 record BACK. if you do LIMIT 10,5 meains records 11..15 in the select result.

Not using ORDER is also legitimate but DONT count of results being consistent. It will probably be so, but if you "check" the table or restore it from backup - results may change. actually ORDER is quite natural in many/most queries anyway - but if you dont care about consistency - not using ORDER may save you time/cpu

buran · 05-06-2002, 12:04 PM

Yes, you should always use ORDER BY when using LIMIT or the results aren't predictable.

I'm trying to get tech-chatter for porn people in channel #porntech on irc.theadultwebmaster.com / chatnet.sextracker.com

Tipsy · 05-06-2002, 02:22 PM

Quote:

Originally posted by XXXManager
Tipsy. Your questions is a little misleading...
There is no such thing as a position in tables - in relational databases. You can not say a record is the 10th (or whatever) record in a table (or a select). The fact that a record is the 1st or 10th in a select that is NOT an "ORDER"ed select is a matter a chance and you SHOULD NOT - under any circumstances - count on it being consistently so..
I recommend the folowwing...
1) order(sort) the table by someithing - anything would do if you dont have a valid field (like ID). "ORDER BY ____ [DESC]". ____ is a field name, DESC is optional for reverse ordering
2) LIMIT x,y - like boldy suggested will give you the -1 record after skipping x records. -1 records means: 1 record BACK. if you do LIMIT 10,5 meains records 11..15 in the select result.

Not using ORDER is also legitimate but DONT count of results being consistent. It will probably be so, but if you "check" the table or restore it from backup - results may change. actually ORDER is quite natural in many/most queries anyway - but if you dont care about consistency - not using ORDER may save you time/cpu

Fair point. Thanks for the clarification

AdultWire · 05-06-2002, 03:18 PM

Heh.. no-one ever gets it when I say it.. haha.

Tipsy · 05-06-2002, 03:22 PM

Quote:

Originally posted by AdultWire
Right.. keeping in mind of course, that the order of rows in a table may change arbitrarily at any time.. specially after table optimizations.

I missed your post - thanks

Actually I was surprised this was still going.

The query is immediately after finding the place in the table and seems to work at least 99.9% of the time which is more than enough for the function it's used for. Sorting the records by ORDER would create far more problems than it solved with some major re-writing being involved. Handy for future ref though

05-05-2002, 02:30 PM	#1
Tipsy Confirmed User Join Date: Jul 2001 Location: See sig Posts: 6,989	MySQL problem I got a simple problem (at least it should be). I just need to select a record from a table by it's location in the table. ie I may need to look at the 6th record or the 10th record. However, I don't wanna create a field for this. Can this be done? I've looked everywhere and it's either so simple that people haven't bothered to say how or it can't be done with a single command. Any help extemeley apprecited. BTW - I tried format c: and it didn't help __________________ Ignorance is never bliss.

05-05-2002, 02:33 PM	#2
Fletch XXX GFY HALL OF FAME DAMMIT!!! Join Date: Jan 2002 Location: that 504 Posts: 60,840	have a link to MySQl info here http://superbooger.com/sponsors.html __________________ Want an Android App for your tube, membership, or free site? Need banners or promo material? Hit us up (ICQ Fletch: 148841377) or email me fletchxxx at gmail.com - recent work - About me

05-05-2002, 02:36 PM	#3
Tipsy Confirmed User Join Date: Jul 2001 Location: See sig Posts: 6,989	Thanks Fletch. I've been at that site most of the evening and can now do just about every kind of mega complex search and sorting I want except the (should be simple) thing above. Maybe I need a drink....now where's that French beer gone..... __________________ Ignorance is never bliss.

05-05-2002, 02:43 PM	#4
boldy Macdaddy coder Industry Role: Join Date: Feb 2002 Location: MacDaddy pimp coder Posts: 2,806	Just end your query with LIMIT 10, 1 will return the 11th record LIMIT 10, -1 will return the 11th record through end of table Cheers! __________________ MacDaddy Coder.

05-05-2002, 02:44 PM	#5
pipp Confirmed User Join Date: Oct 2001 Location: Where the sun don't shine Posts: 1,185	boldy solution was much smarter Last edited by pipp; 05-05-2002 at 02:45 PM..

05-05-2002, 02:46 PM	#6
Tipsy Confirmed User Join Date: Jul 2001 Location: See sig Posts: 6,989	Knew it had to be simple. Ta very much __________________ Ignorance is never bliss. Last edited by Tipsy; 05-05-2002 at 02:48 PM..

05-06-2002, 04:23 AM	#7
kai-tracid Confirmed User Join Date: Apr 2001 Location: Bali, Indonesia Posts: 331	select table from shithead where you're fucked up this will do it! __________________ • [email protected] • Hit him again Jack!He's crazy

05-06-2002, 07:45 AM	#8
AdultWire Confirmed User Join Date: Feb 2002 Location: Toronto, ON Posts: 962	Right.. keeping in mind of course, that the order of rows in a table may change arbitrarily at any time.. specially after table optimizations. __________________ SIG TOO BIG! Maximum 120x60 button and no more than 3 text lines of DEFAULT SIZE and COLOR. Unless your sig is for a GFY top banner sponsor, then you may use a 624x80 instead of a 120x60.

05-06-2002, 10:00 AM	#10
XXXManager So Fucking Banned Join Date: Mar 2002 Location: Far out in the uncharted backwaters of the unfashionable end of the Western Spiral arm of the Galaxy Posts: 893	Tipsy. Your questions is a little misleading... There is no such thing as a position in tables - in relational databases. You can not say a record is the 10th (or whatever) record in a table (or a select). The fact that a record is the 1st or 10th in a select that is NOT an "ORDER"ed select is a matter a chance and you SHOULD NOT - under any circumstances - count on it being consistently so.. I recommend the folowwing... 1) order(sort) the table by someithing - anything would do if you dont have a valid field (like ID). "ORDER BY ____ [DESC]". ____ is a field name, DESC is optional for reverse ordering 2) LIMIT x,y - like boldy suggested will give you the -1 record after skipping x records. -1 records means: 1 record BACK. if you do LIMIT 10,5 meains records 11..15 in the select result. Not using ORDER is also legitimate but DONT count of results being consistent. It will probably be so, but if you "check" the table or restore it from backup - results may change. actually ORDER is quite natural in many/most queries anyway - but if you dont care about consistency - not using ORDER may save you time/cpu

05-06-2002, 12:04 PM	#11
buran Confirmed User Join Date: Mar 2002 Location: how'd I get here? Posts: 264	Yes, you should always use ORDER BY when using LIMIT or the results aren't predictable. I'm trying to get tech-chatter for porn people in channel #porntech on irc.theadultwebmaster.com / chatnet.sextracker.com __________________ [this signature intentionally left blank]

05-06-2002, 03:18 PM	#13
AdultWire Confirmed User Join Date: Feb 2002 Location: Toronto, ON Posts: 962	Heh.. no-one ever gets it when I say it.. haha. __________________ SIG TOO BIG! Maximum 120x60 button and no more than 3 text lines of DEFAULT SIZE and COLOR. Unless your sig is for a GFY top banner sponsor, then you may use a 624x80 instead of a 120x60.