Professional Web Applications Themes

"group by" - order of rows in group - MySQL

this is simple example: TABLE SCHEMA: create table my_table( id int unsigned not null auto_increment primary key, project varchar(255) not null, title varchar(255) not null ); TABLE DATA: 1 | project_1 | revision_1 2 | project_2 | revision_1 3 | project_1 | revision_2 QUERY: select id,project,title from my_table group by project order by title asc; RESULT: 1 | project_1 | revision_1 2 | project_2 | revision_1 WHAT I WANT: 2 | project_2 | revision_1 3 | project_1 | revision_2 MY PROBLEM: from rows in groups, example for "project_1" group rows: 1 | project_1 | revision_1 3 | project_1 | revision_2 ...

  1. #1

    Default "group by" - order of rows in group

    this is simple example:

    TABLE SCHEMA:
    create table my_table(
    id int unsigned not null auto_increment primary key,
    project varchar(255) not null,
    title varchar(255) not null
    );

    TABLE DATA:
    1 | project_1 | revision_1
    2 | project_2 | revision_1
    3 | project_1 | revision_2

    QUERY:
    select id,project,title from my_table group by project order by title
    asc;

    RESULT:
    1 | project_1 | revision_1
    2 | project_2 | revision_1

    WHAT I WANT:
    2 | project_2 | revision_1
    3 | project_1 | revision_2

    MY PROBLEM:
    from rows in groups, example for "project_1" group rows:
    1 | project_1 | revision_1
    3 | project_1 | revision_2
    i want row with highest id to be part of result.

    i currently achieve this by calling "alter table researches order by id
    desc" before select.

    Aljosa Mohorovic

    aljosa.mohorovic@gmail.com Guest

  2. #2

    Default Re: "group by" - order of rows in group

    [email]aljosa.mohorovic[/email] wrote:
    > this is simple example:
    >
    > TABLE SCHEMA:
    > create table my_table(
    > id int unsigned not null auto_increment primary key,
    > project varchar(255) not null,
    > title varchar(255) not null
    > );
    >
    > TABLE DATA:
    > 1 | project_1 | revision_1
    > 2 | project_2 | revision_1
    > 3 | project_1 | revision_2
    >
    > QUERY:
    > select id,project,title from my_table group by project order by title
    > asc;
    >
    > RESULT:
    > 1 | project_1 | revision_1
    > 2 | project_2 | revision_1
    >
    > WHAT I WANT:
    > 2 | project_2 | revision_1
    > 3 | project_1 | revision_2
    >
    > MY PROBLEM:
    > from rows in groups, example for "project_1" group rows:
    > 1 | project_1 | revision_1
    > 3 | project_1 | revision_2
    > i want row with highest id to be part of result.
    >
    > i currently achieve this by calling "alter table researches order by id
    > desc" before select.
    >
    > Aljosa Mohorovic
    This question gets asked a lot!

    Here's a generic answer.

    SELECT t1 . *
    FROM table1 t1
    LEFT JOIN table1 t2 ON t1.col1 = t2.col1
    AND t1.col2 < t2.col2
    WHERE t2.col2 IS NULL;

    strawberry Guest

Similar Threads

  1. Change settings to "Always Allow" via Group Policy
    By ItIsMillerTime4u in forum Macromedia Flash Player
    Replies: 1
    Last Post: March 16th, 08:19 PM
  2. cfoutput cfquery "group processing" error
    By TimMcGeary in forum Coldfusion Database Access
    Replies: 0
    Last Post: July 18th, 02:40 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. Elements "pasted inside" change position when parent group is re-sized
    By JohnSplash webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 5
    Last Post: October 31st, 07:28 PM
  5. Debian Equivalent of Group "Wheel"?
    By Marc Trudeau in forum Debian
    Replies: 4
    Last Post: July 7th, 11:20 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