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 »

It's really not working. It does show those with Customer in them, but not if the oldest of the IDs is customer.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post by mikosiko »

could you post:
- Your tickets table definition
- A set of current data to test and,
- The set of expected results for your query
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 »

Not without revealing private information. I can show the field names, but not the content.

Code: Select all

CREATE TABLE IF NOT EXISTS `tickets` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ticket` int(20) DEFAULT NULL,
  `userid` int(10) DEFAULT NULL,
  `subject` text,
  `message` text,
  `rcnumber` varchar(20) DEFAULT NULL,
  `dateraised` datetime DEFAULT NULL,
  `dateupdated` datetime DEFAULT NULL,
  `dateclosed` datetime DEFAULT NULL,
  `datedeleted` datetime DEFAULT NULL,
  `emailstamp` varchar(50) DEFAULT NULL,
  `website` varchar(50) DEFAULT NULL,
  `updatedby` varchar(20) DEFAULT NULL,
  `repliedto` varchar(5) DEFAULT NULL,
  `datetrashed` datetime DEFAULT NULL,
  `trashedby` varchar(50) DEFAULT NULL,
  `openingquestion` varchar(5) DEFAULT NULL,
  `closedid` varchar(5) DEFAULT NULL,
  `notes` text,
  `attachment` varchar(200) DEFAULT NULL,
  `returnticket` date DEFAULT NULL,
  `latestreply` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2628 ;
An example of what might be there.

Many of these fields are irrelevant to the problem of this query though.
ticket = ticket number, so if there are 1 or more entries in a 'thread', the ticket number per thread remains the same.
updatedby = has either customer, admin or support entered, so we know who submitted what.
dateupdated = each time a message is posted to the thread, dateupdated is given todays date at that time.


So I want to find each thread (of each ticket number) where the latest id of that thread has "updatedby = admin".
The purpose of this is to find those where that latest ticket (latest "ID") had admin in it, and the "dateupdated" is MORE than 7 days old.

If I can get that, I can do others in this style - ie. where something is more than 48 hours (or 2 days, either is fine).

Does this makes sense? MAIN purpose is to close tickets where admin or support have replied and the customer hasn't, so we closed that ticket number.
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 »

simonmlewis wrote:So I want to find each thread (of each ticket number)
WHERE ticket=$ticket
simonmlewis wrote:has "updatedby = admin".
AND updatedby = 'admin'
simonmlewis wrote:and the "dateupdated" is MORE than 7 days old.
AND dateupdated < $date_in_past
simonmlewis wrote:where the latest id of that thread
ORDER BY id 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 »

$date_in_past?
If you have this:
ID Ticket Date Updated Updatedby
1 123 05/07/2014 customer
3 123 05/07/2014 admin
7 123 06/07/2014 customer
8 123 06/07/2014 admin
9 123 06/07/2014 customer

I want to show row 9, because of this group of ticket numbers, it's last one has "customer" as 'updatedby', and it's over 7 days old.
How do I ask it WHERE dateupdated = (add 7 days to dateupdated) ?

The problem I always have here, is that row 7 would be included in this, but I need it to find row 9, as that was the "final one" in the group of that 'ticket'.
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 »

simonmlewis wrote:I want to show row 9, because of this group of ticket numbers, it's last one has "customer" as 'updatedby', and it's over 7 days old.
How do I ask it WHERE dateupdated = (add 7 days to dateupdated) ?
You can calculate a date in the past with either PHP or SQL -- see the docs.
simonmlewis wrote:The problem I always have here, is that row 7 would be included in this, but I need it to find row 9, as that was the "final one" in the group of that 'ticket'.
AND (updatedby = 'admin' OR updatedby = 'customer')

.... but you said only 'admin' above, so if that is not really a constraint then you may be able to remove this completely. But you need to be clear on what you actually want.
(#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'm not sure how I can be clearer.

Of that group, I want to know if the final one has updatedby = 'admin'.
I don't think "WHERE updatedby = 'admin'" would work, but it wouldn't necessarily show the final one.

So to be clear: I want to know those that are 7 days (or more) old, where the final thread in the ticket is 'updatedby = 'admin''.
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 »

So why not just fetch "... WHERE ticket=$ticket ORDER BY id DESC LIMIT 1" and check the value of $row['Updatedby'] ?
(#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 »

Because after WHILE... I have a lot of queries - and I want that query to be based on one factor - the fact that the row extracted was in fact last updated by ..... on..... !!
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 »

simonmlewis wrote:Because after WHILE... I have a lot of queries - and I want that query to be based on one factor - the fact that the row extracted was in fact last updated by ..... on..... !!
I am not clear about what "last updated by ..... on..... !!" means. If you just want the last updated then it would be "... WHERE ticket=$ticket ORDER BY `Date Updated` DESC LIMIT 1". Then it would be "last updated by {$row[' Updatedby']} on {$row['Date Updated']} !!"
(#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 want to show the last "updatedby" but only where the final row in each "group", as laid out above, is "support".

So if you have 5 rows for a ticket number, and the final row has "updatedby" = "customer", I DON'T want that to be found.
But if it was "support", I DO want it found.

If I just said "where updatedby = 'support' ORDER BY ID DESC LIMIT 1, then it would show the one where "customer" was the last one, but would "find" the "support" entry before it.


id ticket updatedby
1 111 customer
2 111 support
3 111 customer

id ticket updatedby
6 444 customer
7 444 support
8 444 customer
8 444 support

If you ran the query that I want..... It would find ticket 444 as a group, but NOT ticket 111.
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 »

Remind me again why you can't use a subquery to order by date (or id), group the main query by ticket number, and check 'updatedby' when you're iterating over the results?
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 »

You are all over the place with your description of this. If you want to select things by each 'ticket' then use "GROUP BY ticket". If you want the only the greatest item then use MAX(`Date Updated`). These kinds of queries are not something that one can surmise from afar. You have access to the dataset and the server. Try queries in a logical progression until you constrain to the results to what you want.
(#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 »

Sorry about that, but I thought i was completely clear. I've explained the "groups" twice.

I want to do a GROUP BY ticket, but only where the latest ticket in the group, is "updatedby = 'support'.
Is that not clear?
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 agree with Celauran.
(#10850)
Post Reply