The power of SQL is that we are able to construct queries to find specific sets of data based on criteria. For example, if we have a database for a pound, we might query “Find me all the brown dogs.” The result from this query would return how many brown dogs we have, if any, and any related metadata, i.e. name, breed, size, age, etc. In this way, SQL is very powerful and beneficial. Sometimes, however, we already know the particular dogs we want to get information about, and just simply need to retrieve them. IN this event, SQL gives us the function
IN(), we can specify a query like “Find me Rover, Rex, T-bone, and Charlie.” Our result set would return the related metadata about those dogs. However, the order in which the data returned is completely independent of the list order we specified. For example, our result set might first return information about the dogs in this order: T-bone, Rex, Rover, and Charlie. We can specify an
ORDER condition to order the result set on some particular metadata, like age, but there are certainly cases were the order of the list we’re seeking is not dependent on any stored metadata. The trick then becomes how can we return our results to match the order in which we specified them using
Utilizing the function
FIELD() we are able to accomplish our goal above.
FIELD() takes two or more parameters. The first parameter is the value to find, and the second and subsequent parameters are the values to match against the first parameter. For instance, using
FIELD('bob', 'frank', 'george', 'mike', 'bob', 'larry') would return 4, as it is the 4th position in the list of parameters (frank, george, and mike are the first three). We can replace the first parameter with a reference to a column in our result set, and replicate our list from the
IN() function for the remainder parameters. In our example above, we would use
FIELD(name, 'Rover', 'Rex', 'T-bone', 'Charlie'). Then, when Rover is retrieved, this function would return 1, and 2 for Rex, and so on. Thus, the order would be relevant to the order in our list.