0

Let's assume we have a simple database that has the following schema: Article(Id, Body, AuthorId) and Author(Id, Name). Suppose that we want to develop a page where each author will be able to view his own posts only. There is only one requirement:

  • When the author wants to see his posts, the SQL query should allow him to see a counter for each item, starting at 1.

Example Here's a simple set up: (1,"Hi",1), (2,"there",1), (3,"test",2), (4,"foo",1).

By executing some sort of SQL statement: `select count, body where authorid=1; we want to get: (1,"Hi",1), (2,"there",1), (3,"foo",1).

Question As you can see, the suggested statement wont work because count does not make sense yet. My question is, is there any way to assign a local count for each item to produce the result above?

1

You can use ROW_NUMBER, like this:

SELECT 
    ROW_NUMBER()OVER(ORDER BY ID ASC) AS [Count],
    body 
FROM @Article AS A
WHERE authorid=1


Count                body
1                    Hi
2                    there
3                    foo
1
  • And if you return more than one author, OVER (PARTITION BY AuthorID ORDER BY ID). Feb 13 '16 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.