What query is needed to count the number of rows in the sample that satisfies the given condition?

Question

SQL

There is a table - the result of the selection, for example
ShopId ShopCode ShopSeq
13500 q109190 1
12679 q109190 1
217 q109190 2
For a record with a given ShopId, you need to count the number of rows in the table whose ShopSeq is less than the ShopSeq of a record with a given ShopId. For example, for an entry with ShopId = 217, the number 2 should be returned, for ShopId = 12679, the number 0 should be returned.
Here's what I managed to compose myself:

COUNT (CASE WHEN ShopSeq < someNumber then 1 end) as X

I just can’t figure out how to substitute ShopSeq from a line with a given ShopId instead of someNumber.

Answer 1

well, replace your someNumber with (Select ShopSeq from Table where ShopId='217')