0

I want to fetch latest entries in a table that is containing more than 1,000,000 entries. I am using this query for an instance 

SELECT id FROM tablea WHERE flag = "N" ORDER BY id LIMIT 510045,200;

and it gives me entries starting from 510045 and ending at 510245. Can MYSQL have something where I can get entries starting from 510245 to 510045. I mean fetching the data from the last and I don't want to fetch only 200 entries.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Vibhor Bhatnagar
  • 33
  • 1
  • 9

4 Answers4

1

You should ORDER BY desc and, if you want, LIMIT for define how many entries you want.

Example:

SELECT id FROM tablea WHERE flag = "N" ORDER BY id DESC; 
-- this will help to find the last entries

But if you want to have the latest entries that you didn't get in last query, you should always hold the value of the last ID, and use it as reference to next check.

Example (Supposing the last ID of the last query execution was 55304):

SELECT id FROM tablea WHERE flag = "N" WHERE id > 55304 ORDER BY id DESC;
Gabriel Gomes
  • 348
  • 1
  • 8
  • So I am using a type of multitasking process in which I have to run this query on different systems at one time. Due to some other constraints, I can't use this query. This would have been the most simpler way, but I can't use this. – Vibhor Bhatnagar Jul 23 '17 at 19:44
  • @ClaudioPinto its just an example. I tried to figure out all situations. Its a not fair -1 – Gabriel Gomes Jul 23 '17 at 19:46
  • @VibhorBhatnagar if you are running this kind of query in multitasking environment, maybe you should split this check into a single process, that caches the result in some place. In this approach multiple tasks could get the cached result. So this will keep the consistence. – Gabriel Gomes Jul 23 '17 at 19:48
  • @ClaudioPinto yes this is not -1, he was giving the right solution, but I can't use it. I explained what i need. My current query is still giving me last entries because of Limit 510045, 200 but i want the same thing in reverse order. – Vibhor Bhatnagar Jul 23 '17 at 19:49
  • @GabrielGomes one way is that i get all the ids from this and i can reverse the order from code, but i dont want to use the code. How i can i do the same in mysql. – Vibhor Bhatnagar Jul 23 '17 at 19:52
  • ORDER BY `id` ASC? – Ian Brindley Jul 23 '17 at 19:52
  • @IanBrindley I am not using ASC or DESC, so by default it will take ASC. – Vibhor Bhatnagar Jul 23 '17 at 19:54
0

If what you want is rows where the id is greater than 510245 just use the where condition 

Select * FROM table WHERE flag = 'n' AND id > 510245

This should do it

Claudio Pinto
  • 389
  • 2
  • 9
  • No its not at all related to ids thats why I edited the question and removed id from there. The problem is current query is returning data from 510045 to 510245 and i want it from 510245 to 510045.(this is not id, you can say this is a sequence of the query result) – Vibhor Bhatnagar Jul 23 '17 at 19:46
  • Can you make it clear what this number is? In MySQL Limit does not allow you to star from a position and get everything from there. Your solution might have to be use a bug limit like LIMIT 510245, 1000000 – Claudio Pinto Jul 23 '17 at 19:53
  • To get 510245 to 510045 use LIMIT 510045,200 – Claudio Pinto Jul 23 '17 at 19:55
  • 510245 is the total number of rows where flag = "N" and i want to process it from last to the position 510045. I hope it will help you understand ? – Vibhor Bhatnagar Jul 23 '17 at 19:57
  • `To get 510245 to 510045 use LIMIT 510045,200` I am using LIMIT 510045,200 but it will give me data from 510045 to 510245 – Vibhor Bhatnagar Jul 23 '17 at 20:00
  • You can't use offset without a limit so you either know the id of offset 510045 and use it in where clause or you have to use a very large limit. Something like the biggest possible but Int – Claudio Pinto Jul 23 '17 at 20:16
  • @strawberry in main comments gave the same solution to use subquery and it worked. :) Thanks claudio and gabrielgomes for your help :) – Vibhor Bhatnagar Jul 23 '17 at 20:22
0

As i understand your requirement . you may try it.

Select * FROM table WHERE flag = 'N' AND id > 510245 ORDER BY id
vjy tiwari
  • 843
  • 1
  • 5
  • 17
0

One more thing here is

The version i was working on was not supporting subquery containing LIMIT. So, @strawberry Thanks for giving me the hint to solve the question. But I used this sub query as inner join table(explained below)

SELECT id FROM tablea AS T1

INNER JOIN (SELECT id FROM tablea WHERE flag = "N" ORDER BY id LIMIT 510045,200) AS T2

WHERE T2._id = T1._id ORDER BY T2._id DESC;

This gave me the required results. Thanks everyone for your help !!

Vibhor Bhatnagar
  • 33
  • 1
  • 9