group by and order with a join

bike5

I know this has been asked a bunch but I still can't find a solution that works for me. Maybe with an answer here it will make more sense what is going on.

Tables:
tbl1: id, name
tbl2: id, tbl1_id, title, votes

I want to retrieve 1 record for each record in tbl1 with tbl2's highest value in "votes".

This is what I want but I know it doesn't work the way I am intended it to:

SELECT tbl1., tbl2. FROM tbl1 LEFT JOIN tbl2 ON tbl2.tbl1_id = tbl1.id GROUP BY tbl1.id ORDER BY tbl2.votes DESC

Display Page ex:
name, title, votes
name, title, votes
name, title, votes
name, title, votes

Any help would be great thank you.

bradgrafelman

I don't have a MySQL server handy to test, but I'd say you just need to SELECT the right data:

SELECT tbl1.name, tbl2.title, MAX(tbl2.votes) AS votes ....

bike5

bradgrafelman: That pulls the correct votes but the wrong data in the other columns. If my data in tbl2 looks like

id, tbl1_id, title, votes
1,1,title 1,4
2,1,title 2,2
3,1,title 3,7

The result the query gives my using "MAX" is

1,1,title1,7

Res

Not the cleanest solution, but certainly the easiest, would be to run the query as a union of the two tables rather then a join.

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

http://dev.mysql.com/doc/refman/5.0/en/union.html

Assuming both tables are the same structure, which is the impression I got from your post.

Res

I some how garbled the link and can't edit my post, sorry.
http://dev.mysql.com/doc/refman/5.0/en/union.html

bike5

Res;10947875 wrote:
Not the cleanest solution, but certainly the easiest, would be to run the query as a union of the two tables rather then a join.

Assuming both tables are the same structure, which is the impression I got from your post.

Both tables are not the same structure. One holds organizations and the other holds multiple records for each organization with a vote number. Within each record (on the non-org. table) there is a number of votes columns and I need to get the top voted record for each organization. Getting the top records all together is easy but grabbing just the top one per organization is where I am stuck at.

Thank you

Res

Gotcha. Maybe something like this?

SELECT `tbl1`.`org_name`,`tbl1`.`org_id`, `tbl2`.* INNER JOIN `tbl1` ON `tbl1`.`org_id` = `tbl2`.`org_id` GROUP BY `tbl1`.`org_name` ORDER BY `tbl2`.`votes`
[code]

Let me know if that doesn't get you any closer, I'm not in a place that I can test against a mysql server right now.

Lars_Berg

select *
  from tbl1 t1
  join tbl2 t2
    on t2.tbl1_id = t1.id
 where t2.votes =
     (select max(votes)
        from tbl2
       where tbl2.tbl1_id = t1.id)

bike5

Res;10947889 wrote:

Gotcha. Maybe something like this?

SELECT `tbl1`.`org_name`,`tbl1`.`org_id`, `tbl2`.* INNER JOIN `tbl1` ON `tbl1`.`org_id` = `tbl2`.`org_id` GROUP BY `tbl1`.`org_name` ORDER BY `tbl2`.`votes`
[code]

Let me know if that doesn't get you any closer, I'm not in a place that I can test against a mysql server right now.[/QUOTE]

That does not solve the issue, that is almost what I originally had except mine was a Left join. That returns the top regardless of organization.


EDIT: [B]Sorry I meant it returns 1 for each org but not the record with the highest votes because it executes group by first, then orders by those results.[/B]

bike5

Lars Berg;10947912 wrote:

select *
  from tbl1 t1
  join tbl2 t2
    on t2.tbl1_id = t1.id
 where t2.votes =
     (select max(votes)
        from tbl2
       where tbl2.tbl1_id = t1.id)

I tried that setup but it was duplicating a couple orgs that had ideas with the same number of votes that happened to also be the MAX. So if 1 org had 2 rows in tbl2 that had the same number of votes and they were also the highest numbers for that org it would bring back both records.