UNION, LIMIT and ORDER BY

12 Message(s) by 3 Author(s) originally posted in mysql database

From: Israel

Date: Friday, May 25, 2007

I have trying to write

1. <chat> {Unix}'s simple {talk} command and {protocol}.
	write has been largely superseded by {talk} and then {irc}.

	An enhancement, {RWP}, has been proposed.

	2. <tool> A simple {text editor} for {Windows}.

	(1998-04-28)

a query

1. <database, information science> A user's (or {agent}'s)
	request for information, generally as a formal request to a
	{database} or {search engine}.

	{SQL} is the most common {database query language}.

	2. <character> {question mark}.

	(1997-04-09)

that seems like it should be simple but
for some reason my attempts aren't working. This is really a general
SQL

<language, database, standard> /S Q L/ An industry-standard
	language for creating, updating and, querying {relational
	database management systems}.

	SQL was developed by {IBM} in the 1970s for use in {System R}.
	It is the {de facto standard} as well as being an {ISO} and
	{ANSI} {standard}.  It is often embedded in general purpose
	programming languages.

	The first SQL standard, in 1986, provided basic language
	constructs for defining and manipulating {tables} of data; a
	revision in 1989 added language extensions for {referential
	integrity} and generalised {integrity} {constraints}.  Another
	revision in 1992 provided facilities for {schema} manipulation
	and {data administration}, as well as substantial enhancements
	for data definition and data manipulation.

	Development is currently underway to enhance SQL into a
	computationally complete language for the definition and
	management of {persistent}, complex objects.  This includes:
	generalisation and specialisation hierarchies, {multiple
	inheritance}, user defined {data types}, {triggers} and
	{assertions}, support for {knowledge based systems},
	{recursive query expressions}, and additional data
	administration tools.  It also includes the specification of
	{abstract data types} (ADTs), object identifiers, {methods},
	{inheritance}, {polymorphism}, {encapsulation}, and all of the
	other facilities normally associated with object data
	management.

	The emerging {SQL3} standard is expected to be complete in
	1998.

	According to Allen G. Taylor, SQL does _not_ stand for
	"Structured Query Language".  That, like "SEQUEL" (and its
	pronunciation /see'kw*l/), was just another unofficial name
	for a precursor of SQL.  However, the IBM SQL Reference manual
	for DB2 and Craig Mullins's "DB2 Developer's Guide" say SQL
	_does_ stand for "Structured Query Language".

	{SQL Standards (http://www.jcc.com/sql_stnd.html)}.

	{An SQL parser
	(ftp://ftp.ora.com/published/oreilly/nutshell/lexyacc/)} is
	described in "Lex & Yacc", by Levine, Mason & Brown published
	by O'Reilly.

	{The 1995 SQL Reunion: People, Projects, and Politics
	(http://www.mcjones.org/System_R/SQL_Reunion_95/)}.

	["A Guide to the SQL Standard", C.J. Date, A-W 1987].

	["SQL for Dummies", Allen G. Taylor, IDG Books Worldwide].

	(2005-11-17)

quesion and does not pertain to MySQL but I could not find a generic
data

<data, data processing, jargon> /day't*/ (Or "raw data")
	Numbers, {characters}, {images}, or other method of recording,
	in a form which can be assessed by a human or (especially)
	input into a {computer}, stored and {processed} there, or
	transmitted on some {digital channel}.  Computers nearly
	always represent data in {binary}.

	Data on its own has no meaning, only when interpreted by some
	kind of {data processing system} does it take on meaning and
	become {information}.

	People or computers can find patterns in data to perceive
	information, and information can be used to enhance
	{knowledge}.  Since knowledge is prerequisite to wisdom, we
	always want more data and information.  But, as modern
	societies verge on {information overload}, we especially need
	better ways to find patterns.

	1234567.89 is data.

	"Your bank balance has jumped 8087% to $1234567.89" is
	information.

	"Nobody owes me that much money" is knowledge.

	"I'd better talk to the bank before I spend it, because of
	what has happened to other people" is wisdom.

	(1999-04-30)

base discussion group

A group G is a non-empty {set} upon which a {binary} operator
	* is defined with the following properties for all a,b,c in G:

	  Closure:     G is closed under *,  a*b in G
	  Associative: * is associative on G, (a*b)*c = a*(b*c)
	  Identity:    There is an identity element  e  such that
		       a*e = e*a = a.
	  Inverse:     Every element has a unique inverse a' such that
		       a * a' = a' * a = e.  The inverse is usually
		       written with a superscript -1.

	(1998-10-03)

except on on advancement and theory

The consensus, idea, plan, story, or set of rules that is
	currently being used to inform a behaviour.  This usage is a
	generalisation and (deliberate) abuse of the technical
	meaning.  "What's the theory on fixing this TECO loss?"
	"What's the theory on dinner tonight?"  ("Chinatown, I
	guess.")  "What's the current theory on letting lusers on
	during the day?"  "The theory behind this change is to fix the
	following well-known screw...."

	(1994-12-14)

and this
is really a basic query construction question.

Just say I have a table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

with three columns, name, date

<convention, data> A string unique to a time duration of 24
	hours between 2 successive midnights defined by the local time
	zone.  The specific representation of a date will depend on
	which calendar convention is in force; e.g., Gregorian,
	Islamic, Japanese, Chinese, Hebrew etc. as well as local
	ordering conventions such as UK: day/month/year, US:
	month/day/year.

	Inputting and outputting dates on computers is greatly
	complicated by these {localisation} issues which is why they
	tend to operate on dates internally in some unified form such
	as seconds past midnight at the start of the first of January
	1970.

	Many software and hardware representations of dates allow only
	two digits for the year, leading to the {year 2000} problem.

	{Unix manual page}: date(1), ctime(3).

	(1997-07-11)

, 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 did not 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 doesn't 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

<data> A data structure holding many values, possibly of
	different types, which is usually accessed sequentially,
	working from the head to the end of the tail - an "ordered
	list".  This contrasts with a (one-dimensional) {array}, any
	element of which can be accessed equally quickly.

	Lists are often stored using a cell and pointer arrangement
	where each value is stored in a cell along with an associated
	pointer to the next cell.  A special pointer, e.g. zero, marks
	the end of the list.  This is known as a (singly) "linked
	list".  A doubly linked list has pointers from each cell to
	both next and previous cells.

	An unordered list is a {set}.

	(1998-11-12)

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?

From: strawberry

Date: Friday, May 25, 2007

wrote in message

In {object-oriented programming} sending a message to an
	{object} (to invoke a {method}) is equivalent to calling a
	{procedure} in traditional programming languages, except that
	the actual code executed may only be selected at run time
	depending on the {class} of the object.  Thus, in response to
	the message "drawSelf", the method code invoked would be
	different if the target object were a circle or a square.

	(1995-02-16)

I've trying to write a query that seems like it should be simple but
for some reason my attempts aren't working. This is really a general
SQL quesion and does not pertain to MySQL but I could not 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 did not 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 doesn't 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;

From: Good Man

Date: Monday, May 28, 2007

wrote in message in
@xxxxxxxxxxx:

I've trying to write a query that seems like it should be simple but
for some reason my attempts aren't working. This is really a general
SQL quesion and does not pertain to MySQL but I could not 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 did not 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 doesn't return anything useful since the
score returned seems to be based on where the grouping started in
someway.

Ideally, you would've 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'd
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

1. <database> A value used to identify a {record} in a
	database, derived by applying some fixed function to the
	record.  The key is often simply one of the {fields} (a
	{column} if the database is considered as a table with records
	being rows, see "{key field}").  Alternatively the key may be
	obtained by applying some function, e.g. a {hash function}, to
	one or more of the fields.  The set of keys for all records
	forms an {index}.  Multiple indexes may be built for one
	database depending on how it is to be searched.

	2. <cryptography> A value which must be fed into the
	{algorithm} used to decode an encrypted message in order to
	reproduce the original {plain text}.  Some encryption schemes
	use the same (secret) key to encrypt and decrypt a message,
	but {public key encryption} uses a "private" (secret) key and
	a "public" key which is known by all parties.

	3. <hardware> An electromechanical {keyboard} button.

	(2003-07-04)

to realizing there is a better way to design

<process> The approach that engineering (and some other)
	disciplines use to specify how to create or do something.  A
	successful design must satisfies a (perhaps informal)
	{functional specification} (do what it was designed to do);
	conforms to the limitations of the target medium (it is
	possible to implement); meets implicit or explicit
	requirements on performance and resource usage (it is
	efficient enough).

	A design may also have to satisfy restrictions on the design
	process itself, such as its length or cost, or the tools
	available for doing the design.

	In the {software life-cycle}, design follows {requirements
	analysis} and is followed by implementation.

	["Object-Oriented Analysis and Design with Applications", 2nd
	ed., Grady Booch].

	(1996-12-08)

your database is
any replication

<database, networking> Creating and maintaining a duplicate
	copy of a database or file system on a different computer,
	typically a {server}.  The term usually implies the
	intelligent copying of parts of the source database which have
	changed since the last replication with the destination.

	Replication may be one-way or two-way.  Two-way replication is
	much more complicated because of the possibility that a
	replicated object may have been updated differently in the two
	locations in which case some method is needed to reconcile the
	different versions.

	For example, {Lotus Notes} can automatically distribute
	document databases across telecommunications networks.  Notes
	supports a wide range of network {protocols} including {X25}
	and {Internet} {TCP/IP}.

	Compare {mirror}.  See also {rdist}.

	(1997-12-12)

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'd do it.

If you do not want to change your table design, I'd stick with your
query (minus the GROUP BY) and use a programming

1. The art of debugging a blank sheet of paper (or, in these
	days of on-line editing, the art of debugging an empty file).

	2. A pastime similar to banging one's head against a wall, but
	with fewer opportunities for reward.

	3. The most fun you can have with your clothes on (although
	clothes are not mandatory).

	[{Jargon File}]

	(2003-02-12)

language

1. <language, programming> {programming language}.

	2. <human language> {natural language}.

	(1998-09-07)

(ie: PHP) to
display the results correctly.

From: Israel

Date: Wednesday, May 30, 2007

wrote in message:

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'd 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

<programming> (Or "parameter") A name in a {function} or
	{subroutine} definition that is replaced by, or bound to, the
	corresponding {actual argument} when the function or
	subroutine is called.  In many languages formal arguments
	behave like {local variables} which get initialised on entry.

	See: {argument}.

	(2002-07-02)

data for the state

<storage, architecture, jargon, theory> How something is; its
	configuration, attributes, condition, or information content.
	The state of a system is usually temporary (i.e. it changes
	with time) and volatile (i.e. it will be lost or reset to some
	initial state if the system is switched off).

	A state may be considered to be a point in some {space} of all
	possible states.  A simple example is a light, which is either
	on or off.  A complex example is the electrical activation in
	a human brain while solving a problem.

	In computing and related fields, states, as in the light
	example, are often modelled as being {discrete} (rather than
	continuous) and the transition from one state to another is
	considered to be instantaneous.  Another (related) property of
	a system is the number of possible states it may exhibit.
	This may be finite or infinite.  A common model for a system
	with a finite number of discrete state is a {finite state
	machine}.

	[{Jargon File}]

	(1996-10-13)

of some hardware

<hardware> The physical, touchable, material parts of a
	computer or other system.  The term is used to distinguish
	these fixed parts of a system from the more changable
	{software} or {data} components which it executes, stores, or
	carries.

	Computer hardware typically consists chiefly of electronic
	devices ({CPU}, {memory}, {display}) with some
	electromechanical parts (keyboard, {printer}, {disk drives},
	{tape drives}, loudspeakers) for input, output, and storage,
	though completely non-electronic (mechanical,
	electromechanical, hydraulic, biological) computers have also
	been conceived of and built.

	See also {firmware}, {wetware}.

	(1997-01-23)

components on a machine

Common term for "computer", usually when considered at the
	hardware level.  The {Turing Machine}, an early example of
	this usage, was however neither hardware nor software, but
	only an idea.

	[Earlier use?]

	(1995-02-15)

that get
logged at a regular interval but I wanted to create a query that'd
just show me the latest for all of the components at arbitrary times.
This query'd 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

1. <operating system> A record of previous user inputs (e.g. to
	a {command interpreter}) which can be re-entered without
	re-typing them.  The major improvement of the {C shell} (csh)
	over the {Bourne shell} (sh) was the addition of a command
	history.  This was still inferior to the history mechanism on
	{VMS} which allowed you to recall previous commands as the
	current input line.  You could then edit the command using
	cursor motion, insert and delete.  These sort of history
	editing facilities are available under {tcsh} and {GNU Emacs}.

	2. <history> {The history of computing
	(http://ei.cs.vt.edu/~history/index.html)}.

	3. See {Usenet} newsgroups {news:soc.history} and
	{news:alt.history} for discussion of the history of the world.

	(1995-04-05)

for post-analysis and can have on the
order of 100k-200k record

<data, database, programming> An {ordered set} of {fields},
	usually stored contiguously.  The term is used with similar
	meaning in several different contexts.  In a file, a "record"
	probably has some fixed length, in contrast to a "line" which
	may have any length and is terminated by some {End Of Line}
	sequence).  A {database} record is also called a "row".  In a
	{spreadsheet} it is always called a "row".  Some programming
	languages use the term to mean a type composed of fields of
	several other types ({C} calls this a "{struct}").

	In all these cases, a record represents an entity with certain
	field values.

	Fields may be of a fixed width ({bits} or {characters}) or
	they may be separated by a {delimiter} character, often
	{comma} ({CSV}) or {HT} ({TSV}).

	In a database the list of values of a given field from all
	records is called a column.

	(2002-03-22)

s so join

1. <database> {inner join} (common) or {outer join} (less
	common).

	2. <theory> {least upper bound}.

	(1998-11-23)

ing a table back onto

<mathematics> A function f : A -> B is surjective or onto or a
	surjection if f A = B.  I.e. f can return any value in B.
	This means that its {image} is its {codomain}.

	Only surjections have {right inverses}, f' : B -> A where
	f (f' x) = x since if f were not a surjection there would be
	elements of B for which f' was not defined.

	See also {bijection}, {injection}.

	(1995-05-27)

itself isn't
really option

<software> (Or "option", "flag", "switch", "option switch") An
	argument to a command that modifies its function rather than
	providing data.  Options generally start with "-" in {Unix} or
	"/" in {MS-DOS}.  This is usually followed by a single letter
	or occasionally a digit.

	Some commands require each option to be a separate argument,
	introduced by a new "-" or "/", others allow multiple option
	letters to be concatenated into a single argument with a
	single "-" or "/", e.g. "ls -al".  A few Unix commands
	(e.g. {ar}, {tar}) allow the "-" to be omitted.  Some options
	may or must be followed by a value, e.g. "cc prog.c -o prog",
	sometimes with and sometimes without an intervening space.

	{getopt} and {getopts} are commands for parsing command line
	options.  There is also a {C} library routine called getopt
	for the same purpose.

	(1996-12-11)

considering how slow it is.
Unless I'm missing something about the performance of joining in this
way I will probably have to resort to multiple independent queries -
hence my original thought of using UNION ALL which I could not seem to
get the syntax

The structure of strings in some language.  A language's
	syntax is described by a {grammar}.  For example, the syntax
	of a binary number could be expressed as

		binary_number = bit [ binary_number ]

		bit = "0" | "1"

	meaning that a binary number is a bit optionally followed by a
	binary number and a bit is a literal zero or one digit.

	The meaning of the language is given by its {semantics}.

	See also {abstract syntax}, {concrete syntax}.

	(1994-10-31)

correct for.
I was trying to avoid having to write any code

<software> Instructions for a computer in some programming
	language, often {machine language}.  The word "code" is often
	used to distinguish instructions from {data} (e.g. "The code
	is marked 'read-only'") whereas "{software}" is used in
	contrast with "{hardware}" and may consist of more than just
	code.

	(2000-04-08)

because we already have
plans in the works for making a front end application

1. {application program}.

	2. {function application}.

that will allow
users to extract all sorts of data so I do not 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'll 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.

From: Israel

Date: Wednesday, May 30, 2007

wrote in message:

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

<data, database, programming> An {ordered set} of {fields},
	usually stored contiguously.  The term is used with similar
	meaning in several different contexts.  In a file, a "record"
	probably has some fixed length, in contrast to a "line" which
	may have any length and is terminated by some {End Of Line}
	sequence).  A {database} record is also called a "row".  In a
	{spreadsheet} it is always called a "row".  Some programming
	languages use the term to mean a type composed of fields of
	several other types ({C} calls this a "{struct}").

	In all these cases, a record represents an entity with certain
	field values.

	Fields may be of a fixed width ({bits} or {characters}) or
	they may be separated by a {delimiter} character, often
	{comma} ({CSV}) or {HT} ({TSV}).

	In a database the list of values of a given field from all
	records is called a column.

	(2002-03-22)

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 not switch

1. <programming> {switch statement}.

	2. <software> {command line option}.

	3. <networking> {packet switch}, {circuit switch}.

	(1999-01-14)

the ORDER BY and
GROUP BY clauses.

From: strawberry

Date: Wednesday, May 30, 2007

wrote in message:
wrote in message:
> 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 not switch the ORDER BY and
GROUP BY clauses.

I can not 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

<language, tool> A {Unix} tool written in {Tcl} and a {script
	language} for automating the operation of {interactive}
	applications such as {telnet}, {FTP}, {passwd}, {fsck},
	{rlogin}, {tip}, etc..  Expect can feed input to other
	programs and perform {pattern matching} on their output.  It
	is also useful for testing these applications.  By adding
	{Tk}, you can also wrap interactive applications in {X11}
	{GUIs}.

	{Home (http://expect.nist.gov/)}.

	["expect: Scripts for Controlling Interactive Tasks", Don
	Libes, Comp Sys 4(2), U Cal Press Journals, Nov 1991].

	(1997-06-09)

to
obtain from that, perhaps we can help. Ideally include

[{Usenet}] 1. To duplicate a portion (or whole) of another's
	message (typically with attribution to the source) in a reply
	or followup, for clarifying the context of one's response.
	See the discussion of inclusion styles under "Hacker Writing
	Style".

	2. [{C}] "#include <disclaimer.h>" has appeared in {sig
	blocks} to refer to a notional "standard {disclaimer} file".

	[{Jargon File}]

the CREATE and
INSERT statements too.

From: Israel

Date: Wednesday, May 30, 2007

wrote in message:

I can not 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.

From: strawberry

Date: Thursday, May 31, 2007

wrote in message:
wrote in message:
> I can not 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 index

(Plural "indices" or "indexes")

	1. <programming> A number used to select an element of a list,
	vector, {array} or other sequence.  Such indices are nearly
	always non-negative integers but see {associative array}.

	2. <database> See {inverted index}.  [Other kinds?]

	3. <World-Wide Web> A {search engine}.

	4. <World-Wide Web> A {subject index}.

	4. <jargon> See {coefficient of X}.

	[{Jargon File}]

	(1997-04-09)

es I'd imagine that it'd take
about 5 seconds. Is that too slow? Is 'GROUP BY' quicker!?!.

From: Israel

Date: Thursday, May 31, 2007

wrote in message:

Too slow? With appropriate indexes I'd imagine that it'd 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

<data, database> An area of a {database} {record}, or
	{graphical user interface} {form}, into which a particular
	item of data is entered.

	Example usage: "The telephone number field is not really a
	numerical field", "Why do we need a four-digit field for the
	year?".

	A {database} {column} is the set of all instances of a given
	field from all records in a {table}.

	(1999-04-26)

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 did not have the patience to wait for
the query for the larger databases but I can only assume that it has
an exponential

1. <mathematics> A function which raises some given constant
	(the "base") to the power of its argument.  I.e.

		f x = b^x

	If no base is specified, {e}, the base of {natural
	logarthims}, is assumed.

	2. <complexity> {exponential-time algorithm}.

	(1995-04-27)

growth.

I ran the following query (which does not 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

<processor> {Intel}'s {superscalar} successor to the {486}.
	It has two 32-bit 486-type integer {pipelines} with dependency
	checking.  It can execute a maximum of two instructions per
	cycle.  It does pipelined {floating-point} and performs
	{branch prediction}.  It has 16 {kilobytes} of on-chip
	{cache}, a 64-bit memory interface, 8 32-bit general-purpose
	{registers} and 8 80-bit {floating-point} registers.  It is
	built from 3.1 million transistors on a 262.4 mm^2 die with
	~2.3 million transistors in the core logic.  Its {clock rate}
	is 66MHz, heat dissipation is 16W, integer performance is 64.5
	{SPECint92}, {floating-point} performance 56.9 {SPECfp92}.

	It is called "Pentium" because it is the fifth in the 80x86
	line.  It would have been called the 80586 had a US court not
	ruled that you can't trademark a number.

	The successors are the {Pentium Pro} and {Pentium II}.

	The following Pentium variants all belong to "x86 Family 6",
	as reported by "Microsoft Windows" when identifying the CPU:

	 Model  Name
	 1      Pentium Pro
	 2      ?
	 3      Pentium II
	 4      ?
	 5, 6   Celeron or Pentium II
	 7      Pentium III
	 8      Celeron uPGA2 or Mobile Pentium III

	A {floating-point division bug
	(ftp://ftp.isi.edu/pub/carlton/pentium/FAQ)} was discovered in
	October 1994.

	[Internal implementation, "Microprocessor Report" newsletter,
	1993-03-29, volume 7, number 4].

	[Pentium based computers, PC Magazine, 1994-01-25].

	(2003-09-30)

laptop with 1GB RAM

1. <storage> {Random Access Memory}.

	2. <humour> Rarely Adequate Memory.

	A humorous reference to the fact that programs and data
	expand to fill the memory available.

	(1995-04-22)

and I'm running MySQL
server 4.1.

From: strawberry

Date: Thursday, May 31, 2007

wrote in message:
wrote in message:
> Too slow? With appropriate indexes I'd imagine that it'd 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 did not 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 does not 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?

From: Israel

Date: Thursday, May 31, 2007

wrote in message:

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

1. To copy {data} (often {program} {code} to be {run}) into
	{memory}, possibly {parsing} it somehow in the process.
	E.g. "{WordPerfect} can't load this {RTF} file - are you sure
	it didn't get corrupted in the {download}?"  Opposite of
	{save}.

	2. The degree to which a computer, {network}, or other
	resource is used, sometimes expressed as a percentage of the
	maximum available.  E.g. "What kind of CPU load does that
	program give?", "The network's constantly running at 100%
	load".  Sometimes used, by extension, to mean "to increase the
	level of use of a resource".  E.g. "Loading a spreadsheet
	really loads the CPU".  See also: {load balancing}.

	3. To {install} a piece of {software} onto a system.
	E.g. "The computer guy is gonna come load Excel on my laptop
	for me".  This usage is widely considered to be incorrect.

	(2002-07-02)

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

<testing> The process of exercising a product to identify
	differences between expected and actual behaviour.  Typically
	testing is bottom-up: {unit testing} and {integration testing}
	by developers, {system testing} by testers, and {user
	acceptance testing} by users.

	{Test coverage} attempts to assess how complete a test has
	been.

	2. The second stage in a {generate and test} search
	{algorithm}.

	[{Jargon File}]

	(2003-09-24)

I can not determine whether or not
putting an index on sampletimestamp had any appreciable effect.

From: Good Man

Date: Friday, June 01, 2007

wrote in message in
@xxxxxxxxxxx:> 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

<data processing, signal processing> Performing some
	predefined sequence of operations on an input to produce an
	output or change of internal state; activity specifically
	involving the computer's {CPU}.

	The term is often qualified: "{data processing}" treats
	{digital} data, "{signal processing}" treats {analog} data
	(possibly in digital form), "{word processing}" takes in typed
	human language input and produces digital documents, {image
	processing} transforms digital {images}.

	(2003-10-23)

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?

Next Message: mySQL backup with SQLyog

Blogs related to UNION, LIMIT and ORDER BY

Implementing a queue in SQL (Postgres version)
greg=# DROP RULE fruitlimit ON q; DROP RULE greg=# CREATE FUNCTION fruitlimit() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ DECLARE maxrows INT = 5; rows INT; BEGIN DELETE FROM q WHERE id NOT IN (SELECT id FROM q ORDER BY id DESC LIMIT ...

Re: Complicated UNION query
... works only if I remove GROUP BY and ORDER BY and it > takes very long time to execute, I have a very large table that's why I > wanted to split it into small identical tables. I think mysql doesn't > support joining tables in union. ...

trying to narrow down my results a bit...
... wow_posts.bid, wow_posts.category, wow_posts.subject from wow_comment LEFT JOIN wow_posts on wow_comment.bid=wow_posts.bid UNION SELECT postdate, user, bid, category, subject from wow_posts ORDER BY postdate DESC Limit 10 ...

Re: different order in a union all clause
Note: >> On MySQL, all i need to do is to use parentheses to enclose each >> select... >> (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) >> UNION >> (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);-------------- ...

Re: different order in a union all clause
Note: > On MySQL, all i need to do is to use parentheses to enclose each select... > (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) > UNION > (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);-------------- > Select c ...

[SQL and Code Injection] LIMIT and ORDER BY MySQL injection
It is a LIMIT flaw and it could be exploited to do an UNION SELECT query. No multiple queries are allowed, it seems... And the ';' char doesn't work. There's magic_quotes activated. Please help me, or at least try and let us know if you ...