Ordering problem 1

[rawkin]

Hi,

I have two tables, one containing account details and the second containing the account history. I'm trying to pull the date the account was last updated along with the account details in a single query;

SELECT
a.id, a.contact_name, a.username, a.status, b.timestamp
FROM
accounts a, history b
WHERE
(a.id = b.account_id)
GROUP BY username;

The only problem with this query is that it returns the timestamp of the first history record, rather than the last history record for that user. Is there a way to incorporate some sort of 'ORDER BY b.timestamp' prior to the 'GROUP BY' ?

thanks,

Pete

 

[TonyGroves]

This should work:
[tt]
SELECT
a.id,a.contact_name,a.username,a.status,
MAX(b.timestamp)
FROM
accounts a JOIN history b ON a.id=b.account_id
GROUP BY a.username
[/tt]

 

[TonyGroves]

Sorry, that should be:
[tt]
SELECT
a.id,a.contact_name,a.username,a.status,
MAX(b.timestamp)
FROM
accounts a JOIN history b ON a.id=b.account_id
GROUP BY a.id
[/tt]

 

[rawkin]

That's done the trick thanks very much for your help.

Pete

 

[rawkin]

A small add-on to this problem

The query provides the entire list of customers with their last contact time. Now I'm trying to order the entire list by the contact time so the most recently contacted are displayed at the top.

I tried;

SELECT
a.id,a.contact_name,a.username,a.status,
MAX(b.timestamp)
FROM
accounts a JOIN history b ON a.id=b.account_id
GROUP BY a.id ORDER BY b.timestamp DESC

This returns a mostly ordered list, but some entries appear completely out of place .. I suspect this is a result of the use of GROUP BY. Is there another approach to ordering the results by timestamp?

thanks,

Pete

 

[TonyGroves]

How about:
[tt]
SELECT
a.id,a.contact_name,a.username,a.status,
MAX(b.timestamp) t
FROM
accounts a JOIN history b ON a.id=b.account_id
GROUP BY a.id
ORDER BY t DESC
[/tt]