Ranking in SQL statements?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

REGEXP requires something to match against prior to the keyword appearing. In this case, there is no field or string referenced.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

Post by afbase »

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 ;
Somehow i missed my 'dividend' in my copy/paste of the sql
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

update

Post by afbase »

so i ran a " debbugging " sql statement to see if it works

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;
I won't give my entire table but just a handful of entries of the returned table from myPHPadmin:
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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'm not sure I follow what all that is supposed to be showing us.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

Post by afbase »

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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

This is still related to viewtopic.php?t=61291 ?
Why don't you calculate the "ranking points" in getdata_storemysql?
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

Post by afbase »

This is still related to viewtopic.php?t=61291 ?
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.

Its going to take awhile before I actually formulate a score or continue with this post for help.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

yay

Post by afbase »

Ok well I finally got the SQL statement to a point where it satisfies all the conditions mentioned in the first page.
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;
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
Post Reply