# Retaining the order of columns in a result set

**URL:** <https://www.ruby-forum.com/t/retaining-the-order-of-columns-in-a-result-set/48741>\
**Category:** Rails\
**Created:** [November 23, 2005, 11:50pm UTC](https://www.ruby-forum.com/t/retaining-the-order-of-columns-in-a-result-set/48741 "2005-11-23T23:50:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![austin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/77aa72/32.png) [@austin](https://www.ruby-forum.com/u/austin)\
**Post date:** [November 23, 2005, 11:50pm UTC](https://www.ruby-forum.com/t/retaining-the-order-of-columns-in-a-result-set/48741/1 "2005-11-23T23:50:35Z")

</div>

HI,

I am trying to get the ordered column names in a result set using  
find\_by\_sql. Perhaps my approach is all wrong, but I am using  
`each .. do`. How do I go about keeping the order of the columns as  
they appear in the SELECT statement?

For example:

```
@rows = SomeConnection.find_by_sql "SELECT one,two,three,four FROM

```

some\_table"

```
@rows.each do |row|
 		$test = row.attribute_names.join(",")
end

```

…

```
<%=$test%>

```

produces something like:

```
        four,one,three,two

```

What I would like is for them to keep their order as they appear in  
the select statement:

```
        one,two,three,four

```

…

I hope that is a clear question. Thanks for giving your help!

Austin

---

<div class="post-metadata">

**Author:** ![austin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/77aa72/32.png) [@austin](https://www.ruby-forum.com/u/austin)\
**Post date:** [November 23, 2005, 11:58pm UTC](https://www.ruby-forum.com/t/retaining-the-order-of-columns-in-a-result-set/48741/2 "2005-11-23T23:58:38Z")

</div>

> I am trying to get the ordered column names in a result set using  
> find\_by\_sql. Perhaps my approach is all wrong, but I am using  
> `each .. do`. How do I go about keeping the order of the columns as  
> they appear in the SELECT statement?

The recordset comes back as a hash, so naturally they’re out of order.  
Why not just display the columns in the order you want?

@rows = Person.find\_by\_sql “SELECT \* FROM people;”

## \<% @rows.each do |row| -%\> \<% %w( id name age ).each do |col| -%\> \<%= row.send(col) %\> \<% end -%\> \<% end -%\>

rick  
[http://techno-weenie.net](http://techno-weenie.net)

---

<div class="post-metadata">

**Author:** ![austin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/77aa72/32.png) [@austin](https://www.ruby-forum.com/u/austin)\
**Post date:** [November 25, 2005, 5:07pm UTC](https://www.ruby-forum.com/t/retaining-the-order-of-columns-in-a-result-set/48741/3 "2005-11-25T17:07:23Z")

</div>

On 23-nov-2005, at 23:47, Austin Swinney wrote:

> …
> 
> I hope that is a clear question. Thanks for giving your help!  
> One option would be to use the functionality fo the underlying driver  
> to get the query result in the form that you want. However, you can  
> use some reflection  
> to use the order provided by ActiveRecord itself (it might be not  
> applicable to your own case)

> > Person.columns.collect{|c| c.name}  
> > =\> [“id”, “login”, “name”, “password”, “email”, “busy”, “cv”,  
> > “invited\_by\_id”, “invites\_left”, “admin”, “region\_id”]

This is aliased as Person.column\_names, which makes you rquery into:

```
@rows = SomeModel.find_by_sql "SELECT one,two,three,four FROM

```

some\_table"

```
@rows.each do |row|
	for column in SomeModel.columns
		#iterate through columns in natural order
	end
end
```

---

<div class="post-metadata">

**Author:** ![austin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/77aa72/32.png) [@austin](https://www.ruby-forum.com/u/austin)\
**Post date:** [November 25, 2005, 10:42pm UTC](https://www.ruby-forum.com/t/retaining-the-order-of-columns-in-a-result-set/48741/4 "2005-11-25T22:42:52Z")

</div>

thanks for the response, julik,

i’m writing an export script that pulls an unknown query and needs to  
retain the column names and the column order used in the sql for the  
purpose of outputting a spreadsheet to the browser.

that is why this causing me so much grief. i’m using a generic db  
connection, not any specific table. i just know the database, but  
the query is a join on at least two tables out of many.

i did something similiar to what i am trying to do here with a ruby  
script. it uses fetch\_fields to get the column info on the result set  
after the query has been made. that is essentially what i am looking  
for in rails, column information on a query result.

# xsql is some unknown query

```
 res_xc = dbh.query(xsql)
 @cols = []
 res_xc.fetch_fields.each_with_index do |info,i|
   @cols << info.name

```

# witness the power of info

# printf “— Column %d (%s) —\n”, i, info.name

# printf “table: %s\n”, info.table

# printf “def: %s\n”, info.def

# printf “type: %s\n”, info.type

# printf “length: %s\n”, info.length

# printf “max\_length: %s\n”, info.max\_length

# printf “flags: %s\n”, info.flags

# printf “decimals: %s\n”, info.decimals

```
  end

 @line = []
 @line << "\n\r"
 @line << @cols.join("\t")
 while row = res_xc.fetch_row do
   @col = []
   row.each do |v|
     @col << v
   end
   @line << @col.join("\t")
 end
 file = @line.join("\n")
```