Professional Web Applications Themes

Select Records In Order Based on an Ordered ID List - MySQL

Hello! Let's say I have an ordered list of ids that correspond to an ordered list of records I want to retrieve, ie. 32,80,10,90,28,5 What's the most efficient way to retrieve the records corresponding to the ids in the same order as the ids? It seems the IN clause doesn't preserve ordering, and using UNION to submit the same query with different id WHERE clauses seems messy/inefficient. Thanks! Mike...

  1. #1

    Default Select Records In Order Based on an Ordered ID List

    Hello!

    Let's say I have an ordered list of ids that correspond to an ordered
    list of records I want to retrieve, ie. 32,80,10,90,28,5

    What's the most efficient way to retrieve the records corresponding to
    the ids in the same order as the ids? It seems the IN clause doesn't
    preserve ordering, and using UNION to submit the same query with
    different id WHERE clauses seems messy/inefficient.

    Thanks!
    Mike

    Mike Guest

  2. #2

    Default Re: Select Records In Order Based on an Ordered ID List

    >Let's say I have an ordered list of ids that correspond to an ordered 

    There's no ordering in an IN clause to preserve. If you don't ORDER BY,
    you have no complaint about the order you get.
     

    Create a table ordering with columns consisting of the ids and the
    relative ordering.
    id ordering
    32 1
    80 2
    10 3
    90 4
    28 5
    5 6

    Join the table against the table with the
    records. ORDER BY ordering.ordering desc .

    This may not be most efficient, but it shouldn't be too bad.

    Gordon Guest

  3. #3

    Default Re: Select Records In Order Based on an Ordered ID List

    Mike wrote: 

    SELECT myval FROM mytable
    WHERE id IN (32,80,10,90,28,5)
    ORDER BY FIND_IN_SET(id, '32,80,10,90,28,5')

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Selecting Records based on DB column with list
    By craigkaminsky in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 4th, 07:41 PM
  2. Delete mulitple records from list based on checkbox?
    By btn in forum Dreamweaver AppDev
    Replies: 2
    Last Post: May 4th, 11:21 AM
  3. Replies: 3
    Last Post: April 18th, 12:52 PM
  4. Replies: 0
    Last Post: April 15th, 01:22 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