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 »

So do I, but none of these queries I have tried, work.
the one I posted days ago, does not show me those only where the last entry has "updatedby = 'support'".

I keep referring back to this. Happy to use a subquery as that will work with a Group, but it's not working - it's bring back those where updatedby - "customer" as well!
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 you can then filter out in PHP. You're spending a ton of time trying to find the perfect query when a working solution was proposed weeks ago.
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 »

But what you are suggesting is that I write a query, that churns through 100s and soon 1000s of entries, and queries each entry based on updatedby.
Rather than filtering out what I don't want, and just - within the query - find what I DO want.

Surely that's better?

Come on, what is the actual answer you are suggesting here?
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 »

Maybe if you post:
- the actual query that "is not working" (the complete query please)
- some example of your test data (don't need to be your real data... just good examples to test your query)
- The expected results that you are after based on the test data. and finally,
- What are you getting from your query after run it over the test data.

Maybe then the people that is trying to help you could make sense of all your previous posts, and be able to provide you a solution.

just my 0.02 cents.
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 »

simonmlewis wrote:But what you are suggesting is that I write a query, that churns through 100s and soon 1000s of entries, and queries each entry based on updatedby.
Not really, no. There's one query returning one set of results, the display of which is determined by a simple conditional in PHP. It shouldn't be an expensive operation.
mikosiko wrote:Maybe if you post:
- the actual query that "is not working" (the complete query please)
- some example of your test data (don't need to be your real data... just good examples to test your query)
- The expected results that you are after based on the test data. and finally,
- What are you getting from your query after run it over the test data.

Maybe then the people that is trying to help you could make sense of all your previous posts, and be able to provide you a solution.
This would be immensely helpful, 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:But what you are suggesting is that I write a query, that churns through 100s and soon 1000s of entries, and queries each entry based on updatedby.
Rather than filtering out what I don't want, and just - within the query - find what I DO want.
If you did it the way Celauran suggests and do a secondary sort by updatedby, then you would not need to churn through any additional entries -- just stop when updatedby changes.
simonmlewis wrote:Surely that's better?
Was it Voltaire who said the better is the enemy of the good...
simonmlewis wrote:Come on, what is the actual answer you are suggesting here?
I think Celauran is suggesting that if you cannot solve your problem the way you'd like, then solve it the way you can.
(#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 »

Ok - if you would kindly quote me the query I should be using, as so far what I have used doesn't work.
Let's say I want to be able to query all fields in the database table..... and do as I suggested. How would you write it?
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 »

Code: Select all

DROP TABLE IF EXISTS `tickets`;

CREATE TABLE `tickets` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ticket` int(11) unsigned NOT NULL,
  `dateupdated` date NOT NULL,
  `updatedby` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tickets` (`id`, `ticket`, `dateupdated`, `updatedby`)
VALUES
	(1,123,'2014-07-01','customer'),
	(2,123,'2014-07-02','support'),
	(3,123,'2014-07-03','customer'),
	(4,123,'2014-07-04','support'),
	(5,123,'2014-07-05','customer'),
	(6,123,'2014-07-06','support'),
	(7,123,'2014-07-07','customer'),
	(8,123,'2014-07-08','support'),
	(9,234,'2014-07-07','customer'),
	(10,234,'2014-07-07','support'),
	(11,234,'2014-07-08','customer'),
	(12,234,'2014-07-09','support'),
	(13,234,'2014-07-13','customer');

Code: Select all

function getUnanswered(PDO $pdo, $date) {
	$query = "SELECT id, ticket, dateupdated, updatedby
		FROM (
			SELECT id, ticket, dateupdated, updatedby 
			FROM tickets
			ORDER BY id DESC
			) AS a
		GROUP BY ticket
		ORDER BY dateupdated DESC"; 
	$stmt = $pdo->prepare($query);
	$exec = $stmt->execute();

	$tickets = [];
	if ($exec) {
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		foreach ($results as $result) {
			if ($result['updatedby'] == 'support' && $result['dateupdated'] < $date) {
				$tickets[] = $result;
			}
		}
	}

	return $tickets;
}

$pdo = new PDO('mysql:hostname=localhost;dbname=sandbox', 'foo', 'bar');
var_dump(getUnanswered($pdo, date('Y-m-d')));

Code: Select all

array (size=1)
  0 => 
    array (size=4)
      'id' => string '8' (length=1)
      'ticket' => string '123' (length=3)
      'dateupdated' => string '2014-07-08' (length=10)
      'updatedby' => string 'support' (length=7)
Not what you're looking for?
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 »

Do a straight copy paste, it does look to be working, however I don't know how to extract each "$ticket" into a variable, to then query the database.

The point of doing it as a "GROUP BY" is that is also extracts other database I need. This is why I was trying it in a "while".
I've never used "foreach" in a query like that.

So how do do I work it so I can do it as $row->tickets or $row[tickets] ?
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 »

simonmlewis wrote:I don't know how to extract each "$ticket" into a variable, to then query the database.
You shouldn't. If you're hitting the database inside a loop, it's time to go back to the drawing board, work out what information you ultimately need, and how to get that.
simonmlewis wrote:So how do do I work it so I can do it as $row->tickets or $row[tickets] ?
It's just returning an array. Iterate over it as you would any other array.

Code: Select all

$tickets = getUnanswered($pdo, date('Y-m-d'));
foreach ($tickets as $ticket) {
    echo $ticket['dateupdated'];
    etc.
}
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 »

So I can just go:
if ($ticket['dateupdated'] == .................) and so on?
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 »

Absolutely.
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 »

Terrific - leave it with me. I'll come back here with my findings.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply