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:

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

Post by simonmlewis »

Going on from this, how do I do the same sort of (inner query) with this INNER JOIN?

Code: Select all

$query = "SELECT u.firstname, u.lastname, u.email, t.ticket, t.subject, t.userid, t.dateclosed, t.dateupdated, t.updatedby FROM admin AS u
INNER JOIN tickets AS t ON u.id = t.userid
WHERE u.firstname LIKE :search OR u.lastname LIKE :search OR email LIKE :search GROUP BY ticket";
This query itself works, with my other code below it.
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 »

I've cracked the Innerjoin - by not using one! Did it another way that works completely.

Got a new issue tho:

Code: Select all

$query = "SELECT * FROM (SELECT * FROM tickets WHERE dateupdated IS NULL AND dateclosed IS NULL AND datetrashed IS NULL AND repliedto IS NULL ORDER BY id DESC) AS ticket GROUP BY ticket";
This goes back to the subject matter completely - There will be one row where the "repliedto" field has nothing in it, but if there are replies, there there will be more rows with the same ticket number, but where replied to is also NULL..... because there are in fact replies, only that open question has the "repliedto" field populated.... so I can spot that one row.

Problem is, I am trying to find each "ticket/thread" where there are no replies. So there will in effect be just ONE of each ticket number in the system.

I need to find this via the MySQL, rather than a CountRows function after.

Code: Select all

SELECT COUNT(*) AS id FROM table
I thought this might help, but not sure how to say "where COUNT = 1".

Code: Select all

 $query = "SELECT count(*) as COUNT * FROM (SELECT * FROM tickets WHERE dateupdated IS NULL AND dateclosed IS NULL AND datetrashed IS NULL AND repliedto IS NULL ORDER BY id DESC) AS ticket GROUP BY ticket";
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 »

I kind of work that one out, but I cannot do it for an individual:

Code: Select all

    $query = "SELECT * FROM (SELECT userid, dateclosed, ticket, dateupdated, MAX(id) AS id FROM tickets WHERE userid = '13' AND dateupdated IS NULL AND dateclosed IS NULL AND datetrashed IS NULL AND repliedto IS NULL) AS tickets GROUP BY ticket";
This is showing nothing. Yet if I do this in phpmyadmin, it shows only ONE entry, but not the entry for which there is just one item.
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 »

Code: Select all

SELECT * FROM (SELECT MAX(id) as id, userid, ticket, dateclosed, dateupdated, datetrashed, updatedby FROM tickets WHERE dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL AND updatedby = 'customer' ORDER BY id DESC) AS ticket GROUP BY ticket
I'm now trying to find the last row, but only if it was "updatedby" 'customer'.
The query above looks fine, but it just finds the last one that "was" updatedby customer. I Was to find it ONLY IF it was updatedby customer.

If the last row is 10, and updatedby was "customer", I want to show it. But if row 9 was updatedby customer, and row 9 was updatedby admin, the code above finds customer incorrectly.
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 »

This is from another thread but is more important to this one.

Why is this code producing nothing, when i can see the last row in the database with id 97, has 'customer' in the updated by field.

ticket 16115

95 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'customer'.
96 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'admin'.
97 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'customer'.

Yes it's producing nothing at all. surely it should be producing it, based on row 97?!

Even this produces nothing, and I am giving it the answer of the ticket!!

Code: Select all

SELECT MAX(id) AS id, ticket, subject, dateupdated, updatedby, datetrashed, dateclosed
FROM tickets
WHERE datetrashed IS NULL
AND ticket = '16115'
AND dateclosed IS NULL
AND dateupdated IS NOT NULL
GROUP BY ticket
HAVING updatedby = 'customer'
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 »

Ok this:

Code: Select all

SELECT MAX(id) AS id, ticket, subject, dateupdated, updatedby, datetrashed, dateclosed
FROM tickets
WHERE datetrashed IS NULL
AND dateclosed IS NULL
AND dateupdated IS NOT NULL
GROUP BY ticket
HAVING updatedby = 'customer'
Now extracts a group of data, but it's taking it from two rows.
The last row is being extracted, but the last row doesn't have "customer" in updatedby, it has "admin" in there.
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 »

Here's a good query then - I need to show only "ongoing" tickets in one screen.
The only difference between ongoing ticket, and new tickets is that the first in the row of ticketnumbers will have "repliedto" field with a "yes" in it if it is replied.

For ongoing tickets it needs to show in the GROUP the last entry of that group, but only if the first entry has repliedto = 'yes'.

How the heck do you do that - echo only the last entry of the group, while checking the first entry of the group??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

I hate to interrupt your conversation with yourself! :)
simonmlewis wrote:Here's a good query then - I need to show only "ongoing" tickets in one screen.
The only difference between ongoing ticket, and new tickets is that the first in the row of ticketnumbers will have "repliedto" field with a "yes" in it if it is replied
So this is SELECT ticketumbers WHERE repliedto='yes'.
simonmlewis wrote:For ongoing tickets it needs to show in the GROUP the last entry of that group, but only if the first entry has repliedto = 'yes'.

How the heck do you do that - echo only the last entry of the group, while checking the first entry of the group??
When you say "last entry" what does that mean? By last response date? If so then: SELECT ticketumbers WHERE repliedto='yes' ORDER BY replydate DESC LIMIT 1
(#10850)
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 »

The won't work. If there are three entries, only the one with the lowest ID will have replied to = yes.
I was the last row of the group echoed, based on the first row's "replied to" field.
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 »

So a subquery to get the tickets where repliedto = 'yes', then use that as your IN condition in your main query.

Something like this, maybe?

Code: Select all

SELECT MAX(id), foo, bar
FROM tickets
WHERE ticket IN (
	SELECT ticket FROM tickets WHERE repliedto = 'yes'
)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

simonmlewis wrote:The won't work. If there are three entries, only the one with the lowest ID will have replied to = yes.
So I might ask: Why? It is one thing to find nice queries, but it usually easier to generate easily queryable data.
(#10850)
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 »

I need to do a count (for page numbering) of this:

Code: Select all

SELECT COUNT(id) AS numrows FROM (SELECT * FROM tickets WHERE website = '$website' AND dateclosed IS NOT NULL AND datetrashed IS NULL) AS tickets GROUP BY ticket
It's coming back as empty, yet the initial code at the top that generates all the tickets:

Code: Select all

 $query = "SELECT * FROM (SELECT * FROM tickets WHERE website =:website AND dateclosed IS NOT NULL AND datetrashed IS NULL ORDER BY id DESC) AS ticket GROUP BY ticket LIMIT $offset, $rowsPerPage";
...has all of them.

Where am I going wrong with my count?
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 »

Code: Select all

SELECT * FROM (SELECT * FROM tickets WHERE website =:website AND dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL ORDER BY id DESC) AS tickets GROUP BY ticket
This shows 221 results (correctly).

Code: Select all

SELECT * FROM (SELECT ticket, count(id) AS numrows FROM tickets WHERE website = '$website' AND dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL) AS tickets GROUP BY ticket
This shows 661 as numrows.

Why is the second one wrong?
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 »

I now need to show from this database, tickets where the latest "dateupdated" is over 24 hours old.
But I need to show that latest entry.

In otherwords, I need to run a report of tickets that we have not responded to "updatedby = customer" over 24 hours.

I kinda thought this would do it:

Code: Select all

$query = "
SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT id FROM tickets WHERE dateclosed IS NULL AND datetrashed IS NULL ORDER BY dateupdated DESC) s
JOIN tickets t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'customer'
AND age_hours > 24";
But it shows only the first entry of each group it finds.

So if there were three entries for the ticket number, it shows the first. I need it to show the third entry (the final one).
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 »

Code: Select all

$query = "
SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT id FROM tickets WHERE dateclosed IS NULL AND datetrashed IS NULL ORDER BY dateupdated DESC) s
JOIN tickets t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'customer'
AND age_hours > 48 ORDER BY $order";
This is meant to find anything over 48 hours old, where the last entry was by "CUSTOMER". But it's wrong. Some of them were last updated by "SUPPORT".
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply