Sorting and then grouping a table

#1

Hi,

I have a table that contains orders, but I only want to select the
latest orders from every client. Every order has a client_id, so I
tried this:

SELECT * FROM `orders` ORDER BY date DESC GROUP BY client_id;

However, that doesn't work. This does:

SELECT * FROM `orders` GROUP BY client_id ORDER BY date DESC;

But it doesn't return the latest orders from every client, it selects
orders by unique clients and then sorts it by date.

How can I get the result I want?

Thank you!
Jaap Haagmans

#2

On 11/9/2010 9:05 AM, jhaagmans wrote:

Quote:

Hi,

I have a table that contains orders, but I only want to select the
latest orders from every client. Every order has a client_id, so I
tried this:

SELECT * FROM `orders` ORDER BY date DESC GROUP BY client_id;

However, that doesn't work. This does:

SELECT * FROM `orders` GROUP BY client_id ORDER BY date DESC;

But it doesn't return the latest orders from every client, it selects
orders by unique clients and then sorts it by date.

How can I get the result I want?

Thank you!
Jaap Haagmans

An often asked question. Google this group for "strawberry query".

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#3

"jhaagmans" <jaap.haagmans (AT) gmail (DOT) com> wrote

Quote:

Hi,

I have a table that contains orders, but I only want to select the
latest orders from every client. Every order has a client_id, so I
tried this:

SELECT * FROM `orders` ORDER BY date DESC GROUP BY client_id;

However, that doesn't work. This does:

SELECT * FROM `orders` GROUP BY client_id ORDER BY date DESC;

But it doesn't return the latest orders from every client, it selects
orders by unique clients and then sorts it by date.

How can I get the result I want?

SELECT client_id, MAX(`date`) FROM `orders` GROUP BY client_id ORDER BY
`date` DESC;

This is the danger in selecing fields that are not part of the grouping and
have no aggregate function...

#4

Thank you both. The term "strawberry query" is new to me, but it has
helped me understand this. Great stuff!

Jaap

#5

On 11/11/2010 12:56 PM, jhaagmans wrote:

Quote:

Thank you both. The term "strawberry query" is new to me, but it has
helped me understand this. Great stuff!

Jaap

That's because the term is specific to this newsgroup - from the famous
poster "strawberry" who has been very eloquent on the subject.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#6

On Thu, 11 Nov 2010 15:58:29 -0500, Jerry Stuckle wrote:

Quote:

On 11/11/2010 12:56 PM, jhaagmans wrote:
Thank you both. The term "strawberry query" is new to me, but it has
helped me understand this. Great stuff!

Jaap

That's because the term is specific to this newsgroup - from the famous
poster "strawberry" who has been very eloquent on the subject.

The method of strawberry reproduction via a runner to create a clone
plant at another location is a happy coincidence. The two or more
strawberry plants thusly connected are a very apt visualization of how
one might represent the behavior of the query....

--
A way of life that is odd or even erratic but interferes with no rights
or interests of others is not to be condemned because it is different.
-- Chief Justice Warren E. Burger

#7

On Nov 12, 3:55*am, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:

Quote:

On Thu, 11 Nov 2010 15:58:29 -0500, Jerry Stuckle wrote:
On 11/11/2010 12:56 PM, jhaagmans wrote:
Thank you both. The term "strawberry query" is new to me, but it has
helped me understand this. Great stuff!

Jaap

That's because the term is specific to this newsgroup - from the famous
poster "strawberry" who has been very eloquent on the subject.

The method of strawberry reproduction via a runner to create a clone
plant at another location is a happy coincidence. The two or more
strawberry plants thusly connected are a very apt visualization of how
one might represent the behavior of the query....

--
A way of life that is odd or even erratic but interferes with no rights
or interests of others is not to be condemned because it is different.
* * * * * * * * * * * * * * -- Chief Justice Warren E. Burger

Just for clairification, that is not how I reproduce. ;-)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Sorting and then grouping a table

comp.databases.mysql comp.databases.mysql

Sorting and then grouping a table - 11-09-2010 , 08:05 AM

Re: Sorting and then grouping a table - 11-09-2010 , 08:30 AM

Re: Sorting and then grouping a table - 11-09-2010 , 08:52 AM

Re: Sorting and then grouping a table - 11-11-2010 , 11:56 AM

Re: Sorting and then grouping a table - 11-11-2010 , 02:58 PM

Re: Sorting and then grouping a table - 11-11-2010 , 09:55 PM

Re: Sorting and then grouping a table - 11-12-2010 , 11:55 AM