SQL Server - simplifying a query - Asked By SVK N on 14-Feb-12 12:46 AM

 public bool Sebind(int pageindex, int RowsInPage)
    {
      int totalRows = Convert.ToInt32(Session["TotalRows"]);
      int finRow = (pageindex + 1) * RowsInPage;
      if (pageindex >= totalRows / RowsInPage)
      {
        finRow = totalRows;
        RowsInPage = totalRows % RowsInPage;
      }
      
        SqlDataSource1.SelectCommand =  "select * from (select top " + RowsInPage.ToString() + " * from (select top (" + finRow.ToString() + ") * from Customers order by CustomerID ASC)as T1 order by T1.CustomerID DESC) as T2 order by T2.CustomerID ASC";



i have the above sql query, though its working fine how can the query be simplified with the same outupt
Chintan Vaghela replied to SVK N on 14-Feb-12 12:58 AM
Hello,

Simpliefied your query as following way

select top " + finRow.ToString() + " * from Customers  order by CustomerID  ASC



Hope this helpful
Web Star replied to SVK N on 14-Feb-12 12:59 AM
Let see below first of all one sub query you are putting without any need
if you remove the red marked string from your original query that also give you same result set only just need to change order by ASC
select * from (select top " + RowsInPage.ToString() + " * from (select top (" + finRow.ToString() + ") * from Customers order by CustomerID ASC)as T1 order by T1.CustomerID DESC) as T2 order by T2.CustomerID ASC 

change it to as follows

select top " + RowsInPage.ToString() + " * from (select top (" + finRow.ToString() + ") * from Customers order by CustomerID ASC)as T1 order by T1.CustomerID  ASC

Both are giving same result so you just remove one sub query burden from original one. These two sub query you needed because you have two differnt top condition one with RowsInpage and another with finRow so use above one is optimized one

Somesh Yadav replied to SVK N on 14-Feb-12 01:46 AM
Hi ,

Try this,

select top " + RowsInPage.ToString() + " * from (select top (" + finRow.ToString() + ") * from Customers order by CustomerID ASC)as T1 order by T1.CustomerID  ASC

Hope it helps you.
SVK N replied to Web Star on 14-Feb-12 02:13 AM
it not working as desired
i mean the query is not getting executed correctly with paging when i click next
i need to display teh next set of records
but it remains to its inital position
Web Star replied to SVK N on 14-Feb-12 03:39 AM
it means in your code there are not chaging the variable value which you are using with top in query so you need to debug your code and check why they did not change on next click, I am sure you will get actual problem
SVK N replied to Web Star on 14-Feb-12 04:06 AM
with the original query the variable value chnages
Web Star replied to SVK N on 14-Feb-12 04:19 AM
the value change of variable didn't depends upon the query it variable change property than the query should return correct value I checked your original quey and which i have posted both giving same result as my side only you need to check other code on your page no any issue with that query.