I am relatively inexperienced with mySQL, and I am trying to learn how
to analyze some data with it. I am using the latest free version of
SQLYOG available.

I have a table with the following information on a monthly basis.

COMPANYID , DATE, MarektValue

I would like to select the largest 100 marketvalues on Decemeber of every year.
If I were to do this for one year, say 1960, it is easily written as: 

SELECT Date, CompanyID, marketvalue FROM Table1 Where (Month(date)=12
and Year(date)=1960) Order by Date, MarketValue Desc Limit 1,100

But I want to do this for a flexible range of years, say 1960-2003. It
seems like you should be able to do something with a LOOP and END LOOP
that would allow you to run the above select statement For Years =
1960 to
2003 or something to that effect, but I don't know where to begin.

---

Request for Question Clarification by andyt-ga on 02 Dec 2004 11:26 PST

jeremyd2-ga,

I'm posting this as a clarification because I'm not sure it's what you
wanted (ie: all the records in one query), but let me know if it
answers the question:

SELECT Date, CompanyID, marketvalue FROM Table1 Where (Month(date)=12
and Year(date)>1960 and Year(date) < 2003 ) Order by Date, MarketValue
Desc Limit 1,100

--andyt-ga

---

Clarification of Question by jeremyd2-ga on 02 Dec 2004 11:30 PST

Unfortunately that won't do it. The Limit at the end of the statement
only returns 100 results. I need it to return the largest 100 results
for each year.

---

Request for Question Clarification by hammer-ga on 09 Dec 2004 17:11 PST

Jeremyd2,

I don't have the right version of mySQL to test with (>=4.0.0), but
try using a UNION. Something like this:

(SELECT Date, CompanyID, marketvalue FROM Table1 Where (Month(date)=12
and Year(date)=1960) Order by MarketValue, Date Desc Limit 100) UNION
(SELECT Date, CompanyID, marketvalue FROM Table1 Where (Month(date)=12
and Year(date)=1961) Order by MarketValue, Date Desc Limit 100) UNION
(SELECT Date, CompanyID, marketvalue FROM Table1 Where (Month(date)=12
and Year(date)=1962) Order by MarketValue, Date Desc Limit 100);

MySQL Manual | 13.1.7.2 UNION Syntax
http://dev.mysql.com/doc/mysql/en/UNION.html

- Hammer

It should be the other way around in the 'Order By' statement:

SELECT Date, CompanyID, marketvalue FROM Table1 Where (Month(date)=12
and Year(date)>1960 and Year(date) < 2003 ) Order by MarketValue Desc,
Date Limit 1,100

See if that works.

Thanks for trying. 

That too, will only return 100 companies, just the 100 largest
companies from all those dates. To clarify,

I need 100 copanies FOR EACH DATE. IE, if we were looking in 1960 and
1961 there would be 200 companies, 100 largest from 1960 and 100
largest from 1961. If 1960,1961, 1962, there would be 300 companies
returned from the query.

Any more ideas? Thanks again for the effort.

to loop in SQL here is the most common way - a while loop
WHILE <Boolean expression> <code block>

Example:

DECLARE @i int
SET @i 1960
WHILE @i < 2003
BEGIN
    SET @i = @i +1
    /* Your code here */
END

Yes, I have tried running that while statement before. I get this
error message in SQLyog:

Error Code : 1064
You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DECLARE @i int
SET @i 1960
WHILE @i < 1965
BEGIN
    SET @i
(0 ms taken)

Can this be because of the version of mysql that my front end
application uses? I think I read that SQLyog supports mysql version
4.17. Is there another way besides the While statement to do this? Is
it better to get a different program to run mysql than SQLyog. I would
prefer to stick with SQLyog since i have been using that an I am
relatively comfortable with it.

Just a thought, but maybe you should use a join.
Have a table with all the dates that you need.
Then run the query using the dates table.
Do a seach on joins, that should give you the looping that you require.

Joins tutorial
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml