-1

I am trying to grab a specific row from a MySQL table based on its position. Let's say I have these records:

+-------------------+
|   ID   |   Data   |
+--------+----------+
|      1 | Apple    |
|      2 | Orange   |
|      3 | Kiwi     |
|      4 | Lobster  |
|     25 | Chicken  |
|     26 | Banana   | <----
|     27 | Melon    |
+--------+----------+

Now, I want to grab the 6th record from this table, but I don't know what the ID is, or what the Data column contains. All I know is that it is the 6th record in the table. How would I go about this?

sashkello
  • 17,306
  • 24
  • 81
  • 109
Alexander Johansen
  • 522
  • 1
  • 14
  • 28
  • 2
    you cant, there's no internal 'order' in a db . anything that requires this means something else is fundamentally wrong –  Feb 19 '14 at 23:50
  • See https://dev.mysql.com/doc/refman/5.0/en/select.html#idm47360244378112 – Sam Dufel Feb 19 '14 at 23:52
  • It's actually a whole lot more complicated than @Dagon's answer, but that sure is a sweet summary – Strawberry Feb 19 '14 at 23:53
  • 1
    well i was restricted on space, and the point is any code that 'needs' this is wrong wrong wrong –  Feb 19 '14 at 23:54
  • May I ask why you would ever need this? – Trent Feb 19 '14 at 23:58
  • I am building a info screen application. And it must know the index of the record. And not the ID. This is because it will cause trouble once i delete a record. – Alexander Johansen Feb 20 '14 at 00:00
  • Why does everyone seem so hostile? Im not an expert in this... All i want is help. Sorry for asking. – Alexander Johansen Feb 20 '14 at 00:02
  • 1
    you need to change how you do things so you have an id to use. there simply is no inherit order in the db –  Feb 20 '14 at 00:04
  • You don't know the ID, but you do know the value of `Data` column. Even though I don't see any reason why you ever would need this, you can do `SELECT * FROM table_name WHERE Data LIKE "Banana"` – Mike Feb 20 '14 at 00:07
  • Alright... Im not wasting my time here anymore. The application im writing does not require to know any values. All it does is read the database downwards, and it is using Index count to figure out what row it last was reading from. Why is that so hard to just take in? I am a total noob at MySQL and im sorry if this question was stupid and all. Anyway. I am not wasting my time here anymore. Asked a mod to delete this question. – Alexander Johansen Feb 20 '14 at 00:25
  • 1
    sorry, you are missing the point, there is **no** inherit down or up, you MUST define that in the db structure yourself. As a self confessed "total noob" you should be more open to the advise of others –  Feb 20 '14 at 00:28
  • 1
    Sounds like @DJZorrow wants to grab the Xth row. This would have applications in selecting the Xth entry in a contest, etc. Yes, there 100% should be a column to store the order of insertion but this is a super simplified case. Don't take it personally, you gotta start somewhere, and every one of us was a noob at some point. – Brett DeWoody Feb 20 '14 at 00:32
  • This is actually not a bad question per se, as almost all newbies fall into this fault of reasoning. I couldn't find an exact duplicate of this question. If there is none, I believe it should be reopened and a better answer which is actually correct is due, because all the rant in comments doesn't REALLY answer the question, just looks like chit-chat. The current closing reason is also wrong, as it is pretty clear what is asked. – sashkello Feb 20 '14 at 03:25

1 Answers1

-1

Couldn't you use use the LIMIT clause:

SELECT ID, Data
  FROM TABLE
  LIMIT 5,1

Here's a SQLFiddle.

Brett DeWoody
  • 59,771
  • 29
  • 135
  • 184
  • Thank you! Did the trick! I don't know why it was downvoted though? Is this bad practice or something? – Alexander Johansen Feb 19 '14 at 23:59
  • 2
    yes it is bad practice, there is no guarantee this will work as you expect –  Feb 20 '14 at 00:03
  • 1
    Despite being a strange question, perhaps even flawed structure, @DJZarrow somehow knows (for now) the position of the record. He wants to grab a row based on when it was inserted into the table (more or less). In reality there should be an `ORDER BY` clause based on date, auto inc, or something to guarantee the order is truly the order of insertion. But we're glossing over a lot of things here, sooo.... – Brett DeWoody Feb 20 '14 at 00:06
  • DELETE the records 'Apple', 'Orange' and 'Kiwi' ... reinsert them with the same ID numbers ... now find 'Banana' - granted, you've answered the question but the question is ... weird ;) – CD001 Feb 20 '14 at 00:07
  • Have a look at this answer to similar problem http://stackoverflow.com/a/1949663/3325500 – Mike Feb 20 '14 at 00:10
  • This will work just fine for the application i am currently writing. It does not matter what data is there. But what does matter is that i keep track of the current row it is reading from. Its not more complicated than that. And this answer did just what i asked for. And besides: There is no security to worry about either if that is an issue. Because this will not run over the internet. Only LAN. – Alexander Johansen Feb 20 '14 at 00:10
  • 1
    why not add a row field to the db then? –  Feb 20 '14 at 00:13
  • 2
    Ideally there would be a row field, date field or something to maintain insertion, or some other, order. Two lessons in one @DJZorrow :) – Brett DeWoody Feb 20 '14 at 00:22
  • Alright people. Ive been using this answer in my software for the past month now. Everything works fine and as expected. Not a single crash or misbehavior. So why this is not an acceptable answer to you is a mindblowing mystery for me. – Alexander Johansen Apr 08 '14 at 13:18