Argthtjtr

Can somebody please give me the solution or explain the right approach for this question. So, I have a query which returns some values (att1). I would like also to have next to it the values which would represent a sorted order of att1. Something like this

att1   att2

 19     3

 2      2

 46     4

 78     5

 1      1

Thanks in advanced!

Assuming a table name of table1, The following should yield the desired result:

select a.att1, (select count(*) from table1 b where b.att1 <= a.att1) as att2

from table1 a;

For every record, the query calculates the number of records less than or equal to the current record, which is then output as the sort index.

I wrote an article on the various methods for this:

Sequential Rows in Microsoft Access

In its simplest form:

SELECT RowNumber(CStr([ID])) AS RowID, *, FROM SomeTable;

using the RowNumber function:

' Builds consecutive row numbers in a select, append, or create query

' with the option of a initial automatic reset.

' Optionally, a grouping key can be passed to reset the row count

' for every group key.

'

' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.

'

Public Function RowNumber( _

    ByVal Key As String, _

    Optional ByVal GroupKey As String, _

    Optional ByVal Reset As Boolean) _

    As Long



    ' Uncommon character string to assemble GroupKey and Key as a compound key.

    Const KeySeparator      As String = "¤§¤"

    ' Expected error codes to accept.

    Const CannotAddKey      As Long = 457

    Const CannotRemoveKey   As Long = 5



    Static Keys             As New Collection

    Static GroupKeys        As New Collection

    Dim Count               As Long

    Dim CompoundKey         As String



    On Error GoTo Err_RowNumber



    If Reset = True Then

        ' Erase the collection of keys and group key counts.

        Set Keys = Nothing

        Set GroupKeys = Nothing

    Else

        ' Create a compound key to uniquely identify GroupKey and its Key.

        ' Note: If GroupKey is not used, only one element will be added.

        CompoundKey = GroupKey & KeySeparator & Key

        Count = Keys(CompoundKey)



        If Count = 0 Then

            ' This record has not been enumerated.

            '

            ' Will either fail if the group key is new, leaving Count as zero,

            ' or retrieve the count of already enumerated records with this group key.

            Count = GroupKeys(GroupKey) + 1

            If Count > 0 Then

                ' The group key has been recorded.

                ' Remove it to allow it to be recreated holding the new count.

                GroupKeys.Remove (GroupKey)

            Else

                ' This record is the first having this group key.

                ' Thus, the count is 1.

                Count = 1

            End If

            ' (Re)create the group key item with the value of the count of keys.

            GroupKeys.Add Count, GroupKey

        End If

        ' Add the key and its enumeration.

        ' This will be:

        '   Using no group key: Relative to the full recordset.

        '   Using a group key:  Relative to the group key.

        ' Will fail if the key already has been created.

        Keys.Add Count, CompoundKey

    End If



    ' Return the key value as this is the row counter.

    RowNumber = Count



Exit_RowNumber:

    Exit Function



Err_RowNumber:

    Select Case Err

        Case CannotAddKey

            ' Key is present, thus cannot be added again.

            Resume Next

        Case CannotRemoveKey

            ' GroupKey is not present, thus cannot be removed.

            Resume Next

        Case Else

            ' Some other error. Ignore.

            Resume Exit_RowNumber

    End Select

End Function

All code is also on GitHub: VBA.RowNumbers