Professional Web Applications Themes

sorting on grouped results? - MySQL

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' | ...

  1. #1

    Default 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

    Gregory Guest

  2. #2

    Default Re: sorting on grouped results?

    On 20 Jun, 23:50, Gregory Stewart <com> wrote: 

    Search the group for "Strawberry Query" it does exactly what you need.

    Captain Guest

  3. #3

    Default Re: sorting on grouped results?

    On Jun 21, 3:28 am, Captain Paralytic <com> wrote: 











    >
    > Search the group for "Strawberry Query" it does exactly what you need.[/ref]

    Thanks for the hint. I got it all working now with the 'Strawberry
    Query'.

    Greg

    Gregory Guest

Similar Threads

  1. Help with a Grouped Search Results Page...
    By The Ox in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 9th, 02:23 PM
  2. Sorting Database Results in PHP
    By TheRapidGroup in forum Dreamweaver AppDev
    Replies: 4
    Last Post: February 27th, 01:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139