Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2010
    Posts
    18

    Question Unanswered: Find values that are not in sequence

    Hello,

    How to find values that are not in sequence ?

    A
    B
    C
    D
    Z
    E
    F
    G

    or something like this:

    1
    2
    3
    8
    4
    5
    6

    Each of the highlighted values is not in the proper sequence, how to find those ?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    like i mentioned on devshed, you have to give more information

    please show the actual query that produces these results

    as you know, the rows of a table do not have an inherent order, and especially not a table consisting of only one column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by r937 View Post
    like i mentioned on devshed, you have to give more information

    please show the actual query that produces these results

    as you know, the rows of a table do not have an inherent order, and especially not a table consisting of only one column
    Hello r937,

    Actually I don't have any working query, i was wondering that if its possible through MySQL ?
    Thanks for ur reply.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It may be possible, depending on the problem. We may be able to answer your question once you provide more detail, but as you've asked the question so far the answer is "maybe".

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The bigger question is why would the sequence matter, except when you display
    Dave

  6. #6
    Join Date
    Mar 2010
    Posts
    18
    Hello,

    Please refer the example:

    Table

    Andy Xyz
    Bruce Xyz
    Collinwood Xyz
    Dragon Xyz
    Zypher Xyz
    Elli Xyz
    Frank Xyz

    As u can see, the name Zypher is not in alphabetical series.
    So, can it be sorted out by using MySql query ?

    Thanks

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is all you are asking is can you get rows ordered into a specific order?
    if so the answer is yes... place on ORDER BY clause at the end of the select statement

    if you have another sequence and you want to finds rows out of sequence then thats a different and much more complex task
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by healdem View Post
    is all you are asking is can you get rows ordered into a specific order?
    if so the answer is yes... place on ORDER BY clause at the end of the select statement

    if you have another sequence and you want to finds rows out of sequence then thats a different and much more complex task
    Hii Healdem.
    Thanks for replying

    Yaa, I am trying to finds rows which are out of sequence.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    like i said, rows will be placed in table according to space and clustering, what difference does it make how they are put into table? and why would you need to find them? Allow the database to store the data according to its needs and you can query/order the data according to your needs.
    Dave

  10. #10
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by dav1mo View Post
    like i said, rows will be placed in table according to space and clustering, what difference does it make how they are put into table? and why would you need to find them? Allow the database to store the data according to its needs and you can query/order the data according to your needs.
    Dave
    Hii Dave,

    The records which are alphabetically arranged are like tel. directory structure for different cities.

    So it cannot be sorted in Asc/Dsc order by name, because all the cities will get merged.

    keeping the record structure intact, i need to find the "out-of-sequence" records.

    Thanks.
    Last edited by andy982183; 04-01-10 at 08:10.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andy982183 View Post
    Yaa, I am trying to finds rows which are out of sequence.
    good news!!! your quest is over!!!

    rows in a table have no sequence (i told you this days ago)

    you may now move on to your next problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can you identisy how a row will be out of sequence
    and what cause that out of sequence condition, as others have said the way data is stored in a database is out of your control and you cannot rely on the db storing data in a specific sequence.

    thats why the designers of SQL implemented the ORDER BY clause.
    read da manuel as to what sort of (pardon the deliberate pun) order you want yoru rows to come in.

    unless you have a really really flaky design and obtuse data source then ORDER BY should be able to do what you want

    a footnote, as others have said, this forum isn't really for learning SQL from the roots, its about helping. if you are an absolute beginner then there are plenty of other sites such as w3schools. heck theres even some good books on SQL... You could always buy a copy of Rudy's (R937) book simply SQL.. in all good signatures on this forum.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Mar 2004
    Posts
    480
    The records which are alphabetically arranged are like tel. directory structure for different cities.

    So it cannot be sorted in Asc/Dsc order by name, because all the cities will get merged.
    MORE GOOD NEWS .... you can have more than one field in your ORDER BY clause so use
    ORDER BY
    city,
    last name

Posting Permissions

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