sql select row from index

Question

if i had 10 rows which fits this description on mysql

SELECT id, nav, img 
FROM mytable 
WHERE id='$id' 
ORDER BY pageDisplayNum ASC;

pageDisplayNum may not be in numeric order, meaning 1, 2, 5, 10, 16, 22 etc...
q: how can i choose the 3rd or 6th item from this list

the index number would be coming in from php as a variable
i read about TOP but this didnt work either

SELECT TOP $num 
id, nav, img 
FROM mytable 
WHERE id='$id'
ORDER BY pageDisplayNum ASC;

What database are you using? The best answer is to use the row_number() function, but mysql does not support this function. Instead you have to use a self-join. — Gordon Linoff
– Gordon Linoff, Commented May 13, 2012 at 3:43

bitoshi.n · Accepted Answer · 2012-05-13 04:04:27Z

2

This for 7th item

SELECT id, nav, img 
FROM mytable 
WHERE id='$id' 
ORDER BY pageDisplayNum ASC;
LIMIT 6,1

You can add LIMIT offset,row_count query

edited May 13, 2012 at 4:04

answered May 13, 2012 at 3:45

bitoshi.n

2,3381 gold badge16 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vikas Over a year ago

You might want to change offset to 5. LIMIT 6, 1 will output 7th row, not 6th.

Vikas · Accepted Answer · 2012-05-13 03:46:39Z

1

You can use LIMIT <offset>, <#rows> To select the row you want:

SELECT id, nav, img 
FROM mytable 
WHERE id='$id' 
ORDER BY pageDisplayNum ASC
LIMIT 2, 1

answered May 13, 2012 at 3:46

Vikas

8,9585 gold badges41 silver badges48 bronze badges

Collectives™ on Stack Overflow

sql select row from index

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related