I've got a table with
'id' because the primary area and hang to auto increment..
'title' of type varchar ..
'category' of type varchar ..
Table contents ::.
Now, I wish to choose latest rows where category="tv" i.e row 5, row 6 only
Case a good example the actual table may have many groups, I really hope I'm obvious in the manner I'm putting forward my problem...
in the solutions given ...this question is showing results the way i would like them ::.
SELECT * FROM test WHERE id > (SELECT id FROM test WHERE category !="tv" ORDER BY id DESC LIMIT 1)
however when i add another row to my table the query proven no results that we can realise why..
$$$$I would like the ultimate block of the particular category $$$$
I believe this is exactly what you'll need.
SELECT * FROM table WHERE id > (SELECT id FROM table WHERE category != (SELECT category FROM table ORDER BY id DESC LIMIT 1) ORDER BY id DESC LIMIT 1);
The interior most Choose is locating the group of the final category block. The center choose is locating the ID from the last row this is a different category than that. Then your outer choose is choosing all rows next id. So, it'll find all of the rows that are members of the ultimate category block.
SELECT * FROM table WHERE category = 'radio' && id > (SELECT id FROM table WHERE category != 'radio' && id < (SELECT id FROM table WHERE category = 'radio' ORDER BY id DESC LIMIT 1) ORDER BY id DESC LIMIT 1)
I believe which should do the thing you need. Observe that you will find 3 places you have to make the category you are searching for (within this example it's searching for 'radio').