Page 3 of 4
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 11, 2014 6:41 am
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.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 11, 2014 10:54 am
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
Re: How do I group by, but only show the latest in the DB?
Posted: Sun Jul 13, 2014 4:59 pm
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.
Re: How do I group by, but only show the latest in the DB?
Posted: Sun Jul 13, 2014 6:44 pm
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
Re: How do I group by, but only show the latest in the DB?
Posted: Mon Jul 14, 2014 3:34 am
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'.
Re: How do I group by, but only show the latest in the DB?
Posted: Tue Jul 15, 2014 7:57 pm
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.
Re: How do I group by, but only show the latest in the DB?
Posted: Wed Jul 16, 2014 2:56 am
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''.
Re: How do I group by, but only show the latest in the DB?
Posted: Wed Jul 16, 2014 8:59 pm
by Christopher
So why not just fetch "... WHERE ticket=$ticket ORDER BY id DESC LIMIT 1" and check the value of $row['Updatedby'] ?
Re: How do I group by, but only show the latest in the DB?
Posted: Thu Jul 17, 2014 4:21 am
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..... !!
Re: How do I group by, but only show the latest in the DB?
Posted: Thu Jul 17, 2014 2:47 pm
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']} !!"
Re: How do I group by, but only show the latest in the DB?
Posted: Thu Jul 17, 2014 5:04 pm
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.
Re: How do I group by, but only show the latest in the DB?
Posted: Thu Jul 17, 2014 6:57 pm
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?
Re: How do I group by, but only show the latest in the DB?
Posted: Thu Jul 17, 2014 9:27 pm
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.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 2:59 am
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?
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 11:11 am
by Christopher
I agree with Celauran.