473,545 Members | 2,776 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question Regarding "Limit"

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!
Jul 20 '05 #1
5 2100
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
Jul 20 '05 #2
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!


Jul 20 '05 #3
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

Jul 20 '05 #4
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.
Jul 20 '05 #5
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.
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
3713
by: joe | last post by:
Can anyone help me out by either telling me how to get to the result i need or by pointing me to some documentation about the following question: A certain query to a database give me eg 100 results: $query = "select id, name, addr1, addr2, city from mytable where id=" ..$id; $connection = mysql_connect(etc etc) ....
2
5093
by: Lars | last post by:
I am trying to post a pretty long string (9300 bytes) in a hidden field and i get this very strange error message: Request object, ASP 0107 (0x800A01B8) The data being processed is over the allowed limit. What is going on here. Over what allowed limit? Has anyone seen this before?
0
2546
by: mrwoopey | last post by:
Hi, My OLAP data cube is giving me the following error when I am manipulating OLAP data cube views: "the data being processed is over the allowed limit" I know that this message is caused by the size limit of each form field that is retrieved in the Request object is 102,399 bytes. The error occurs when I exceed this limit.
0
1780
by: krystoffff | last post by:
Hi I would like to paginate the results of a query on several pages. So I use a query with a limit X offset Y to display X results on a page, ok. But for the first page, I need to run the same query with a count(*) to know how many pages I will get (number total of rows/ X). The problem is my query is very slow (maybe 5s) because there...
4
2277
by: bibsoconner | last post by:
Hi, I hope someone can please help me. I'm having a lot of trouble with schema files in .NET. I have produced a very simple example that uses "include" to include other schema files. It all works with SPY, but when I pick "Validate Schema" from the .NET 2003 menu, it fails with message: "Type XType is not declared." As I hinted at in...
10
2488
by: paulw | last post by:
Hi Please give problems that "HAS TO" to use recursion (recursive calls to itself.) Preferrably real world examples, not knights tour. I'm thinking about eliminating the use of stack... Thanks.
2
4806
by: TG | last post by:
I'm using the System.DirectoryServices.DirectorySearcher.Findall method to attempt to retrieve user id's from an Exchange LDAP server. I get the error message "The administrative limit for this request was exceeded". Can someone please tell me if you've seen this and what the cause is? Thanks
5
13365
by: Martin | last post by:
I'm trying to adapt a PHP script that was written to use MySQL, so that it will work with an MSAccess MDB file. An important part of the script makes use of the SQL "LIMIT" keyword available in MySQL. eg: "SELECT MyField FROM MyTable LIMIT 40,10" to select 10 records beginning at the 41st record. Can anyone tell me how I can achieve this...
25
20521
by: tekctrl | last post by:
Anyone: I have a simple MSAccess DB which was created from an old ASCII flatfile. It works fine except for something that just started happening. I'll enter info in a record, save the record, and try to move to another record and get an Access error "Record is too large". The record is only half filled, with many empty fields. If I...
0
7432
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7456
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7786
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6022
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5359
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5076
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3490
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3470
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1044
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.