As SELECT in second Rank of Top

Navigation
0

I want to do this:

A thousand runners run a race, take the most clubs, the winner is the first place is Juan with 200000, second place Pedro with 50 and the third place Pedro with 2 sticks. I must select by name Pedro that only sees the second place of collecting sticks.

NO, I do not want this because Pedro can be anyone, it's a variable, the important thing is the sticks collected in BIGINT value. 

SELECT nombre FROM TABLE Where Nombre PEDRO

SELECT TOP 1 nombre FROM TABLE 
SELECT TOP 2 nombre FROM TABLE 
SELECT TOP 3 nombre FROM TABLE

I want the Excel to show me the second one that picked the most sticks, the third one, but only if I do Pedro, which is the second place, show me only Pedro.

    
asked by Juan Carlos Villamizar Alvarez 18.10.2018 в 00:01
source

3 answers

1

It's easier with real data and structure of real tables, but hey, assuming you have a table tablePositions with columns name and cantPalitos: 

 --1ro
 SELECT TOP 1 NOMBRE, cantPalitos FROM tablaPosiciones ORDER BY cantPalitos DESC

 --2do
 SELECT NOMBRE, cantPalitos FROM tablaPosiciones  WHERE cantPalitos = (
 SELECT TOP 2 MIN(cantPalitos) FROM tablaPosiciones ORDER BY cantPalitos DESC)

 --3ro
 SELECT NOMBRE, cantPalitos FROM tablaPosiciones  WHERE cantPalitos = (
 SELECT TOP 3 MIN(cantPalitos) FROM tablaPosiciones ORDER BY cantPalitos DESC)
    
answered by 19.10.2018 / 16:24
source
0

You can get it with a subquery and the function ROW_NUMBER in the following way 

select subconsulta.Nombre 
from (select ROW_NUMBER() over(order by NumeroPalos desc) id,Nombre
from TABLE) as subconsulta
Where id = 2

Assuming that the column "NumbersPalos" is the number of sticks taken, you order them in descending order and at the moment of calling the subquery you apply a where that selects in the order number 2 that will be the second one that takes more sticks

What the ROW_NUMBER function does is to enumerate the selected data in the order or partition that you indicate

    
answered by 18.10.2018 в 15:17
0

In this case I recommend you use an SP, it's easier but if you require a query use the following: 

select subconsulta.Nombre 
from (select ROW_NUMBER() over(order by NumeroPalos desc) id,Nombre
from [tabla1]) as subconsulta
Where id = 2
UNION
select subconsulta3.Nombre 
from (select ROW_NUMBER() over(order by NumeroPalos desc) id,Nombre,NumeroPalos
from [tabla1]) as subconsulta2 
INNER JOIN 
(select ROW_NUMBER() over(order BY NumeroPalos desc) id,Nombre,NumeroPalos
from [tabla1]) as subconsulta3 ON subconsulta3.NumeroPalos = subconsulta2.NumeroPalos 
and subconsulta2.id =2 AND subconsulta3.id =3
    
answered by 22.10.2018 в 06:44
When uploading to the server there is a query that does not work Error in MySQL query