Page 1 of 4
How do I group by, but only show the latest in the DB?
Posted: Thu May 22, 2014 3:10 am
by simonmlewis
I am building a system where people raise tickets.
Front screen shows each of the ticket numbers, and the latest info on it.But it's show all tickets.
I did a group by, but it always shows the first of the sets it finds. So if there are three rows for a ticket number, it shows the first it finds. I want it to show the last, but only that one row.
So if there are three rows for 123, and 2 rows for 456 ticket numbers. I only want it to show:
123
456
Re: How do I group by, but only show the latest in the DB?
Posted: Thu May 22, 2014 7:17 am
by Celauran
Use a subquery to get the order?
Re: How do I group by, but only show the latest in the DB?
Posted: Thu May 22, 2014 7:25 am
by simonmlewis
Trust me, I cannot suss this one out.
I have several screens, unanswered, closed, ongoing.
But I want one "show all" screen that does the lot. But if I do that. I get multiples of each, and I need to only show the latest entry of the ticket number.
So if ticket 123, has three rows for it, I want to nly show the latest row.
Re: How do I group by, but only show the latest in the DB?
Posted: Thu May 22, 2014 7:34 am
by Celauran
Right, and you're tying to use GROUP BY for ordering, which isn't going to work. You're doing something like this
Code: Select all
SELECT foo, bar
FROM table_name
WHERE whatever
GROUP BY ticket_number
ORDER BY date_created DESC
And I'm suggesting trying something like this instead
Code: Select all
SELECT foo, bar
FROM (
SELECT foo, bar
FROM table_name
WHERE whatever
ORDER BY date_created DESC
)
GROUP BY ticket_number
Is that not working?
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:13 am
by simonmlewis
Code: Select all
$query = "SELECT * FROM
{
SELECT * FROM tickets WHERE dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL ORDER BY id DESC
}
GROUP BY ticket";
$result = $pdo->query($query);
$num_rows = $result->rowCount();
This is pulling up an error:
[text]Fatal error: Call to a member function rowCount() on a non-object in C:\xampp\phpMyAdmin\site\includes\a_tickets.inc on line 61[/text]
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:16 am
by Celauran
Which means $result is false, which means your query is bad. Your subquery needs to be surrounded by parentheses, not braces.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:19 am
by simonmlewis
Code: Select all
$query = "SELECT * FROM (select * tickets WHERE dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL ORDER BY id DESC) GROUP BY ticket";
This has same effect.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:20 am
by Celauran
Alright, so with the obvious syntax error out of the way, what errors is this query generating?
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:25 am
by simonmlewis
Code: Select all
$query = "SELECT * FROM (select * tickets WHERE dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL ORDER BY id DESC) GROUP BY ticket";
$result = $pdo->query($query);
$num_rows = $result->rowCount();
[text]Fatal error: Call to a member function rowCount() on a non-object in C:\xampp\phpMyAdmin\site\includes\a_tickets.inc on line 46[/text]
Here it is. Line 46 is :
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:28 am
by Celauran
Yes, that was already established. $result is false, which means $pdo->query failed. So why did it fail? The error is in your query itself.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:29 am
by simonmlewis
Correction:
Code: Select all
$query = "SELECT * FROM (SELECT * FROM tickets WHERE dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL ORDER BY id DESC) GROUP BY ticket";
"missing the "FROM..." in the () area.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:29 am
by simonmlewis
Sorry to sound blunt, but I kinda realised that - I'm trying to ascertain where....
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:45 am
by Celauran
Have you checked $pdo->errorInfo()? Run the query manually? Checked error logs?
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:48 am
by simonmlewis
Run locally it says:
#1248 - Every derived table must have its own alias
Re: How do I group by, but only show the latest in the DB?
Posted: Fri May 23, 2014 7:53 am
by simonmlewis
Think I've got it.
In yours and another version like it I found online, it didn't show the ...AS ticket_sub.
To give the innertable another name.
Sorted it now and it works.
Code: Select all
$query = "SELECT * FROM (SELECT * FROM tickets WHERE dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL ORDER BY id DESC) AS ticketsub GROUP BY ticket";