Ranking in SQL statements?
Moderator: General Moderators
Code: Select all
SELECT stock_id AS rank FROM curldata WHERE dividend REGEXP '([[:digit:]].[[:digit:]]|.[[:digit:]])' AND 'annsales'>='100' GROUP BY 'stock_id' ORDER BY 'multiplier' ASC, 'multiplier'*'bookval' ASC, 'past_5_earnings' DESC, 'curass'/'long_term_debt' DESC ;update
so i ran a " debbugging " sql statement to see if it works
I won't give my entire table but just a handful of entries of the returned table from myPHPadmin:
Code: Select all
SELECT ticker, stock_id, rank, COUNT( stock_id ) AS rank
FROM curldata
WHERE 'annsales' >= '100'
AND dividend REGEXP '([[:digit:]].[[:digit:]]|.[[:digit:]])'
GROUP BY 'stock_id'
ORDER BY 'multiplier' ASC , 'multiplier' * 'bookval' ASC , 'past_5_earnings' DESC , 'curass' / 'long_term_debt' DESC
LIMIT 0 , 1000;ticker stock_id rank rank
BDF 1 0 1
FCT 2 0 1
FAM 3 0 1
FFA 4 0 1
AGE 10 0 1
Well my goal is to have this sql statement give a rank (i.e. 1st, 2nd, 3rd, etc) to the rank field. my SQL statement doesn't seem to be doing that but just returning 1's and I was kinda of hoping that a table of returned values would give a hint to of what was occurring in my DB-table and hopefully give an idea to forum readers of how to help me correct this.
If the table doesn't seem all that clear ignore it
If the table doesn't seem all that clear ignore it
This is still related to viewtopic.php?t=61291 ?
Why don't you calculate the "ranking points" in getdata_storemysql?
Why don't you calculate the "ranking points" in getdata_storemysql?
Yes it is apart of that whole thing. I decided not to create a score for stocks because I'm trying to create a portfolio model as close as i can that is written in "The Intelligent Investor" (4th ed.) by Ben Graham. He never created a score for stocks so I figured it would be best to not do so myself. However he does have many financial arguments to sort stocks by so i figured I would be able to rank stocks by all the arguments.This is still related to viewtopic.php?t=61291 ?
Why don't you calculate the "ranking points" in getdata_storemysql?
Its going to take awhile before I actually formulate a score or continue with this post for help.
yay
Ok well I finally got the SQL statement to a point where it satisfies all the conditions mentioned in the first page.
Exhibit A
I chose to order it by lowest multiplier (p/e ratio)simply because thats an simple valuation of a stock.
THE MILLION DOLLAR QUESTION: how can I update the rank column using this sql statement?
p.s. don't ask for a million dollars
Exhibit A
Code: Select all
SELECT * , COUNT( stock_id ) AS rank
FROM curldata
WHERE annsales >= '100'
AND dividend >0
AND multiplier <=17
AND multiplier >0
AND bookval >0
AND multiplier * bookval <= 22.5
AND past_5_earnings >= 16.5
AND curass / long_term_debt >=2
GROUP BY 'stock_id'
ORDER BY 'multiplier' DESC;THE MILLION DOLLAR QUESTION: how can I update the rank column using this sql statement?
p.s. don't ask for a million dollars