SQL Limit start from

Question

I have this query

SELECT * FROM Posts WHERE Topics = '2hnsk6i2iqgoh0c6kdbyd' ORDER BY Number DESC limit 0,25

Which would would the following results

2hnsk6i2iqgoh0c6kdbyd   admin   coecxrj0vqkow46h6243v   17:28   2013/01/02  1357147682
2hnsk6i2iqgoh0c6kdbyd   admin   uvde2od4fffjyri2se3v6   17:28   2013/01/02  1357147682
2hnsk6i2iqgoh0c6kdbyd   admin   7nvk4tamdcmmqxc8xxzns   17:28   2013/01/02  1357147682
2hnsk6i2iqgoh0c6kdbyd   admin   46ibv785bpbcqin5csgqt   17:28   2013/01/02  1357147681
2hnsk6i2iqgoh0c6kdbyd   admin   nprsukm2v7adx62psizcn   17:28   2013/01/02  1357147681
2hnsk6i2iqgoh0c6kdbyd   admin   5jffjmcrywv28ypxf88m8   17:28   2013/01/02  1357147681
2hnsk6i2iqgoh0c6kdbyd   admin   i5ejx2hqf7m6m4ybc0b43   17:28   2013/01/02  1357147680
2hnsk6i2iqgoh0c6kdbyd   admin   m72qzizi0ru4z6e2vz6vq   17:28   2013/01/02  1357147680
2hnsk6i2iqgoh0c6kdbyd   admin   3mf83zzvow6wt2gfhb6ub   17:28   2013/01/02  1357147680
2hnsk6i2iqgoh0c6kdbyd   admin   ct2j2u8xx8ny8x3u27sjc   17:27   2013/01/02  1357147679
2hnsk6i2iqgoh0c6kdbyd   admin   sdbx6iyburbeg0ie0magt   17:27   2013/01/02  1357147679
2hnsk6i2iqgoh0c6kdbyd   admin   5hir63ok3kq06g3upqbhk   17:27   2013/01/02  1357147679
2hnsk6i2iqgoh0c6kdbyd   admin   4pvuwgqazd84yck7adoxa   17:27   2013/01/02  1357147678
2hnsk6i2iqgoh0c6kdbyd   admin   oiv6quuyrpwe8bgirjr4i   17:27   2013/01/02  1357147678
2hnsk6i2iqgoh0c6kdbyd   admin   xcutah6426zesq323gfwx   17:27   2013/01/02  1357147678
2hnsk6i2iqgoh0c6kdbyd   admin   hgfqab040h4k55bjedpkt   17:27   2013/01/02  1357147678
2hnsk6i2iqgoh0c6kdbyd   admin   oonp5r3np5knm5d4ohm55   17:27   2013/01/02  1357147677
2hnsk6i2iqgoh0c6kdbyd   admin   cbckqm8qi2oaimir2epzi   17:27   2013/01/02  1357147677
2hnsk6i2iqgoh0c6kdbyd   admin   vtsaqex6wfdmh8ioceuvn   17:27   2013/01/02  1357147677
2hnsk6i2iqgoh0c6kdbyd   admin   0ubs8ur2wo55ohfuipdo0   17:27   2013/01/02  1357147677
2hnsk6i2iqgoh0c6kdbyd   admin   8tvorn65epoaict0fp26z   17:27   2013/01/02  1357147676
2hnsk6i2iqgoh0c6kdbyd   admin   soctqr26i7aycza2u73pi   17:27   2013/01/02  1357147676
2hnsk6i2iqgoh0c6kdbyd   admin   8ukjwmao2qtt374g6p7ej   17:27   2013/01/02  1357147676
2hnsk6i2iqgoh0c6kdbyd   admin   4wyinun2ki45h6ww73qg6   17:27   2013/01/02  1357147675
2hnsk6i2iqgoh0c6kdbyd   admin   3dpsbg2t7s7fj2hbiyenk   17:27   2013/01/02  1357147675

That brings up forums posts in a topic, but say the user is looking through post history, and wants to use that to go to the topic, i want some code like

SELECT * 
FROM Posts 
WHERE Topics = '2hnsk6i2iqgoh0c6kdbyd' 
ORDER BY Number DESC limit (SELECT * FROM POSTS WHERE POST = 'soctqr26i7aycza2u73pi'),25.

That of course doesn't work, but i don't know if you could, and if so, how i would be able to get that piece of code to work

As there is some confusion i'll try and simplify it.

I want to run query Topics = '$topic' and then inside that pull Post = '$post', which will return one value, i want the line number of that to be used in the Limit to get use as the starting point for returning the data, or essentially similar code to do the same job

MySQL version 5.5.29-log

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using.... — marc_s, Jan 02 '13 at 21:33
I don't quite understand the question. Also, don't use `SELECT *` as it is poor practice; always specify a column list. — Kermit, Jan 02 '13 at 21:35
@marc_s I think OP is using MySQL, as LIMIT is specific to that, AFAIK — RonaldBarzell, Jan 02 '13 at 21:38
MySQL and SQLite support the `LIMIT x,y` syntax. MySQL and PostgreSQL support `LIMIT y OFFSET x` syntax. — Bill Karwin, Jan 02 '13 at 21:40
@RonaldBarzell: good *guess* - I wish the OP would **specify** explicitly .... — marc_s, Jan 02 '13 at 21:44
It's not clear what you're trying to do, though. The parameters to LIMIT are row numbers, what does it mean to replace it with `(SELECT * FROM ...)`? — Barmar, Jan 02 '13 at 21:56
um, @RonaldBarzell, `LIMIT` is NOT specific to MySQL, it's a built in SQL function. I think what OP is looking for is `least` — Colleen, Jan 02 '13 at 22:02
I don't think LIMIT is standard SQL. I'll need to double check, but I know some big SQL engines do not support it, but support different "equivalents" like TOP. — RonaldBarzell, Jan 02 '13 at 22:21
I'd never used least, but with Limit, i was thinking about a way for the a subquery to be able to return the line number of the one result and that would be where it starts — Craig Weston, Jan 02 '13 at 22:22
@RonaldBarzell, no, `LIMIT` is not standard SQL, nor is `TOP`, or `FIRST`/`SKIP`. The way to do this in standard SQL is with windowing functions. — Bill Karwin, Jan 02 '13 at 23:10

Colleen · Answer 1 · 2013-01-03T00:11:32.210

0

is least what you are looking for? I.e. the lesser of 25 and the number of replies to that post?

SELECT * 
FROM Posts 
WHERE User = 'admin' 
ORDER BY Number DESC limit least((SELECT count(*) FROM POSTS WHERE POST = 'soctqr26i7aycza2u73pi'),25);

EDIT: WILD misunderstanding of what OP was asking for. Sounds like what is desired is to grab the post number via the post column, and get the next 25 posts.

So I think what OP ACTUALLY wants is

select *
from posts
where user = 'admin'
order by number desc offset (select count(*) from posts where number > (select number from posts where post = 'soctqr26i7aycza2u73pi') )
limit 25;

note that I have number > instead of < because of OP's desc ordering.

RE-EDIT: turns out you can't do subselects in limit or offset statements (http://stackoverflow.com/questions/8181727/how-to-use-subquery-to-define-mysql-select-limit-offset) without some hacky magic.

In discussion, OP mentioned he is using PHP for this. My recommendation was to store the result of select count(*) from posts where number > (select number from posts where post = 'soctqr26i7aycza2u73pi' as a separate variable and plug it into offset later, or just loop over the results of the outermost query from there to 25.

edited Jan 03 '13 at 00:11

answered Jan 02 '13 at 22:04

Colleen

23,899
12
45
75

Shouldn't there be a `COUNT(*)` in the subquery to get the number of replies? – Barmar Jan 02 '13 at 22:08
I just tried that statement is PHPMyAdmin, and got "#1327 - Undeclared variable: least" – Craig Weston Jan 02 '13 at 22:09
haven't actually used `least` (have used `greatest`, so I'm assuming the same rules apply...)-- does it work if you move the `least()` inside the select? i.e. `limit (select least(count(*), 25).....`? – Colleen Jan 02 '13 at 22:16
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT least (count(*) FROM POSTS WHERE POST = 'soctqr26i7aycza2u73pi'),25)' at line 4 Is the error i get when i try that – Craig Weston Jan 02 '13 at 22:19
your parentheses are in the wrong place. you need to move the one after the number in `post=` and move it to the end. `limit (select least(count(*) , 25))` – Colleen Jan 02 '13 at 22:24
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT least (count(*) FROM POSTS WHERE POST = 'soctqr26i7aycza2u73pi',25))' at line 4 Same problem – Craig Weston Jan 02 '13 at 22:26
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22086/discussion-between-colleen-and-craig-weston) – Colleen Jan 02 '13 at 22:27

score 0 · Answer 2 · answered Jan 03 '13 at 01:15

0

SELECT * FROM Posts 
WHERE User = 'admin' 
AND Number >= ( SELECT Number FROM POSTS WHERE POST = 'soctqr26i7aycza2u73pi')
ORDER BY Number
LIMIT 25

This is the solution for the problem, thanks for those who tried to help

answered Jan 03 '13 at 01:15

Craig Weston

141
2
4
10

SQL Limit start from

2 Answers2