CODEWITHSUNDEEP
sqlsql-server
Artem
Artem

Reputation: 1050

Assign a unique count to each row in the resulting table of a select statement

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:

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?

Upvotes: 0

Views: 49

Answers (1)

Nick
Nick

Reputation: 7431

Use ROW_NUMBER() in your SELECT

SELECT ROW_NUMBER() OVER(ORDER BY AuthorID), Body, AuthorID FROM Article;

Row Number

Upvotes: 3

Related Questions