Sql-server – Assign a unique count to each row in the resulting table of a select statement

sql server

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?

Best Answer

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

Related Solutions

Sql-server – How to assign different random values to each row in a SELECT statement

A subquery is evaluated once if possible. I can't recall what the "feature" is called (folding?) sorry.

The same applies to GETDATE and RAND functions. NEWID is evaluated row by row because it in intrinsically a random value and should never generate the same value twice.

The usual techniques are to use use NEWID as input to CHECKSUM or as a seed to RAND

For random values per row:

SELECT
   co1l, col2,
   ABS(CHECKSUM(NEWID())) AS Random1,
   RAND(CHECKSUM(NEWID())) AS Random2
FROM
   MyTable

If you want random order:

SELECT
   co1l, col2
FROM
   MyTable
ORDER BY
   NEWID()

If you want random order with a row order too. ActualOrder order here is preserved regardless of the order of the resultset

SELECT
   id, val,
   ROWNUMBER() OVER (ORDER BY id) AS id
FROM
   #t1
ORDER BY
   NEWID()

Edit:

In this case, we can state the requirement as:

return any random value from the set for each row in the set

the random value will be different from the actual value in any row

This is different to what I offered above which simply re-orders rows in various ways

So, I'd consider CROSS APPLY. The WHERE clause force row by row evaluation and avoids the "folding" issue and ensures that val and rnd are always different. CROSS APPLY can scale quite well too

SELECT
   id, val, R.rnd
FROM
   #t1 t1
   CROSS APPLY
   (SELECT TOP 1 val as rnd FROM #t1 t2 WHERE t1.val <> t2.val ORDER BY NEWID()) R
ORDER BY
   id

Sql-server – Interpreting SQL Server’s Showplan XML

First let me just say - fantastic looking site :)

This sort of thing is exactly what I wrote that XSLT for - I'm glad that others are finding it useful!

I have to confes its something that I wrote a while ago and then got kind of side-tracked, there are some improvements that I've been planning to make for a while now that I hope to get around to actually implementing soon!

Some links:

Let me know if you have any suggestions for improvements!

Best Answer

Related Solutions

Sql-server – How to assign different random values to each row in a SELECT statement

Sql-server – Interpreting SQL Server’s Showplan XML

Related Question