Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to this, so please be patient...

  1. I have a table "tFruit".
  2. The fields inside tFruit are "ID", "Buyer", "Seller", "Time", and "FruitName".
  3. "ID" is the key field, the other 4 fiends has non-unique data.

When I use this SQL statement I will get all sellers sorted by Time. But an individual seller appears multiple times because they sold fruits not only once.

SELECT Seller 
FROM tFruit 
WHERE Buyer = 'email@address.com' 
ORDER BY Time DESC

What I want is to get the list of seller, non-duplicate, sorted by Time. Thus I use this statement:

SELECT Seller 
FROM tFruit 
WHERE Buyer = 'email@address.com' 
GROUP BY Seller 
ORDER BY Time DESC

Now there is no duplicate Seller, but they are not sorted by Time.

Any ideas how to do this?

Thanks!

share|improve this question

1 Answer 1

Try adding the Time field to your select list, as in:

SELECT Seller, Max(Time) AS MostRecentTime 
FROM tFruit 
WHERE Buyer = 'email@address.com' 
GROUP BY Seller 
ORDER BY Max(Time) DESC;

Max(Time) as MostRecentTime will display the most recent Time for the given Seller. You don't really need this in the SELECT clause, you really only need MAX(Time) in the ORDER BY clause. I've added it to the SELECT clause so you can see the results are sorted by Time in descending order.

share|improve this answer
2  
Also if you put in the SELECT list, you can use ORDER BY MostRecentTime DESC –  ypercube Sep 18 '13 at 7:05
    
Note side some extra thoery why... GROUP BY will always fetch the first FIFO record that matches the GROUP BY column(s). And only the column Seller is correct, the column Time is "random" matched. ORDER BY wont work because it will run after the GROUP BY thats why you cant sort un the Time column like that, GROUP BY is sometimes an evil thing in MySQL where mistakes are very easy to make –  Raymond Nijland Sep 18 '13 at 23:26
    
I admit I didn't see the mysql tag. –  Max Vernon Sep 18 '13 at 23:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.