Professional Web Applications Themes

Difficult query - MySQL

Hello, First, excuses for my bad english. I have a table: mysql> select * from test; +----+---------+---------+------+ | id | column1 | column2 | text | +----+---------+---------+------+ | 1 | 1 | 1 | qwe | | 2 | 2 | 1 | asd | | 3 | 3 | 1 | zxc | | 4 | 4 | 1 | zaq | | 5 | 1 | 2 | xsw | | 6 | 2 | 2 | cde | | 7 | 3 | 2 | ewq | | 8 | 4 | 2 | dsa | | ...

  1. #1

    Default Difficult query

    Hello,

    First, excuses for my bad english.

    I have a table:

    mysql> select * from test;
    +----+---------+---------+------+
    | id | column1 | column2 | text |
    +----+---------+---------+------+
    | 1 | 1 | 1 | qwe |
    | 2 | 2 | 1 | asd |
    | 3 | 3 | 1 | zxc |
    | 4 | 4 | 1 | zaq |
    | 5 | 1 | 2 | xsw |
    | 6 | 2 | 2 | cde |
    | 7 | 3 | 2 | ewq |
    | 8 | 4 | 2 | dsa |
    | 9 | 1 | 3 | cxz |
    | 10 | 2 | 3 | esz |
    | 11 | 3 | 3 | csq |
    | 12 | 4 | 2 | zse |
    +----+---------+---------+------+

    I want to select all the values from column1 one time and the maximum
    value of column2 that correspond with that and also the value of the
    column text that is on the same row.

    I have the next solution but it isn't very nice:

    mysql> CREATE TABLE tmp SELECT column1, MAX(column2) AS column2 FROM
    test GROUP BY column1;

    mysql> select test.column1, test.column2, text FROM test, tmp WHERE
    test.column1 = tmp.column1 AND test.column2 = tmp.column2;
    +---------+---------+------+
    | column1 | column2 | text |
    +---------+---------+------+
    | 1 | 3 | cxz |
    | 2 | 3 | esz |
    | 3 | 3 | csq |
    | 4 | 2 | dsa |
    | 4 | 2 | zse |
    +---------+---------+------+

    mysql> DROP table tmp;

    Does anybody know a nicer way to that? I tried joins and a lot of
    other things but this is the only way I got it working.

    Again, excuses for my bad engisch (I'm from holland)

    --
    EeBie

    eebieeebie@hotmail.com Guest

  2. #2

    Default Re: Difficult query

    On Apr 19, 10:52 am, com wrote: 

    Your requirement says: "I want to select all the values from column1
    one time", but your sample output has two entries for the column1
    value "4". These contradict each other. Which do you actually want to
    see?

    Captain Guest

  3. #3

    Default Re: Difficult query

    On 19 apr, 12:01, Captain Paralytic <com> wrote: 

    You are right, I said it wrong. I only want the values where column2
    is maximum , most of the time that gives one value but not all the
    time.

    --
    EeBie

    eebieeebie@hotmail.com Guest

  4. #4

    Default Re: Difficult query

    On Apr 19, 11:01 am, Captain Paralytic <com> wrote: 











    >
    > Your requirement says: "I want to select all the values from column1
    > one time", but your sample output has two entries for the column1
    > value "4". These contradict each other. Which do you actually want to
    > see?[/ref]

    I'm assuming EeBie wants to see all results holding the max value for
    each group:

    SELECT t1. *
    FROM test t1
    LEFT JOIN test t2 ON t2.column1 = t1.column1
    AND t1.column2 < t2.column2
    WHERE t2.column2 IS NULL


    BTW EeBie, don't apologise. Your English is almost perfect:

    correspond -> corresponds
    next -> following

    strawberry Guest

  5. #5

    Default Re: Difficult query

    On 19 apr, 12:29, strawberry <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > I'm assuming EeBie wants to see all results holding the max value for
    > each group:
    >
    > SELECT t1. *
    > FROM test t1
    > LEFT JOIN test t2 ON t2.column1 = t1.column1
    > AND t1.column2 < t2.column2
    > WHERE t2.column2 IS NULL[/ref]

    That works! I don't know exactly how but I try to figure it out.
    Thank you.
     

    :-)

    --
    EeBie

    eebieeebie@hotmail.com Guest

Similar Threads

  1. Using MS Access, is it difficult to query twodatasources?
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: November 15th, 02:02 AM
  2. is it difficult ?
    By diramridir webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 2
    Last Post: September 16th, 08:47 AM
  3. This can't be that difficult
    By Julian Roberts in forum Macromedia Dreamweaver
    Replies: 10
    Last Post: July 16th, 04:00 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