[RESOLVED] Simply view the latest entries - SQL

kikki

Hi there,
I've got a problem, I wonder how to fix this issue with simple query..

I've got 2 tables.. category, pictures, how to get only the latest (the last) picture from all categories?

I've tried myself, but it shows all the old images from categories not the latest picture, even though I used ORDER BY id DESC.. do I have to sort that before I group them?


$query = "SELECT `picture`, `category`, `cateid` 
FROM pixers 
GROUP BY `cateid` 
ORDER BY `category` ASC, `id` DESC";

Thanks!

jpr

Hello,

The way you have your query written right now will normally return the entire table (all pictures), ordered by most recent to oldest. If you only want one result to be returned, then you need to add a LIMIT of 1 to your query. That will it will be ordered by most recent, and it will only take the top result.

$query = "SELECT picture, category, cateid
FROM pixers
GROUP BY cateid
ORDER BY category ASC, id DESC
LIMIT 0,1";

-Jason

kikki

jpr wrote:
Hello,

The way you have your query written right now will normally return the entire table (all pictures), ordered by most recent to oldest. If you only want one result to be returned, then you need to add a LIMIT of 1 to your query. That will it will be ordered by most recent, and it will only take the top result.

$query = "SELECT picture, category, cateid
FROM pixers
GROUP BY cateid
ORDER BY category ASC, id DESC
LIMIT 0,1";

-Jason

Thanks for the reply jpr, but the problem is it doesn't show the recent pictures instead it shows oldest images, I really don't know what is wrong with it..

I'm listing one per category.. so categories are not limited since I'm grouping images by category ID to show only one from a category.. ordering suppose to show the recent picture from the latest id, but it doesn't.. 🙁

I'm still wondering around to fix this issue..

TIA

bradgrafelman

So it's correctly grouping the images and only showing one per category, yet it's showing the oldest image from each?

Out of curiosity, does it change if you switch "id DESC" to "id ASC" in your ORDER BY clause?

kikki

bradgrafelman wrote:
So it's correctly grouping the images and only showing one per category, yet it's showing the oldest image from each?

Out of curiosity, does it change if you switch "id DESC" to "id ASC" in your ORDER BY clause?

Thanks for the reply bradgrafelman,
the craziest thing is.. when I changed "id DESC" to "id ASC", the output result is the same as previously, I wonder whats wrong... 🙁

This is my exact SQL code

	
$query = "SELECT count(*) AS `totalpixer`, `picture`, `category`, `cateid` 
	FROM pixers 
	WHERE (`activated`=1) 
	GROUP BY `cateid` 
	ORDER BY `category` ASC, `id` DESC 
	LIMIT " . $ss. ", " . $rpp;

TIA

Piranha

The problem is that ORDER BY and GROUP BY doesn't work well together. The order by part is run after GROUP BY. Limit is not run until after the two other parts. At least I think it is like this, don't know for sure. What I do know for sure is that it won't work as you want it to.

I think this is one of the rare occations when you can't solve it with one normal SQL question, you probably need a bunch of them. The reason is that you can use limit to get the latest result from one catid, but not to get the latest from each of the ids. Something like this would work:

select distinct catid  // order by whatever you want
foreach result
{
select columns from table where catid = catid_from_above order by date desc limit 0, 1
}

Unfortunatley I can only see one other solution to this, and it is to do the same with a stored procedure. It would be a better solution, but still it would have to do multiple queries. If anyone can make it with one query (with or without subqueries) it would probably be a better solution.

Piranha

I came to think about another solution. In the categories table you could use one column to store what picture was the last one, and then update that all the time. Then all you have to do is to run one query where you join the tables.

kikki

Piranha wrote:
I came to think about another solution. In the categories table you could use one column to store what picture was the last one, and then update that all the time. Then all you have to do is to run one query where you join the tables.

Thank you for replying Piranha..
This sounds weird, because why SQL doesn't show normally like it should? There are few other ways if I wanted to use multiple queries, like for example.. executing other queries on the loop of categories listing or something, or just do one function with a query to get the last image from the database and call it when listing categories, but I was wondering if there is a way to fix this issue just with one query, because the question is not complex, many others must have been through this issue before me, I think SQL should be able to do it with one query. Please let me know your opinion.

thanks!

Piranha

kikki wrote:
This sounds weird, because why SQL doesn't show normally like it should?

It does. But you don't understand how it should show it. Me neither in this case. Read up on the rules of queries to understand it.

kikki wrote:
because the question is not complex

If it is not complex it should not be any problem, and you should not need to ask here. If you have to ask you don't know if it is complex or not. Make a list of the steps the query have to do, and rank them in the order they have to be done. Then you will get to know if the query is complex or not. I didn't manage to do that list, but since it is not complex you won't have any problem...

kikki wrote:
Please let me know your opinion.

I think I did in the two posts I already posted in this thread. Don't hesitate to ask, but please read the posts already posted first.

kikki

Piranha wrote:
It does. But you don't understand how it should show it. Me neither in this case. Read up on the rules of queries to understand it.

hmm, I'm not sure what your trying to say. I was very clear when I said that the result of this query isn't what I'm trying to archive here. But you're right about one thing; that we both aren't sure in this case how the query should be to get latest pictures listed in one query or how it should be re-arranged to get it working.

Piranha wrote:
If it is not complex it should not be any problem, and you should not need to ask here. If you have to ask you don't know if it is complex or not. Make a list of the steps the query have to do, and rank them in the order they have to be done. Then you will get to know if the query is complex or not. I didn't manage to do that list, but since it is not complex you won't have any problem...

Well, when I said it's not complex, I meant that the question is very clear and sounds easy, but in practical it doesn't give exact result, take it easy bro.

Piranha wrote:
I think I did in the two posts I already posted in this thread. Don't hesitate to ask, but please read the posts already posted first.

Thank you

kikki

I've solved this issue.. thanks for your help jpr, bradgrafelman and Piranha.. I really appreciate your help..

I think there are no other simpler way to solve it, so I've used inner join.. and it worked. Thanks for all your help guys, really appreciated. 🙂

Piranha

kikki wrote:
hmm, I'm not sure what your trying to say. I was very clear when I said that the result of this query isn't what I'm trying to archive here. But you're right about one thing; that we both aren't sure in this case how the query should be to get latest pictures listed in one query or how it should be re-arranged to get it working.

What I ment is that the query is doing what it is told to do. The problem is that neither you nor I understand exactly what we tell it to do. To understand what we ask from it we should read up the database rules.

I am sure. It can't be done with a normal query. The question is clear and easy to you and me, but when breaking it up and try to solve it step by step it is not that easy anymore.

Piranha

kikki wrote:
I've solved this issue.. thanks for your help jpr, bradgrafelman and Piranha.. I really appreciate your help..

I think there are no other simpler way to solve it, so I've used inner join.. and it worked. Thanks for all your help guys, really appreciated. 🙂

Please show us the query that solved the problem. Since I haven't even been able to solve it on paper I would like to see the solution.

kikki

Piranha wrote:
Please show us the query that solved the problem. Since I haven't even been able to solve it on paper I would like to see the solution.

Sure.. 🙂

	$query = "SELECT pixer_category.`id`, pixer_category.`category`, pixers.`picture` 
	FROM pixer_category INNER JOIN pixers ON pixer_category.`id` = pixers.`cateid` 
	GROUP BY `cateid` 
	ORDER BY `category` ASC, `id` DESC";