Professional Web Applications Themes

Query to find a missing number - MySQL

Hello, I need to write a query to find out a set of missing number in a given sequence. Eg : a Column in some table has the following data Col1 1 2 3 4 5 6 8 9 10 Here I need to write a query to find out continued...

  1. #1

    Default Query to find a missing number



    Hello,



    I need to write a query to find out a set of missing number in a given
    sequence.



    Eg : a Column in some table has the following data



    Col1

    1

    2

    3

    4

    5

    6

    8

    9

    10



    Here I need to write a query to find out that number 7 is missing in the
    given sequence.

    One possible solution is by using any loop. But I am looking out if the same
    can be achieved using any query.



    Thanks in advance.



    Regards,

    Mahesh











    Mahesh BS Guest

  2. #2

    Default Re: Query to find a missing number

    Mahesh BS wrote:
    > Hello,
    >
    >
    >
    > I need to write a query to find out a set of missing number in a given
    > sequence.
    >
    >
    >
    > Eg : a Column in some table has the following data
    >
    >
    >
    > Col1
    > 1
    > 2
    > 3
    > 4
    > 5
    > 6
    > 8
    > 9
    > 10
    >
    >
    >
    > Here I need to write a query to find out that number 7 is missing in the
    > given sequence.
    >
    > One possible solution is by using any loop. But I am looking out if the same
    > can be achieved using any query.
    >
    If you need to do this more than once, or if you have several missing numbers to find
    in a large dataset, here's the method:
    Create a table that contains a sequence, and use a LEFT JOIN to find the missing one.

    To create a table and fill it with numbers quickly, use the method described in this article:
    [url]http://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html[/url]

    Then, you can issue a query like this:

    SELECT
    some_column
    FROM
    some_table
    LEFT JOIN sequence_table ON some_table.col_id = sequence_table.col_id
    WHERE
    sequence_table.col_id IS NULL;

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

Similar Threads

  1. Query of Query Missing Some Data
    By g1zm0guy in forum Coldfusion Database Access
    Replies: 7
    Last Post: January 17th, 09:30 AM
  2. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  3. find row number of specific record in another query
    By speedyG in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: March 4th, 02:09 PM
  4. Find localhost’s IP number
    By steve in forum PHP Development
    Replies: 7
    Last Post: July 27th, 12:38 AM
  5. where do I find the version number?
    By remote_luxury in forum Macromedia Freehand
    Replies: 3
    Last Post: April 20th, 02:34 AM

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