CODEWITHSUNDEEP
sqlsql-server-2008calculated-columnssubquery
wibarr
wibarr

Reputation: 276

Trouble with computed column in SQL

I have a table that I wish to select a subset of columns from but also add on the end a computed column based upon where you are located in a queue. There are the following fields (that are pertinent):

id: int, auto increment, primary key answertime: datetime, nullable

By default, when something is submitted to the queue, its answertime is NULL. So, I wish to select the ID of the thing in the queue as well as its rank in the queue (i.e. rank 1 is the next item that is unanswered, etc). Here's what I was thinking:

rank - id - COUNT(ids below my id where answertime is not null). However, I'm having an issue with the syntax of this query:

SELECT id AS outerid, COUNT(
    SELECT * FROM tablename WHERE id<outerid AND answertime IS NOT NULL
)
FROM tablename 
WHERE answertime IS NULL;

Now, obviously, this is wrong because I'm fairly confident you can't embed a select inside of an aggregate function, likewise flipping the SELECT and COUNT doesn't work as you can't embed a SELECT at that point in the code (it can only be used in a WHERE clause).

Is this even possible to do with just SQL or do I need to add some logic on the program end?

If it helps, I'm doing this on SQL Server 2008, although I doubt that would add any value.

Upvotes: 2

Views: 443

Answers (3)

Andriy M
Andriy M

Reputation: 77657

As suggested by @HLGEM, you could use ROW_NUMBER() to obtain your results. The method involves ranking the rows in tablename by id without partitioning and by id with partitioning by answertime. The difference between the rankings for every row where answertime is NULL would give you the same value as the one you are calculating using COUNT() in the subquery.

Here's an implementation of the method:

;
WITH ranked AS (
  SELECT
    *,
    Rnk     = ROW_NUMBER() OVER (                        ORDER BY id),
    PartRnk = ROW_NUMBER() OVER (PARTITION BY answertime ORDER BY id)
  FROM tablename
)
SELECT
  id,  /* AS outerid, if you like */
  Cnt = Rnk - PartRnk
FROM ranked
WHERE answertime IS NULL

Upvotes: 0

WoLfulus
WoLfulus

Reputation: 1977

SELECT id AS outerid,
(SELECT COUNT(*) FROM tablename WHERE id < outerid AND answertime IS NOT NULL) AS othercol
FROM tablename -- ?
WHERE answertime IS NULL;

also, where's the FROM statement?

Upvotes: 1

Yuck
Yuck

Reputation: 50825

You can do that, you just can't use SELECT * in an aggregate sub-query. Try this, which gets the COUNT value as a scalar result:

SELECT
   id AS outerid,
   (SELECT COUNT(Id) FROM tablename
    WHERE id<outie.id AND answertime IS NOT NULL)
FROM tablename outie
WHERE answertime IS NULL;

You may need to choose for yourself between using COUNT(*), COUNT(Id) or some other column depending on what you're really after.

Upvotes: 3

Related Questions