1

Topic: Rating TOP2 on a condition

Hello, help, please with the decision:
There is one table, for example
Region the Person the Estimation
Moscow Ivanov 10
Moscow Petrov 5
Moscow Putenova 7
Moscow Peskovinsky 9
Perm Smirnov 5
Perm Petrov 10
Perm Petrov 15
Perm Smirnova 20
And .
It is necessary to deduce for each region on 2 persons with the least estimations, for example
Region the Person the Estimation
Moscow Petrov 5
Moscow Putenova 7
Perm Smirnov 5
Perm Petrov 10

2

Re: Rating TOP2 on a condition

, already the second person with the similar task.
Examinations shto-le?

3

Re: Rating TOP2 on a condition

WITH CTE AS (SELECT *, N=ROW_NUMBER () OVER (PARTITION BY Region ORDER BY the Estimation))
SELECT Region, the Person, the Estimation
FROM CTE
WHERE N <3
ORDER BY Region, the Person, the Estimation;

4

Re: Rating TOP2 on a condition

iap wrote:

WITH CTE AS (SELECT *, N=ROW_NUMBER () OVER (PARTITION BY Region ORDER BY the Estimation))
SELECT Region, the Person, the Estimation
FROM CTE
WHERE N <3
ORDER BY Region, the Person, the Estimation;

As usual, forgot FROM sad((

WITH CTE AS (SELECT *, N=ROW_NUMBER () OVER (PARTITION BY Region ORDER BY the Estimation) FROM the Table)
SELECT Region, the Person, the Estimation
FROM CTE
WHERE N <3
ORDER BY Region, the Person, the Estimation;

5

Re: Rating TOP2 on a condition

iap;
Thanks big, all turned out!
With coming new year smile

6

Re: Rating TOP2 on a condition

RC88;
Only the examiner can suspect something looking at the code smile

7

Re: Rating TOP2 on a condition

What CTE - only subqueries
select t. "Region", t. "Person", t. "Estimation"
from (select t. *, row_number () over (partition by t. "Region" order by t. "Estimation" asc) NN
from Tabel t with (nolock) / **/) t
where
t. NN in (1, 2)
order by 1, 2, 3

8

Re: Rating TOP2 on a condition

Jaffar;
About, and you  think that it not one and too?

9

Re: Rating TOP2 on a condition

TaPaK wrote:

Jaffar;
About, and you  think that it not one and too?

Strictly speaking, likely it not same.
Probably it is processed by different fragments of the code of the server.
After all syntax of the derivative table eliminates, for example, a recursion.

10

Re: Rating TOP2 on a condition

iap;
If to take specific these examples  and handling will be very similar, and is faster .  to take a recursion, there too anything special, except table spool with values of a recursion. So question of the organization of operators of a recursion here there is more.

11

Re: Rating TOP2 on a condition

TaPaK wrote:

iap;
If to take specific these examples  and handling will be very similar, and is faster .  to take a recursion, there too anything special, except table spool with values of a recursion. So question of the organization of operators of a recursion here there is more.

you saw the program code of the server?
In particular, concerning to CTE and the derivative table?
It is assured that is not present. We can guess only.

12

Re: Rating TOP2 on a condition

iap wrote:

it is passed...
You saw the program code of the server?
In particular, concerning to CTE and the derivative table?
It is assured that is not present. We can guess only.

Well the plan, for it is accessible to us only and we look smile

13

Re: Rating TOP2 on a condition

select top 2 with ties t.*
from Tabel t with (nolock)
order by row_number () over (partition by t. "Region" order by t. "Estimation" asc), 1, 2, 3 [/code]
?

14

Re: Rating TOP2 on a condition

[quote = __ Avenger __] select top 2 with ties t.*
[/code]

Your request will always produce two records. And at all on two records for each region.