Professional Web Applications Themes

by occurence - MySQL

Hello all if i have a table containing the follwing Id --Value 0 -- 15 1 -- 5 2 -- 15 3 -- 4 4 -- 9 5 -- 1 6 -- 4 how can i obtains easily only values repeated more than only one (where occurence >1) result: Id --Value 0 -- 15 2 -- 15 3 -- 4 6 -- 4 thanks in advance...

  1. #1

    Default by occurence

    Hello all

    if i have a table containing the follwing

    Id --Value
    0 -- 15
    1 -- 5
    2 -- 15
    3 -- 4
    4 -- 9
    5 -- 1
    6 -- 4

    how can i obtains easily only values repeated more than only one
    (where occurence >1)
    result:

    Id --Value
    0 -- 15
    2 -- 15
    3 -- 4
    6 -- 4

    thanks in advance

    upx.brussels@gmail.com Guest

  2. #2

    Default Re: by occurence

    Not 100% sure of this in MySQL ........

    SELECT T1.*
    FROM TEST T1
    JOIN
    (
    SELECT B, COUNT(B) AS NUM
    FROM TEST
    GROUP BY B
    ) T2 ON T1.B = T2.B WHERE T2.NUM > 1


    <com> wrote in message
    news:googlegroups.com... 



    Sean Guest

  3. #3

    Default Re: by occurence

    On Mar 26, 4:59 pm, "Sean" <sean.anderson[nospam]oakleafgroup.biz>
    wrote: 




    > [/ref]

    A subquery is an inefficient way to do this. In the particular case of
    selecting non-unique values the correct way would be like this:

    SELECT t1 . *
    FROM `test1` t1
    LEFT JOIN `test1` t2 ON t1.value = t2.value
    AND t1.id <> t2.id
    WHERE t2.value IS NOT NULL
    ORDER BY t1.value DESC ,t1. id;

    For a more general case, in which you want to select where greater
    than 2 or greater than 3, the answer posted on the mysql forum (http://
    forums.mysql.com/list.php?10) is more like the way to go.

    strawberry Guest

Similar Threads

  1. How to find second occurence of a string?
    By vkunirs in forum Macromedia ColdFusion
    Replies: 4
    Last Post: April 18th, 03:22 PM
  2. Position of String Occurence
    By Tesla in forum PHP Development
    Replies: 5
    Last Post: February 20th, 12:56 AM
  3. Assigning a date display to the first occurence
    By J. Muenchbourg in forum ASP
    Replies: 1
    Last Post: August 5th, 07:39 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