CODEWITHSUNDEEP
sql-serverdatabase
njoromwando
njoromwando

Reputation: 111

Return second item from a subquery

i want to return the second item from this subquery:

set GrowerNumber =(select top 1 tea_no
  from ktda_file
  where ktda_file.fosa_acno=customer.fosa_acno)

Upvotes: 1

Views: 282

Answers (5)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this 

WITH CTE AS
( 
 SELECT top 2 tea_no  from ktda_file
 WHERE ktda_file.fosa_acno=customer.fosa_acno
 ORDER BY tea_no ASC
)     
SELECT TOP 1 tea_no  FROM CTE ORDER BY tea_no DESC

Upvotes: 1

Slava N.
Slava N.

Reputation: 596

GrowerNumber = (
  select top 1
    tea_no
  from
    (
      select top 2
        tea_no
      from
        ktda_file
      where ktda_file.fosa_acno=customer.fosa_acno
    ) as a
  order by
    tea_no desc
)

Upvotes: 1

cameront
cameront

Reputation: 710

Hi you need to do something like this (as Ivan said, you dont have an order by in your query, so what is the 2nd one?) Nevertheless assuming it is tea_no...

GrowerNumber = (Select tea_no  from (select row_number() over (order by tea_no) as row
    from ktda_file where ktda_file.fosa_acno=customer.fosa_acno) as orderedlist
    where row = 2)

Upvotes: 0

JassyJov
JassyJov

Reputation: 204

Maybe this will help you:

(select tea_no from (select row_number() over ( order by tea_no asc) as rowID,  tea_no from ktda_file where ktda_file.fosa_acno=customer.fosa_acno)x where rowID = 2)

Upvotes: 0

Devart
Devart

Reputation: 121912

SELECT *
FROM customer
OUTER APPLY (
    SELECT
        Item1 = MAX(CASE WHEN t.RowNum = 1 THEN t.tea_no END),
        Item2 = MAX(CASE WHEN t.RowNum = 2 THEN t.tea_no END)
    FROM (
        SELECT tea_no, RowNum = ROW_NUMBER() OVER (ORDER BY tea_no)
        FROM ktda_file
        WHERE ktda_file.fosa_acno = customer.fosa_acno
    ) t
    WHERE RowNum < 3
) t2

Upvotes: 0

Related Questions