Does this work with a SELECT-CASE Statement?

[joacker]

Hello,

today i have the following question:

I need a SELECT statement that can also return the value of row n+1, which means:

SELECT
CASE nr = 1 THEN nr
CASE nr = 5 THEN nr
ELSE nextRow.nr

I guess this is not makeable with a select statement but seems to be a common problem (IF x THEN take the value of the next row). Therefore is there a way to solve this problem with a self-join (isn't it slow then?) or something else?

Thx

 

[gmmastros]

No.

You'll need to use a self-join. Self-joins can be slow, but they don't need to be. It all depends on your situation.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

Select t.value,min(selfjoin.value)
from tablename t
inner join(
Select value
from tablename
)selfjoin
on selfjoin.value>t.value
group by t.value
order by t.value

 

[joacker]

Thank you guys.

So imo the question is: use self-join or prepare the talbe with cursor for the select queries to avoid self-joins.

 

[joacker]

I just had another idea:

SELECT
CASE
WHEN nr = 1 THEN nr
WHEN nr = 5 THEN nr
ELSE
(SELECT nr FROM table WHERE table.id = this.id + 1)
END

Would that be better than a self join?

 

[gmmastros]

What is your definition of better?

Seriously, whatever you do, it's most important that the data be correct. I mean, it doesn't matter how fast the code is if it's returning the wrong results. That being said, I suppose by 'better' you mean, fastest. Right?

The method you are describing here is called a correlated sub-query. In my experience, subqueries are slower than self joins. Why is this? Sub-queries are often calculated for each row in the out query. When you use a self join, all the data is joined first, then calculations are performed second.

I infer, from your previous posts, that you think self-joins should be avoided. In my own code, I have used self-joins on numerous occasions, and have never been disappointed by their performance. Of course, like any other joins you use, it's important (for performance) that you are joining in such a way that indexes can be used. It's also important that the join conditions are specified in such a way that each row is joined only once to each other. There are times when multiple rows are ok, but not in this situation.

From a performance perspective, cursors are almost never the correct way to write your code. They are almost always slower than set based methods.

Most of the time, a self join will perform best, followed by sub-queries, and cursors are the worst.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joacker]

Thx gmmastros. This was a very helpful answer