Joining, grouping and ordering

Dev Shed Forums Sponsor:

July 3rd, 2009, 04:41 AM

Northie

Square Peg in a Round Hole

Join Date: Oct 2007

Location: North Yorkshire, UK

Posts: 462

Time spent in forums: 3 Days 9 h 27 m 6 sec
Reputation Power: 266

Joining, grouping and ordering

HI guys and gals,

I really need some advice here - i have complete brain freeze over this problem.

I have (among other things) two tables. One of these has customer details in it, the other is a log of all requests to the site.

If a customer is logged in, then the log table records the customer id as well (or null otherwise).

The log table was only ever designed to be used for generating basic stats reports but now my client wants to see when a customer last logged in.

I have currently got this sql

Code:

SELECT
	c.*,
	vh.time
FROM
	customers c
INNER JOIN
	visitor_history vh
	ON
	c.unid = vh.customer
GROUP BY
	c.unid

but when it groups it takes a very early 'time' (log on time) value. How can i get it to get the most recent?

I think i may have to do some nested selects of use some other form of temporary table - but can't figure it out.

Any thoughts?

Many thanks in advance!

July 3rd, 2009, 04:46 AM

Northie

Square Peg in a Round Hole

Join Date: Oct 2007

Location: North Yorkshire, UK

Posts: 462

Time spent in forums: 3 Days 9 h 27 m 6 sec
Reputation Power: 266

Think I've answered my own question

I think I've answered my own question

Code:

SELECT
	*
FROM
	(
		SELECT
			c.*,
			vh.time
		FROM
			customers c
		INNER JOIN
			visitor_history vh
			ON
			c.unid = vh.customer
		ORDER BY
			vh.time DESC,
			c.surname
	) d
GROUP BY
	d.unid

done a quick test and it seems to work.

If there are any quicker or more elegant methods, please do respond!!!

July 3rd, 2009, 05:27 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 753

Time spent in forums: 2 Weeks 21 h 30 m 37 sec
Reputation Power: 107

Luckily, this question is answered almost every single day. There's even a page in the manual about it. Just do a search for "groupwise max mysql".

July 3rd, 2009, 08:45 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,687

Time spent in forums: 2 Months 1 Week 3 Days 9 h 27 m 54 sec
Reputation Power: 2458

Quote:

Originally Posted by Northie

done a quick test and it seems to work.

"seems" being the operative word

GROUP BY together with the dreaded, evil "select star" is almost always a recipe for disaster

without me going into details about why, just remember never to mix those two

Quote:

Originally Posted by Northie

now my client wants to see when a customer last logged in

then i would expect to see a WHERE clause for that customer, yes?

Code:

SELECT MAX(vh.time) AS last_logged_in
  FROM visitor_history vh
 WHERE vh.customer = 937

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 3rd, 2009, 09:51 AM

Northie

Square Peg in a Round Hole

Join Date: Oct 2007

Location: North Yorkshire, UK

Posts: 462

Time spent in forums: 3 Days 9 h 27 m 6 sec
Reputation Power: 266

hey, I'm posting shorthand here and would rather not reveal full database schemas!!!!

"seems" yeah - not exactly what was asked for - the query is returning the last time each customer made a request while logged in - good enough, as a I can't be bothered to link onto the unique_visitors table to pull out session information

And no, there is no requirement for a "where" statement as the log on date is to be displayed in the customer list, not the customer details page.

There is a separate page containing all requests made by that customer with a different query which does not relate to this one!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Last edited by Northie : July 3rd, 2009 at 09:54 AM.

July 3rd, 2009, 10:11 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,687

Time spent in forums: 2 Months 1 Week 3 Days 9 h 27 m 54 sec
Reputation Power: 2458

Quote:

Originally Posted by Northie

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

oh, that's okay then, forget i said anything

July 3rd, 2009, 10:14 AM

Northie

Square Peg in a Round Hole

Join Date: Oct 2007

Location: North Yorkshire, UK

Posts: 462

Time spent in forums: 3 Days 9 h 27 m 6 sec
Reputation Power: 266

Quote:

Originally Posted by r937

oh, that's okay then, forget i said anything

July 9th, 2009, 05:15 AM

Northie

Square Peg in a Round Hole

Join Date: Oct 2007

Location: North Yorkshire, UK

Posts: 462

Time spent in forums: 3 Days 9 h 27 m 6 sec
Reputation Power: 266

Quick update:

I had to change INNER JOIN to LEFT JOIN to get customers who had signed up but never logged in

Just in case anyone is going to use the above SQL for their own needs

Comments on this post

cafelatte disagrees: Not that they would ;-)

July 21st, 2009, 07:36 AM

Northie

Square Peg in a Round Hole

Join Date: Oct 2007

Location: North Yorkshire, UK

Posts: 462

Time spent in forums: 3 Days 9 h 27 m 6 sec
Reputation Power: 266

Ug, now they want to know if the customers have products ( which may or may not have been approved or not) and pagination!!!!

Code:

SELECT
	*
FROM
	(
		SELECT
			d.*,
			cp.has_products
		FROM
			(
				SELECT
					c.*,
					vh.time
				FROM
					customers c
				LEFT JOIN
					visitor_history vh
					ON
					c.unid = vh.customer
				ORDER BY
					vh.time DESC,
					c.surname
			) d

		LEFT JOIN
			(
				SELECT
					customer,
					count(id) AS has_products
				FROM
					customer_products
				WHERE
					time_approved > 0
				GROUP BY
					customer
			) cp
			ON
			d.unid = cp.customer
		GROUP BY
			d.unid
	) a
ORDER BY
	surname ASC
LIMIT
	0, 50

#10

July 21st, 2009, 08:50 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,687

Time spent in forums: 2 Months 1 Week 3 Days 9 h 27 m 54 sec
Reputation Power: 2458

ORDER BY inside a subquery is useless

and the GROUP BY d.unid is going to arbitrarily pick an indeterminate vh.time value, if there is more than one, so it isn't necessarily going to be the last logged in time

#11

July 21st, 2009, 08:57 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 753

Time spent in forums: 2 Weeks 21 h 30 m 37 sec
Reputation Power: 107

Quote:

Originally Posted by r937

ORDER BY inside a subquery is useless

unless used in conjunction with an OFFSET and/or LIMIT clause

Viewing: Dev Shed Forums > Databases > MySQL Help > Joining, grouping and ordering

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!