CODEWITHSUNDEEP
sqlt-sql
Yuriy Shkraba
Yuriy Shkraba

Reputation: 402

How search first position on SQL?

I have problem, I can't search value position in my table. For example: I have table:

ProdID  InstID  APY
C3M2.5K 11920   0.3
C3M2.5K 11246   0.25
C3M2.5K 11626   0.25
C3M2.5K 12081   0.15
C3M2.5K 11224   0.05
C3M2.5K 11311   0.05
C3M2.5K 11460   0.05
C3M2.5K 11164   0.03
C3M2.5K 11394   0.01

Code:

select * from table
where APY = 0.05

I need write select which will be display:
Count(APY) - it's: 9
First position APY - it's: 5
Thanks for your help.

Upvotes: 0

Views: 42

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

You could use this query:

WITH CTE AS
(
   SELECT CountAPY = (SELECT COUNT(*) FROM dbo.TableName),
          Pos = ROW_NUMBER() OVER (ORDER BY ID),
          ProdID, InstID, APY
   FROM dbo.TableName
)
SELECT TOP 1 CountAPY, FirstPos = Pos
FROM CTE
WHERE APY = 0.05

Demo: http://sqlfiddle.com/#!6/a4f22/1/0

Upvotes: 3

Related Questions