How do I group by, but only show the latest in the DB?
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
How do I group by, but only show the latest in the DB?
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
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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
Use a subquery to get the order?
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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.
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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
Right, and you're tying to use GROUP BY for ordering, which isn't going to work. You're doing something like this
And I'm suggesting trying something like this instead
Is that not working?
Code: Select all
SELECT foo, bar
FROM table_name
WHERE whatever
GROUP BY ticket_number
ORDER BY date_created DESC
Code: Select all
SELECT foo, bar
FROM (
SELECT foo, bar
FROM table_name
WHERE whatever
ORDER BY date_created DESC
)
GROUP BY ticket_number-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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();
[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]
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
Which means $result is false, which means your query is bad. Your subquery needs to be surrounded by parentheses, not braces.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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";Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
Alright, so with the obvious syntax error out of the way, what errors is this query generating?
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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();Here it is. Line 46 is :
Code: Select all
$num_rows = $result->rowCount();Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
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.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
Correction:
"missing the "FROM..." in the () area.
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";Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
Sorry to sound blunt, but I kinda realised that - I'm trying to ascertain where....
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
Have you checked $pdo->errorInfo()? Run the query manually? Checked error logs?
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
Run locally it says:
#1248 - Every derived table must have its own alias
#1248 - Every derived table must have its own alias
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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.
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";Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.