How do I group by, but only show the latest in the DB?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

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?

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I group by, but only show the latest in the DB?

Post by Celauran »

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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I group by, but only show the latest in the DB?

Post 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?
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?

Post 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]
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I group by, but only show the latest in the DB?

Post by Celauran »

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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I group by, but only show the latest in the DB?

Post by Celauran »

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?

Post 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 :

Code: Select all

$num_rows = $result->rowCount();
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I group by, but only show the latest in the DB?

Post 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.
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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
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?

Post by simonmlewis »

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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I group by, but only show the latest in the DB?

Post by Celauran »

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?

Post by simonmlewis »

Run locally it says:

#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.
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?

Post 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";
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply