How to get limited record in sql as ” limit ” in Mysql?

We are having the limit keyword in mysql so that we can limit the data so that we can improve the performance of the application.

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY col_name) as row FROM table_name ) a WHERE row > startindex  and row <= lastindex;

the above syntax we need to follow in order to limit the records in SQL

Why I  need to make my query more complex by implementing this and where can i implement this?

  • While displaying large amount of data we are implementing the concept of paging. When we are using the paging concept why we need to bring all the data for displaying just 10 records it doesn’t make any sense of writing such type of code as well as queries too.
  • In order to overcome such scenarios we just need to use simple things which make the application so better as well as the code looks to be more specilazed.
  • Here i just used the concept of rownumber in sql.
  • Such Queries makes a lot of difference between others.
  • Here the start index and end index will be send from the front end.

Example for implementing the Query

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY col_name) as row FROM table_name ) a WHERE row > startindex  and row <= lastindex;

Difference of startindex and lastindex will gives the total number of records to be displayed…

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) as row FROM groupDetails ) a WHERE row > 0 and row <= 10;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s