How to use SELECT inside COUNT in MYSQL

Question

I have this view:

myVIEW: (id,percent)

I want to make another view, which will be like this:

LASTVIEW: (lastID, lastPR, counter)

and in "counter", in every line I want to have how money id`s have a bigger percent than the percent of this line. so I tried:

CREATE VIEW LASTVIEW(lastID, lastPR, counter) AS
SELECT id AS lastID, percent AS lastPR
COUNT (SELECT id FROM myVIEW WHERE percent < lastPR) AS counter,
FROM myVIEW;

Answer 1

Your are almost there. Try this:

SELECT id AS lastID, percent AS lastPR, (SELECT Count(id) 
FROM myVIEW bigger 
WHERE bigger.percent > myv.percent) AS counter
FROM myVIEW myv