Get SQL Data last to first

Anoop Bansal

Please look at the attached image

I am having 1000s of rows in purchase table, I want the fetch 10 rows each in desending order (date), like bottom to first, as that we use in pagination.

Suppose I have 30 rows then the output will be

30-20

20-10

10-1

how can I achieve this?

if I use

SELECT TOP 10 * FROM purchase ORDER BY date DESC

then I am getting 30-20 rows then the next row will be fetch 20-10 and then 10-1 (until it reaches the top row)

Please help

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah9y ago
Accepted answer
using OFFSET and FETCH you can do.
Declare @Page int = 1
Declare @PageSize int = 10

SELECT
    *
FROM
    [Purchase]
ORDER BY
    PurchaseDate DESC

    OFFSET ((@Page-1) * @PageSize) ROWS
    FETCH NEXT @PageSize ROWS ONLY;
try above code after changing value of @Page, you should get desired result
Anoop Bansal9y ago
Sir, please help me on this below link
http://www.c-sharpcorner.com/Forums/get-data-from-last-to-first-with-join-and-distinct-rows

I Got stuck when using this.
Bryian Tan9y ago
@Anoop Bansal

Check this out: https://thesqldude.com/2012/04/07/how-to-prevent-users-from-accessing-sql-server-from-any-application-or-any-login-expect-your-main-application-its-login/
Nilesh Shah9y ago
@Anoop,

I'm not sure if there is any way in SQL Server express edition that database can be restricted to be used from a single application only

Any one who has user name and password can access it

I think you can host on Azure VM or SQL Azure then you can have this level of security

I also advice you to post a new question with this specific requirement, may be someone else can answer
Anoop Bansal9y ago
@Nilesh
lets make it simple, I created an application and used SQL Server and I delivered it to you (as you are a developer (as an example)), as you also uses SSMS and I don't want you to view/access my database, then what precaution should be taken in this case.

The database can ONLY be accessible with my application.
Nilesh Shah9y ago
You wrote:
Is their no way to protect the database, so that it can open by userid and password as we we used to connect in SSMS (Sql Server Authentication), doesn't it will help to protect. the db ?

My answer:
Sorry, I think I got your question wrong. I thought you want to protect data, but you want is a user name and password for db access.

check this link on how to set it up.
http://support.webecs.com/kb/a374/how-do-i-configure-sql-server-express-to-enable-mixed-mode-authentication.aspx
Anoop Bansal9y ago
Thanks Nilesh
Client donot have any server to host there db, they are using on a single PC,

As I donot want to encrypt the database because there are plenty of columns and this will slightly decrease the performance (as I already on the web).

Is their no way to protect the database, so that it can open by userid and password as we we used to connect in SSMS (Sql Server Authentication), doesn't it will help to protect. the db ?
Nilesh Shah9y ago
You wrote:
Actually, it was a freelancing project and on the client machine, they also use SQL Server Management Studio (in this case, my database is not secure)

My answer:
If it is a commercial use, then it is not adviced at all to use SQL Server Express edition. It is not meant for such use. You should have a commercial licensed version software only.

If client machine has SSMS already, then there must be somewhere SQL Server too. Why can't you use it to host your db? then you can have proper authentication and access levels specified.

Still you are worried about someone accessing and looking at your data and it is so much confidential, then I advice you to use encryption in SQL server, but it will be little bit slow in terms of access
Anoop Bansal9y ago
@Nilesh
As I am prety new to SQL Server and don't know about protecting the database as much.

I read some article on web but unable to achieve so far, please guide me to secure the database, so that it can ONLY access with that userid and password as such it can be passed in the connection string.

Actually, it was a freelancing project and on the client machine, they also use SQL Server Management Studio (in this case, my database is not secure)

Do suggest something.

Thanks
Anoop
Nilesh Shah9y ago
You wrote:

As I am working with Sql Server with my application and deployed SQL Server Express on the client machine with Integrated Security=true (without userid and password) as a connection string.

As it is without user id and password, anyone can login into the system by installing Management Studio and access my database. In this case, how can I secure my database from being viewed / access by anonymous user.

My answer:

If a user has such a great access that he/she can install SQM Server Management Studio (which requires admin access), then you cannot protect it because he/she can take the bakcup and restore on some other pc and still can access data.

It is like protecting a bank robary from a security guard of bank!
Anoop Bansal9y ago
Thanks @Nilesh !! thats exactly I want to achieve.

I have one more question to ask, it would be greatful if you can assist something.
As I am working with Sql Server with my application and deployed SQL Server Express on the client machine with Integrated Security=true (without userid and password) as a connection string.

As it is without user id and password, anyone can login into the system by installing Management Studio and access my database. In this case, how can I secure my database from being viewed / access by anonymous user.

Please suggest.
Bryian Tan9y ago
You can do something like this, 1. Order by date DESC , 2. Select by Row number
SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY [Date] DESC) AS RowNum, *
          FROM      order
        ) AS RowConstrainedResult
WHERE   RowNum >= 1 --11 --21
    AND RowNum < 10 --20 --30
ORDER BY RowNum