473,325 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Question about the LIMIT clause


I've run in to a problem with a query I'm trying to write. I have attached a
sample SQL script at the end of this post to show an overview of what I'm
working with.

I want to be able to use LIMIT to control how many rows from one table are
returned, independent of how many rows there are in a second table that is
joined to the first.

When I execute the following SQL script:

SELECT DISTINCT
d.datetimestamp,
e.eventid,
e.eventname,
e.eventdescription
FROM
dates d
LEFT JOIN
events e ON
e.dateid = d.dateid
LIMIT
0, 1

I get this:

+---------------+---------+-----------+----------------------------+
| datetimestamp | eventid | eventname | eventdescription |
+---------------+---------+-----------+----------------------------+
| 1088361927 | 1 | Event One | This is a test first event |
+---------------+---------+-----------+----------------------------+

when what I really want is:

+---------------+---------+--------------+-----------------------------+
| datetimestamp | eventid | eventname | eventdescription |
+---------------+---------+--------------+-----------------------------+
| 1088361927 | 1 | Event One | This is a test first event |
| 1088361927 | 2 | Second Event | This is a test second event |
| 1088361927 | 3 | Third Event | This is a test third event |
+---------------+---------+--------------+-----------------------------+

In other words I want to retrieve one row from the dates table and all
associated rows from the events table.

Is this possible to do, with or without using the LIMIT clause? I've read
about that CONCAT_GROUP function available in MySQL 4.1, which sounds like
it will do what I need to do, but the server I need to run this one has
other software on it that isn't compatible with 4.1.

One final point is that I'm working with more complex tables than this and
with large sets of data, on a server with a large amount of concurrent
users, so any solution would need to be efficient.

Thanks for any help you can give me,

Steve

---------------

CREATE TABLE `dates` (
`dateid` int(10) unsigned NOT NULL auto_increment,
`datetimestamp` int(10) unsigned NOT NULL default '0',
`datetitle` varchar(50) NOT NULL default '',
`datedescription` varchar(255) NOT NULL default '',
PRIMARY KEY (`dateid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

INSERT INTO `dates` VALUES (1, 1088361927, 'First Date', 'This is a test
first date');
INSERT INTO `dates` VALUES (2, 1088361948, 'Second Date', 'This is a test
second date');

CREATE TABLE `events` (
`eventid` int(10) unsigned NOT NULL auto_increment,
`dateid` int(10) unsigned NOT NULL default '0',
`eventname` varchar(50) NOT NULL default '',
`eventdescription` varchar(255) NOT NULL default '',
PRIMARY KEY (`eventid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

INSERT INTO `events` VALUES (1, 1, 'Event One', 'This is a test first
event');
INSERT INTO `events` VALUES (2, 1, 'Second Event', 'This is a test second
event');
INSERT INTO `events` VALUES (3, 1, 'Third Event', 'This is a test third
event');
INSERT INTO `events` VALUES (4, 2, 'Fourth Event', 'This is a test fourth
event');
INSERT INTO `events` VALUES (5, 2, 'Fifth Event', 'This is a test fifth
event');
Jul 20 '05 #1
1 3009
LIMIT works against the result set of the query, not any intermediate
result set or table.

If you could use MySQL 4.1, I would suggest:

SELECT DISTINCT d.datetimestamp, e.eventid, e.eventname,
e.eventdescription
FROM dates d LEFT JOIN events e ON e.dateid = d.dateid
WHERE d.dateid IN (SELECT d2.dateid FROM dates d2 LIMIT 1)

But this doesn't work in MySQL 4.0 or earlier; only MySQL 4.1 has
support for that type of subquery expression.

I think you're going to have to execute two SQL statements.

SELECT d2.dateid FROM dates d2 LIMIT 1

Fetch the result of the above query and use use it for the value of the
parameter in the following query:

SELECT DISTINCT d.datetimestamp, e.eventid, e.eventname,
e.eventdescription
FROM dates d LEFT JOIN events e ON e.dateid = d.dateid
WHERE d.dateid = ?

Sometimes a single SQL statement simply cannot do a given task. Instead
of fighting for many hours and days to figure out how you can do it in
one statement, just get the job done with two statements, and move on.

One more note: LIMIT shouldn't implicitly give you the row with the
lowest value, or the "first" row. The order of a result set is not
defined except by an ORDER BY clause. You might want to use this
instead as your step 1 query:

SELECT MIN(d2.dateid) FROM dates d2

Regards,
Bill K.
Jul 20 '05 #2

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

Similar topics

10
by: Randell D. | last post by:
Folks, I have a SELECT that returns with multiple records - It works when I have a LIMIT clause but when I try to include a GROUP BY clause, the select returns nothing (ie no records, no...
3
by: Jay K | last post by:
Hi, I have multiple queries like this: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 desc LIMIT 5 and
5
by: Jeremy | last post by:
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...
2
by: Mike | last post by:
New to PHP and MySQL. Using PHP5 and MySQL 4.1 Windows XP Pro IIS 5.1 I'm trying to page a recordset, and am using a LIMIT clause to fetch a defined range of records from my db. However, the...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
7
by: Scott Frankel | last post by:
Still too new to SQL to have run across this yet ... How does one return the latest row from a table, given multiple entries of varying data? i.e.: given a table that looks like this: color...
1
by: lawrence k | last post by:
Want to replace the limit clause in a query, but can't get it right. What's wrong with this: $pattern = "(.*)limit (.*)"; $replacement = '$1'; $replacement .= "LIMIT $limit"; $replacement .=...
1
by: henryrhenryr | last post by:
Hello! I'm having a bit of trouble with a query. I am trying to paginate a set of results using LIMIT. Each result has a bit of 'basic' data and potentially multiple 'additional' data. I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.