SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 10:04 AM
| ||||
| ||||
 sorting on grouped results? Hello group, I trying to get the following result from 2 tables (one to many relationship). I've been using group by and subqueries, but haven't figured out yet how to do the following: Table a: id (int, pkey) | name (varchar) 2 | fruits 3 | veggies Tabe b: id (int, pkey) | a_id (int) | name (varchar) | created (timestamp) | relevance (int) 5 | 2 | 'banana' | '2007-06-15 12:00:00' | 1 6 | 2 | 'apple' | '2007-06-16 12:00:00' | 2 7 | 3 | 'potato' | '2007-06-17 12:00:00' | 1 8 | 2 | 'peach' | '2007-06-17 12:00:00' | 1 What I want is to list distinct/grouped rows (id) from table a joined with table b (a.id = b.a_id). But I want the one row per unique a.id returned from a joined with b with the highest relevance and most recent created date (ORDER BY relevance DESC, created DESC) from table b for example: a.id | a.name | b.id | b.a_id | b.name | b.created | b.relevance 2 | fruits | 6 | 2 | 'apple' | '2007-06-16 12:00:00' | 2 3 | veggies | 7 | 3 | 'potato' | '2007-06-17 12:00:00' | 1 I hope this makes sense. Any idea how I would do this? Appreciate any help or ideas. Greg      |
|
02-28-2008, 10:04 AM
| |||
| |||
| Re: sorting on grouped results? On 20 Jun, 23:50, Gregory Stewart <gstewart...@gmail.com> wrote: > Hello group, > > I trying to get the following result from 2 tables (one to many > relationship). I've been using group by and subqueries, but haven't > figured out yet how to do the following: > > Table a: > id (int, pkey) | name (varchar) > > 2 | fruits > 3 | veggies > > Tabe b: > id (int, pkey) | a_id (int) | name (varchar) | created (timestamp) | > relevance (int) > > 5 | 2 | 'banana' | '2007-06-15 12:00:00' | 1 > 6 | 2 | 'apple' | '2007-06-16 12:00:00' | 2 > 7 | 3 | 'potato' | '2007-06-17 12:00:00' | 1 > 8 | 2 | 'peach' | '2007-06-17 12:00:00' | 1 > > What I want is to list distinct/grouped rows (id) from table a joined > with table b (a.id = b.a_id). But I want the one row per unique a.id > returned from a joined with b with the highest relevance and most > recent created date (ORDER BY relevance DESC, created DESC) from table > b > > for example: > > a.id | a.name | b.id | b.a_id | b.name | b.created | b.relevance > 2 | fruits | 6 | 2 | 'apple' | '2007-06-16 12:00:00' | 2 > 3 | veggies | 7 | 3 | 'potato' | '2007-06-17 12:00:00' | 1 > > I hope this makes sense. > > Any idea how I would do this? Appreciate any help or ideas. > > Greg Search the group for "Strawberry Query" it does exactly what you need. |
|
02-28-2008, 10:04 AM
| ||||
| ||||
| Re: sorting on grouped results? On Jun 21, 3:28 am, Captain Paralytic <paul_laut...@yahoo.com> wrote: > On 20 Jun, 23:50, Gregory Stewart <gstewart...@gmail.com> wrote: > > > > > Hello group, > > > I trying to get the following result from 2 tables (one to many > > relationship). I've been using group by and subqueries, but haven't > > figured out yet how to do the following: > > > Table a: > > id (int, pkey) | name (varchar) > > > 2 | fruits > > 3 | veggies > > > Tabe b: > > id (int, pkey) | a_id (int) | name (varchar) | created (timestamp) | > > relevance (int) > > > 5 | 2 | 'banana' | '2007-06-15 12:00:00' | 1 > > 6 | 2 | 'apple' | '2007-06-16 12:00:00' | 2 > > 7 | 3 | 'potato' | '2007-06-17 12:00:00' | 1 > > 8 | 2 | 'peach' | '2007-06-17 12:00:00' | 1 > > > What I want is to list distinct/grouped rows (id) from table a joined > > with table b (a.id = b.a_id). But I want the one row per unique a.id > > returned from a joined with b with the highest relevance and most > > recent created date (ORDER BY relevance DESC, created DESC) from table > > b > > > for example: > > > a.id | a.name | b.id | b.a_id | b.name | b.created | b.relevance > > 2 | fruits | 6 | 2 | 'apple' | '2007-06-16 12:00:00' | 2 > > 3 | veggies | 7 | 3 | 'potato' | '2007-06-17 12:00:00' | 1 > > > I hope this makes sense. > > > Any idea how I would do this? Appreciate any help or ideas. > > > Greg > > Search the group for "Strawberry Query" it does exactly what you need. Thanks for the hint. I got it all working now with the 'Strawberry Query'. Greg |
Linear Mode
