ORDER BY <Ordinal Field Position> Possible? 4

[Michael42]

Hello,

In the ORDER BY part of a SELECT how can I use the ordinal position of a field instead of it's name?

I'd like to always use the first field in the interface I have for many tables.



Thanks,

Michael42

 

[hneal98]

Are you doing this from code? What does your code look like?

 

[Michael42]

hneal98,

I was hoping to use a pure SQL statement to handle this (not vb code). Not knowing SQL that deeply I am not sure if this is possble.

The code that fires the SQL will be from VB:
Dim sSQL as String
Dim sTable as String
sTable = "locations"
sSQL = "SELECT * FROM " & sTable & " ORDER BY 1"
Data1.RecordSource=sSQL


Thanks,

Michael42

 

[hneal98]

I would think it would be something like this:

tablename.fields(0)

your sql statement would look like this:

"SELECT * FROM " & sTable & " ORDER BY " & oTable.fields(0)

You would have to declare oTable as an object and assign the table locations to it.

Hope this helps.

 

[nicsin]

Michael,

I don't know if it is going to work with * but I think it will work if you explicitly state the fields.

select filed1, filed2, field3 
from table1 
order by 1

 

[MichaelRed]

I think (cogito! Ergo?) it would need to be Fields(1).Name, obtained from a tdf objectbut then the syntax could be even more obscure. I did a brief procedure:

Public Function basQryByOrdnial(sTable As String, ordnial As Integer) As String

    'Michael Red    1/9/04.
    'for Tek-Tips thread701-743214, Michael42

    'Sample Usage: _
    ? basQryByOrdnial("tblCombins", 2) _
    SELECT * FROM tblCombins ORDER BY Col3;

    Dim dbs As DAO.Database
    Dim tdf As TableDef
    Dim sSQL As String
    Dim fldName As String

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(sTable)
    fldName = tdf.Fields(ordnial).Name

    sSQL = "SELECT * FROM " & sTable & " ORDER BY " & fldName & ";"
    basQryByOrdnial = sSQL


End Function

Which returns the string SQL, which you could use to inset into the controls' control source.

I tested it by running the example usage from t he immediat window, copying the returned string, pasting it into an empty query and running that. The results were as I would expect on the table.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jrbarnett]

Michael42 and Nicsin are correct - you can use the ordinal position of the column to sort a query, and on multiple columns as well, just as with column names.
This is very useful in union queries where the fieldnames may differ in different sections, or there are subqueries bringing in data from other tables.

John

 

[Michael42]

Wow, looks like there are a few ways to handle this. Thanks all for you comments.

Michael42

 

[hneal98]

Thanks for the star.