473,322 Members | 1,501 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,322 software developers and data experts.

UNION, LIMIT and ORDER BY

I've trying to write a query that seems like it should be simple but
for some reason my attempts are not working. This is really a general
SQL quesion and doesn't pertain to MySQL but I couldn't find a generic
database discussion group except on on advancement and theory and this
is really a basic query construction question.

Just say I have a table with three columns, name, date, score and
these represent the test scores for various people. Each person can
take the test as many times as they like but I only want to get back
the last result.

My first thought didn't seem right but I figured I'd try it anyway:
SELECT name, date, score FROM testresults
GROUP BY name
ORDER BY date DESC
And yes this is wrong and does not return anything useful since the
score returned seems to be based on where the grouping started in
someway.

My second thought was that the list of people is small and known so I
was going to just hardcode their names into the query:
SELECT name, date, score FROM testresults WHERE name = 'bob' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = 'mary' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = 'jim' ORDER BY
date DESC LIMIT 1
UNION ALL
This is syntactically incorrect.

Can anyone help me with this query?

May 25 '07 #1
11 9656
On May 25, 3:23 pm, Israel <israeldip...@hotmail.comwrote:
I've trying to write a query that seems like it should be simple but
for some reason my attempts are not working. This is really a general
SQL quesion and doesn't pertain to MySQL but I couldn't find a generic
database discussion group except on on advancement and theory and this
is really a basic query construction question.

Just say I have a table with three columns, name, date, score and
these represent the test scores for various people. Each person can
take the test as many times as they like but I only want to get back
the last result.

My first thought didn't seem right but I figured I'd try it anyway:
SELECT name, date, score FROM testresults
GROUP BY name
ORDER BY date DESC
And yes this is wrong and does not return anything useful since the
score returned seems to be based on where the grouping started in
someway.

My second thought was that the list of people is small and known so I
was going to just hardcode their names into the query:
SELECT name, date, score FROM testresults WHERE name = 'bob' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = 'mary' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = 'jim' ORDER BY
date DESC LIMIT 1
UNION ALL
This is syntactically incorrect.

Can anyone help me with this query?
SELECT t1.* FROM tests t1
LEFT JOIN tests t2 ON t1.name = t2.name
AND t1.date t2.date
WHERE t2.date IS NULL;

May 25 '07 #2
Israel <is**********@hotmail.comwrote in news:1180102988.273271.53270
@p77g2000hsh.googlegroups.com:
I've trying to write a query that seems like it should be simple but
for some reason my attempts are not working. This is really a general
SQL quesion and doesn't pertain to MySQL but I couldn't find a generic
database discussion group except on on advancement and theory and this
is really a basic query construction question.

Just say I have a table with three columns, name, date, score and
these represent the test scores for various people. Each person can
take the test as many times as they like but I only want to get back
the last result.

My first thought didn't seem right but I figured I'd try it anyway:
SELECT name, date, score FROM testresults
GROUP BY name
ORDER BY date DESC
And yes this is wrong and does not return anything useful since the
score returned seems to be based on where the grouping started in
someway.
Ideally, you would have two tables here: one with people names (auto-
increment user id, user name) and one with test results (auto-increment
tableID, ID referencing the person, testdate, testresult). This would
make this query, and others you are likely to create afterwards,
incredibly easier (though this is untested):

SELECT t.date,t.score,u.name
FROM testresults t
JOIN users u ON t.userID=u.userID
GROUP BY t.userID
ORDER BY t.date DESC

The key to realizing there is a better way to design your database is
any replication of data: ie in your table, the name "Bob" is entered as
many times as he took the test. What if he called you up and said "Id
prefer my name be stored as Robert"? You'd have to go trawling through
this table, and any others, changing data when there is really no need
to do so - changing a single entry in the "users" table would do it.

If you don't want to change your table design, I would stick with your
query (minus the GROUP BY) and use a programming language (ie: PHP) to
display the results correctly.
May 28 '07 #3
On May 28, 1:35 pm, Good Man <h...@letsgo.comwrote:
The key to realizing there is a better way to design your database is
any replication of data: ie in your table, the name "Bob" is entered as
many times as he took the test. What if he called you up and said "Id
prefer my name be stored as Robert"? You'd have to go trawling through
this table, and any others, changing data when there is really no need
to do so - changing a single entry in the "users" table would do it.
My explanation of the tables was modified for brevity. There's
actually two tables (linked by FK) and the data is actually parameter
data for the state of some hardware components on a machine that get
logged at a regular interval but I wanted to create a query that would
just show me the latest for all of the components at arbitrary times.
This query would only list a handful of parameter types but there's on
the order of 100 parameter types all together and the parameter log
table keeps all of the history for post-analysis and can have on the
order of 100k-200k records so joining a table back onto itself is not
really option considering how slow it is.
Unless I'm missing something about the performance of joining in this
way I'll probably have to resort to multiple independent queries -
hence my original thought of using UNION ALL which I couldn't seem to
get the syntax correct for.
I was trying to avoid having to write any code because we already have
plans in the works for making a front end application that will allow
users to extract all sorts of data so I don't want to waste time
making a collection of hodge podge little apps or php code that
duplicates this work. Until we get the final application completed I
wanted to do everything via queries otherwise it will be like pulling
teeth to get users to stop using all of the "temporary" applications
but in the interim people can still get the data they need.
May 30 '07 #4
On May 28, 1:35 pm, Good Man <h...@letsgo.comwrote:
SELECT t.date,t.score,u.name
FROM testresults t
JOIN users u ON t.userID=u.userID
GROUP BY t.userID
ORDER BY t.date DESC
With the ORDER BY after the GROUP BY I just end up with the final
groups ordered by the date from some (seemingly random) row from each
group. What I need is for the first row picked for the group to be
the latest entry for that group but you can't switch the ORDER BY and
GROUP BY clauses.

May 30 '07 #5
On May 30, 3:35 pm, Israel <israeldip...@hotmail.comwrote:
On May 28, 1:35 pm, Good Man <h...@letsgo.comwrote:
SELECT t.date,t.score,u.name
FROM testresults t
JOIN users u ON t.userID=u.userID
GROUP BY t.userID
ORDER BY t.date DESC

With the ORDER BY after the GROUP BY I just end up with the final
groups ordered by the date from some (seemingly random) row from each
group. What I need is for the first row picked for the group to be
the latest entry for that group but you can't switch the ORDER BY and
GROUP BY clauses.
I can't see what's wrong with the query I posted previously - but if
you provide us with an example dataset and the result you'd expect to
obtain from that, perhaps we can help. Ideally include the CREATE and
INSERT statements too.

May 30 '07 #6
On May 30, 12:18 pm, strawberry <zac.ca...@gmail.comwrote:
I can't see what's wrong with the query I posted previously - but if
you provide us with an example dataset and the result you'd expect to
obtain from that, perhaps we can help. Ideally include the CREATE and
INSERT statements too.
The issue I have is that it performs a join onto itself and with
100k-200k records it is way to slow.

May 30 '07 #7
On May 30, 8:22 pm, Israel <israeldip...@hotmail.comwrote:
On May 30, 12:18 pm, strawberry <zac.ca...@gmail.comwrote:
I can't see what's wrong with the query I posted previously - but if
you provide us with an example dataset and the result you'd expect to
obtain from that, perhaps we can help. Ideally include the CREATE and
INSERT statements too.

The issue I have is that it performs a join onto itself and with
100k-200k records it is way to slow.
Too slow? With appropriate indexes I'd imagine that it would take
about 5 seconds. Is that too slow? Is 'GROUP BY' quicker!?!.

May 31 '07 #8
On May 31, 5:32 am, strawberry <zac.ca...@gmail.comwrote:
Too slow? With appropriate indexes I'd imagine that it would take
about 5 seconds. Is that too slow? Is 'GROUP BY' quicker!?!.
Ok, so I must be missing something then. This is my sql to create the
tables:
CREATE TABLE `parameter` (
`Id` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(45) NOT NULL default '',
PRIMARY KEY (`Id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `parameterlog` (
`ParameterId` int(10) unsigned NOT NULL default '0',
`SampleTimestamp` double NOT NULL default '0',
`Data` varchar(50) NOT NULL default '',
KEY `FK_parameterdata_Parameter` (`ParameterId`),
CONSTRAINT `FK_parameterdata_Parameter` FOREIGN KEY (`ParameterId`)
REFERENCES `parameter` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The FK_parameterdata_Parameter creates a BTREE index on the
ParameterId field so performing a join with that field with should as
quick as possible - right?

I ran the following query:
SELECT p1.* FROM `parameterlog` p1
LEFT JOIN `parameterlog` p2 ON p1.ParameterId = p2.ParameterId
AND p1.`Sampletimestamp` < p2.`Sampletimestamp`
WHERE p2.`Sampletimestamp` IS NULL;

On a smaller database with about 56,000 rows in this table it took 141
seconds and returned 73 rows. I didn't have the patience to wait for
the query for the larger databases but I can only assume that it has
an exponential growth.

I ran the following query (which doesn't produce the correct results):
SELECT * FROM parameterlog
GROUP BY ParameterId
ORDER BY SampleTimestamp
With the same record set of 56,000 rows this query executed in 0.21
seconds.

I'm using a 1.86GHz Pentium laptop with 1GB RAM and I'm running MySQL
server 4.1.

May 31 '07 #9
On May 31, 2:25 pm, Israel <israeldip...@hotmail.comwrote:
On May 31, 5:32 am, strawberry <zac.ca...@gmail.comwrote:
Too slow? With appropriate indexes I'd imagine that it would take
about 5 seconds. Is that too slow? Is 'GROUP BY' quicker!?!.

Ok, so I must be missing something then. This is my sql to create the
tables:
CREATE TABLE `parameter` (
`Id` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(45) NOT NULL default '',
PRIMARY KEY (`Id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `parameterlog` (
`ParameterId` int(10) unsigned NOT NULL default '0',
`SampleTimestamp` double NOT NULL default '0',
`Data` varchar(50) NOT NULL default '',
KEY `FK_parameterdata_Parameter` (`ParameterId`),
CONSTRAINT `FK_parameterdata_Parameter` FOREIGN KEY (`ParameterId`)
REFERENCES `parameter` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The FK_parameterdata_Parameter creates a BTREE index on the
ParameterId field so performing a join with that field with should as
quick as possible - right?

I ran the following query:
SELECT p1.* FROM `parameterlog` p1
LEFT JOIN `parameterlog` p2 ON p1.ParameterId = p2.ParameterId
AND p1.`Sampletimestamp` < p2.`Sampletimestamp`
WHERE p2.`Sampletimestamp` IS NULL;

On a smaller database with about 56,000 rows in this table it took 141
seconds and returned 73 rows. I didn't have the patience to wait for
the query for the larger databases but I can only assume that it has
an exponential growth.

I ran the following query (which doesn't produce the correct results):
SELECT * FROM parameterlog
GROUP BY ParameterId
ORDER BY SampleTimestamp
With the same record set of 56,000 rows this query executed in 0.21
seconds.

I'm using a 1.86GHz Pentium laptop with 1GB RAM and I'm running MySQL
server 4.1.
I'm not really familiar with this approach - but what happens if you
put an index on sampletimestamp?

May 31 '07 #10
On May 31, 11:04 am, strawberry <zac.ca...@gmail.comwrote:
I'm not really familiar with this approach - but what happens if you
put an index on sampletimestamp?
Interestingly I tried that a few times and averaged 147 seconds and
then after I removed it the time averaged 154 seconds. The load on my
system is roughly the same as it was the first time I ran the test so
I have no clue why it changed but running the query does peg out my
CPU at 100% for a while.
Without more exhaustive testing I can't determine whether or not
putting an index on sampletimestamp had any appreciable effect.
May 31 '07 #11
Israel <is**********@hotmail.comwrote in news:1180617935.216117.284910
@q75g2000hsh.googlegroups.com:

The FK_parameterdata_Parameter creates a BTREE index on the
ParameterId field so performing a join with that field with should as
quick as possible - right?
yeah, but according to your query, you're also performing a join on
'Sampletimestamp'... so those should be indexed as well
>
I ran the following query:
SELECT p1.* FROM `parameterlog` p1
LEFT JOIN `parameterlog` p2 ON p1.ParameterId = p2.ParameterId
AND p1.`Sampletimestamp` < p2.`Sampletimestamp`
WHERE p2.`Sampletimestamp` IS NULL;
SELECT * FROM parameterlog
GROUP BY ParameterId
ORDER BY SampleTimestamp

With the same record set of 56,000 rows this query executed in 0.21
seconds.
Of course it did. The query just says 'get everything' then group and order
by'. There's not much processing power/time in getting 'everything'.

Perhaps if you add an index on 'Sampletimestamp' things will happen much
quicker... have you looked at the EXPLAIN result of your query?
Jun 1 '07 #12

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

Similar topics

8
by: Wiebke Pätzold | last post by:
import sys Hi all, At the beginning there is a table (database) with different columns. So I create a search operator to look for regular expressions. First I start the search only in one...
10
by: KENNY L. CHEN | last post by:
Dear experts, I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO) and TEST1 (COL1,COL2,REC_NO). Both tables are unique-indexed on (COL1,COL2,REC_NO). I think the following...
0
by: Marek Lewczyk | last post by:
Hello, Currently I'm testing my app using MySQL 4.1.0 version, and I have a strange error during execution a union query. (SELECT IF(_DAT.pri <=> null, null, ROUND(_DAT.pri/1.22)) AS pri_net,...
0
by: unixman | last post by:
As usual, it is 2:00am, and I'm pulling my hair out, finally resorting to posting in the newsgroups for help. :) Simple problem, in theory. Given table "map": CREATE TABLE map ( entry_id...
0
by: Paradigm | last post by:
I am trying to make a union query to join 4 tables. I have reduced the tables to just 5 fields and made sure that the field types and names are the same. I keep getting an error message ODBC call...
1
by: Tom Schindl | last post by:
Hi, the following Statement worked on MySQL 4.0 but after upgrading to 4.1.12 on win32 the order is not working any more. Is this a known problem or is our SQL simply not useable on 4.1 or is...
4
by: Justin Koivisto | last post by:
OK, out of my element here once again... I want a query (or something I can use as a record source for a report) to do the following: * look in 2 tables (one field each) to find all entered...
0
by: Josué Maldonado | last post by:
Hello list, is there a way to write in plpgsql the query that returns a set of this: create view xxx as (select '3ARR' as level, dpe_stamp from detpa where dpe_productfk=1 order by 2 desc...
6
by: jmarcrum | last post by:
Hi! I have created a Union Query in ACCESS 2003, that combines 130 records from one query (Extra Foreman Radios) and 250 records from another query (Forman Main Radios). I have created a...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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.