Hi there,

I currently have a table with census data that is designed as follows: each row represents a location and each column is a language that contains the number of people in that location that speak the language. There are about 20 languages.

It looks something like this.

ID | language1 | language2 | language3|...| language N|
---------------------------------------------
1 |12 | 45 | 20 |...| 30 |
---------------------------------------------
2 | 50 | 40 | 10 |...| 60 |
---------------------------------------------
3 | 20 | 10 | 40 |...| 30 |

What I want to do is find out the top 5 languages spoken at each location.

Is there a query that will do this? I envision the final query table to look like this:

ID | First | Second | Third | Fourth| Fifth|
---------------------------------------------
1 | language2 | language N | language 3 |...| language 1 |
---------------------------------------------
2 | language1 | language 2 | language N |...| language 3 |
---------------------------------------------
3 | language3 | language N | language 1 |...| language 2 |

Thanks for the help!

No chance in a single query

You can ORDER rows BY column value in a query but you cannot change the order of the columns by a value in a row in a query.

To accomplish what you want it would need to restructure the rather poor table schema. The data relationships mean that it needs three tables, one for locations, one for languages and a linking table.

Thanks chrishirst. Do you think a Pivot query could do this? I have seen examples that look like would achieve this solution, but I do not understand how to use it.

Quite probably, assuming your DB server supports pivots, failing that it will have to be a stored procedure.

It took me a while to get my head round pivots - the example that finally got it to click was on asktom (aren't they all) - see http://asktom.oracle.com/pls/asktom/...31263576751669 and look down page to "Followup November 15, 2006 - 6am Central time zone"