Question Regarding "Limit"

[Jeremy]

I am relatively inexperienced with SQL, and I am trying to learn how
to analyze some data with it.

I have a table with the following information.

COMPANY ID , DATE, MarektValue

I would like to select the largest 100 marketvalues for each date in
my table. How can I do this efficiently in MySql.

In Microsoft Access , I can run something like this, but it is
extremely slow:

SELECT Company ID, Date, MarketValue
FROM Table1 RIGHT JOIN Dates ON Table1.Date = Dates.Date
WHERE (((Marketvalue) In (Select Top 100 [marketvalue] from Table1
where [Date] = [dates].[Date] Order by date, MarketValue Desc)));

I know that the LIMIT function in mySql will get 100 rows if i am
looking at one specific year. but I cant seem to get it to take the
100 largest marketvalues every year.

Thanks in advance for any assistance!

[The Collective]

Jeremy wrote:

I am relatively inexperienced with SQL, and I am trying to learn how
to analyze some data with it.

I have a table with the following information.

COMPANY ID , DATE, MarektValue

I would like to select the largest 100 marketvalues for each date in
my table. How can I do this efficiently in MySql.

In Microsoft Access , I can run something like this, but it is
extremely slow:

SELECT Company ID, Date, MarketValue
FROM Table1 RIGHT JOIN Dates ON Table1.Date = Dates.Date
WHERE (((Marketvalue) In (Select Top 100 [marketvalue] from Table1
where [Date] = [dates].[Date] Order by date, MarketValue Desc)));

I know that the LIMIT function in mySql will get 100 rows if i am
looking at one specific year. but I cant seem to get it to take the
100 largest marketvalues every year.

Thanks in advance for any assistance!

Hey Jeremy,

Not to get to in depth with your query have you tried using the LIMIT CLAUSE along with the ORDER BY clause.
I would just guess off the top of my head (Sorry leaving work right now...Can't wait to get home!!!)

SELECT Company ID, Date, MarketValue
FROM Table1 RIGHT JOIN Dates ON Table1.Date = Dates.Date
WHERE [Date] = [dates].[Date]
ORDER BY MarketValue Desc (or however you feel like ordering by)
LIMIT 0, 100
.... Like I said I am on my way out, but I will revisit this as soon as I get in. Let me know if this query did it for you

[jeremyd2]

I think my problem is that I am using SQLyog, which supports MySQl
version 4.17. I tried running the following without success:
DECLARE @i int
SET @i 1960
WHILE @i < 1965
BEGIN
SET @i = @i +1
SELECT Date, CompanyID, marketvalue FROM Table1 Where
(Month(date)=12 and Year(date)=@i) Order by Date, marketvalue Desc
Limit 1,100

END
Any ideas?

Jeremy wrote:

I am relatively inexperienced with SQL, and I am trying to learn how
to analyze some data with it.

I have a table with the following information.

COMPANY ID , DATE, MarektValue

I would like to select the largest 100 marketvalues for each date in
my table. How can I do this efficiently in MySql.

In Microsoft Access , I can run something like this, but it is
extremely slow:

SELECT Company ID, Date, MarketValue
FROM Table1 RIGHT JOIN Dates ON Table1.Date = Dates.Date
WHERE (((Marketvalue) In (Select Top 100 [marketvalue] from Table1
where [Date] = [dates].[Date] Order by date, MarketValue Desc)));

I know that the LIMIT function in mySql will get 100 rows if i am
looking at one specific year. but I cant seem to get it to take the
100 largest marketvalues every year.

Thanks in advance for any assistance!

[jeremyd2]

Collective,

Thanks for trying. I have tried the limit clause as per your
suggestion. So far, I have only been able to return the 100 rows for
whatever dates are specified. what i need the query to do is return the
top 100 rows for every year specified. IE, if Dates were December 1960
and December 1961, I would get 100 results from 1960 and 1961. I
imagine it needs some type of loop function. I am using SQLyog as my
front-end application, which i think supports SQL 4.17. Someone suggest
a WHILE statement, but that didnt work either. Any additional
suggestions are much appreciated.


The Collective wrote:

Jeremy wrote:

I am relatively inexperienced with SQL, and I am trying to learn how to analyze some data with it.

I have a table with the following information.

COMPANY ID , DATE, MarektValue

I would like to select the largest 100 marketvalues for each date in my table. How can I do this efficiently in MySql.

In Microsoft Access , I can run something like this, but it is
extremely slow:

SELECT Company ID, Date, MarketValue
FROM Table1 RIGHT JOIN Dates ON Table1.Date = Dates.Date
WHERE (((Marketvalue) In (Select Top 100 [marketvalue] from Table1
where [Date] = [dates].[Date] Order by date, MarketValue Desc)));

I know that the LIMIT function in mySql will get 100 rows if i am
looking at one specific year. but I cant seem to get it to take the
100 largest marketvalues every year.

Thanks in advance for any assistance!

Hey Jeremy,

Not to get to in depth with your query have you tried using the LIMIT

CLAUSE along with the ORDER BY clause.

I would just guess off the top of my head (Sorry leaving work right now...Can't wait to get home!!!)
SELECT Company ID, Date, MarketValue
FROM Table1 RIGHT JOIN Dates ON Table1.Date = Dates.Date
WHERE [Date] = [dates].[Date]
ORDER BY MarketValue Desc (or however you feel like ordering by)
LIMIT 0, 100
... Like I said I am on my way out, but I will revisit this as soon

as I get in. Let me know if this query did it for you

[Bill Karwin]

je******@gmail.com wrote:

I think my problem is that I am using SQLyog, which supports MySQl
version 4.17. I tried running the following without success:
DECLARE @i int
SET @i 1960
WHILE @i < 1965
BEGIN
SET @i = @i +1
SELECT Date, CompanyID, marketvalue FROM Table1 Where
(Month(date)=12 and Year(date)=@i) Order by Date, marketvalue Desc
Limit 1,100

END

Two problems with that:
1. It looks like the stored procedure language. You can use this only
when defining a stored procedure. This works only in MySQL 5.x.
2. Even so, it's incorrect syntax for MySQL's stored procedure language.
MySQL doesn't use the @ symbols, as Microsoft SQL Server does.

Bill K.

[Bill Karwin]

Jeremy wrote:

SELECT Company ID, Date, MarketValue
FROM Table1 RIGHT JOIN Dates ON Table1.Date = Dates.Date
WHERE (((Marketvalue) In (Select Top 100 [marketvalue] from Table1
where [Date] = [dates].[Date] Order by date, MarketValue Desc))); what i need the query to do is return the top 100 rows for every year specified

If your SQLyog front-end supports MySQL 4.1, and you are in fact running
MySQL 4.1 (required for subqueries), I think this might do what you want:

SELECT T1.`COMPANY ID`, T1.`Date`, T1.MarketValue
FROM Table1 AS T1 INNER JOIN
(SELECT DISTINCT YEAR(`Date`) AS YEAR FROM Dates) AS D
ON YEAR(T1.`Date`) = D.YEAR
WHERE T1.MarketValue IN
(SELECT DISTINCT T2.MarketValue FROM Table1 AS T2
WHERE T2.`COMPANY ID` = T1.`COMPANY ID`
AND YEAR(T2.`Date`) = YEAR(T1.`Date1)
ORDER BY T2.MarketValue DESC
LIMIT 100)

NB: this is not tested.

Further reading:
http://dev.mysql.com/doc/mysql/en/AN...ubqueries.html
http://dev.mysql.com/doc/mysql/en/Unnamed_views.html (subqueries in the
FROM clause)
http://dev.mysql.com/doc/mysql/en/SELECT.html (read about LIMIT and
DISTINCT)
http://dev.mysql.com/doc/mysql/en/Da...functions.html (the YEAR
function)

Regards,
Bill K.