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:
Re: How do I group by, but only show the latest in the DB?
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.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
could you post:
- Your tickets table definition
- A set of current data to test and,
- The set of expected results for your query
- 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?
Not without revealing private information. I can show the field names, but not the content.
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.
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 ;
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.
All the best from the United Kingdom.
- 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?
WHERE ticket=$ticketsimonmlewis wrote:So I want to find each thread (of each ticket number)
AND updatedby = 'admin'simonmlewis wrote:has "updatedby = admin".
AND dateupdated < $date_in_pastsimonmlewis wrote:and the "dateupdated" is MORE than 7 days old.
ORDER BY id DESC LIMIT 1simonmlewis wrote:where the latest id of that thread
(#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?
$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'.
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.
All the best from the United Kingdom.
- 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?
You can calculate a date in the past with either PHP or SQL -- see the docs.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) ?
AND (updatedby = 'admin' OR updatedby = 'customer')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'.
.... 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?
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''.
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.
All the best from the United Kingdom.
- 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?
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?
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.
All the best from the United Kingdom.
- 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?
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']} !!"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..... !!
(#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?
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.
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.
All the best from the United Kingdom.
Re: How do I group by, but only show the latest in the DB?
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?
- 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?
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?
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?
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.
All the best from the United Kingdom.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US