Need help with complex MySQL query... any takers?

Sam · 2008-07-02 21:49:09

Hey,

This is either not possible, or my brain is just malfunctioning today. Here is the situation:

I have a table with 3 fields: id, name and value. id is an incrementing primary key, name, and value are text.

An example of the data in my table looks like so:

id name value
1 monkey banana
2 monkey apple
3 lion meat
4 snake mouse
5 snake insect

I want to be able to display only 1 of each of the name fields, the newest one, the results would look like so:

2 monkey apple
3 lion meat
5 snake insect

That is 1 of each of the name fields and the latest one that was added to the table in the database.

Struggling to find a solution in SQL, possible in PHP? I’m stuck. Been staring at it for too long perhaps. Help appreciated greatly!

Bloke · 2008-07-02 22:03:58

Hmmm, if you ORDER BY id desc and GROUP BY name, does that work? Not quite sure how group by does its stuff so I might be way off.

EDIT: failing that, you could perhaps grab the lot (ordered by id desc) then iterate over each row and just keep an output array indexed by name. The pseudocode might be:

$out = array();
foreach row {
  if (!array_key_exists(row[name])) {
    out[row[name]] = array(row[id], row[val]);
  }
}

EDIT2: MattD’s solution sounds more plausible. And quicker :-)

Last edited by Bloke (2008-07-02 22:09:59)

MattD · 2008-07-02 22:06:50

select max(id), name, value from table group by name

I think this would do what you need but I’m more familiar with oracle and not sure if it would work the same in mysql.

EDIT: Not sure if you can group by only name.

Edit again: Looks like you can’t use max in mysql…. hmmm….

Last edited by MattD (2008-07-02 22:19:14)

Sam · 2008-07-02 22:21:06

Thanks guys. MattD’s solution is what I was trying previously and it never quite worked.

Bloke, I might give your PHP option a go but surely this could simply be done in SQL.. somehow.

SELECT * FROM table GROUP BY name

This provides the correct result but in the wrong order, earliest entry first, why can’t the order be switched? Argh…

MattD · 2008-07-02 22:22:36

Ok, forget what I said above. This does work

SELECT max( id ) , name, value FROM table GROUP BY name

max( id ) name value
3 lion meat
2 monkey banana
5 snake insect

MattD · 2008-07-02 22:24:02

Mine works directly in phpadmin.

Bloke · 2008-07-02 22:24:53

This concurs with MattD

MattD · 2008-07-02 22:26:49

Funny, I was on the same site after I thought max wasn’t working.

Sam · 2008-07-02 22:29:46

Hi Matt,

This is as far as I got, but those results are incorrect. The id is correct as is the name, but the value is wrong.

The value of id 2 monkey should be apple not banana.

If you populate the table a bit more this become even more apparent. Really odd!

MattD · 2008-07-02 22:41:50

I was just noticing that. Looking into it.

MattD · 2008-07-02 22:54:04

This is how i would do it in Oracle but it doesn’t look like you can do it the same way in mysql.
select a.id, a.name, a.value from table a, (select max(id), name from table group by name) b where a.id = b.id

or

SELECT t1.id, t1.name, t1.value FROM table AS t1, (SELECT max( id ) , name FROM table GROUP BY name) AS t2 WHERE t1.id = t2.id

Sam · 2008-07-02 22:55:34

Thanks for looking into it Matt, appreciate the effort. Will keep looking for a solution in the mean time.

Thinking grabbing all of the database rows and then sorting it via PHP might be the way to go!?

Last edited by Sam (2008-07-02 22:55:54)

Textpattern CMS

Textpattern CMS support forum

#1 2008-07-02 21:49:09

Need help with complex MySQL query... any takers?

#2 2008-07-02 22:03:58

Re: Need help with complex MySQL query... any takers?

#3 2008-07-02 22:06:50

Re: Need help with complex MySQL query... any takers?

#4 2008-07-02 22:21:06

Re: Need help with complex MySQL query... any takers?

#5 2008-07-02 22:22:36

Re: Need help with complex MySQL query... any takers?

#6 2008-07-02 22:24:02

Re: Need help with complex MySQL query... any takers?

#7 2008-07-02 22:24:53

Re: Need help with complex MySQL query... any takers?

#8 2008-07-02 22:26:49

Re: Need help with complex MySQL query... any takers?

#9 2008-07-02 22:29:46

Re: Need help with complex MySQL query... any takers?

#10 2008-07-02 22:41:50

Re: Need help with complex MySQL query... any takers?

#11 2008-07-02 22:54:04

Re: Need help with complex MySQL query... any takers?

#12 2008-07-02 22:55:34

Re: Need help with complex MySQL query... any takers?

Board footer