Add RowNumber Problem

N

naobao

Registered User.
Local time
Today, 11:10
Joined
Feb 13, 2014
Messages
99
I used the following SQL to automatically generate the RowNumber column

SELECT A.id, (SELECT COUNT(*) FROM packing_ship_mark_1a WHERE A.ID>=ID) AS RowNum
FROM packing_ship_mark_1a AS A;

id RowNumber
596 24
568 1
571 2
572 3
573 4
574 5
575 6
576 7
577 8
578 9
579 10
597 25
581 11
582 12
585 13
586 14
587 15
588 16
589 17
590 18
591 19
592 20
593 21
594 22
595 23

But the RowNumber is not displayed as I expected. How can I achieve the following result?

596 1
568 2
571 3
572 4
573 5
574 6
575 7
576 8
577 9
578 10
579 11
597 12
582 13
581 14
585 15
587 16
588 17
589 18
590 19
591 20
592 21
593 22
594 23
595 24
586 25
 
add sort to your query By ID
 
arnelgp said:
sort to your query By ID
Click to expand...
Functionally, this already happens with the following expression: WHERE A.ID>=ID

@naobao
For the method used, you need a field whose sorted contents reflect the exact order required. The ID field alone does not provide this. Are there any other fields in the table, perhaps a usable one?

If you have SQL Server as backend: T-SQL has a function called Rownumber.
Otherwise, I can think of three ways you can create the numbering you want. However, all of them go beyond the scope of a simple query.

1) Create a field in the table later:
SQL: 
ALTER TABLE packing_ship_mark_1a ADD COLUMN RowNumber Counter(1,1)

2) The field already exists in the table. The table is loaded into a recordset, which fixes the order of the records. The value for the field is then incremented using a simple loop.

3) The QueryIncrement function generates a numbering. However, this is not stable when the query is used again. Therefore, the generated query result must be fixed, in this case using a maketable query.
SQL: 
SELECT 
   ID, 
   QueryIncrement(ID, 1) AS RowNumber 
INTO
   NewTable
FROM 
   packing_ship_mark_1a
WHERE
   Reset_Globals() = True
Code: 
' mod_QueryIncrement

Public gQI_Category As String
Public gQI_Icount As Long

Public Function QueryIncrement(ByVal Init As Variant, ByVal StartValue As Long, ParamArray Categories() As Variant) As Long
    Dim k As Long, v As Variant
    Dim sAllCategories As String

    v = Init
    If IsMissing(Categories) Then
        sAllCategories = "$$$$$"
    Else
        For k = 0 To UBound(Categories)
            If IsNull(Categories(k)) Then Categories(k) = vbNullString
        Next
        sAllCategories = Join(Categories, "|")
    End If

    If gQI_Category = sAllCategories Then
        gQI_Icount = gQI_Icount + 1
    Else
        gQI_Category = sAllCategories
        gQI_Icount = StartValue
    End If
    QueryIncrement = gQI_Icount
End Function

Public Function Reset_Globals() As Boolean
    gQI_Category = vbNullString
    gQI_Icount = 0
    Reset_Globals = True
End Function
 
ebs17 said:
Functionally, this already happens with the following expression: WHERE A.ID>=ID

@naobao
For the method used, you need a field whose sorted contents reflect the exact order required. The ID field alone does not provide this. Are there any other fields in the table, perhaps a usable one?

If you have SQL Server as backend: T-SQL has a function called Rownumber.
Otherwise, I can think of three ways you can create the numbering you want. However, all of them go beyond the scope of a simple query.

1) Create a field in the table later:
SQL: 
ALTER TABLE packing_ship_mark_1a ADD COLUMN RowNumber Counter(1,1)

2) The field already exists in the table. The table is loaded into a recordset, which fixes the order of the records. The value for the field is then incremented using a simple loop.

3) The QueryIncrement function generates a numbering. However, this is not stable when the query is used again. Therefore, the generated query result must be fixed, in this case using a maketable query.
SQL: 
SELECT
   ID,
   QueryIncrement(ID, 1) AS RowNumber
INTO
   NewTable
FROM
   packing_ship_mark_1a
WHERE
   Reset_Globals() = True
Code: 
' mod_QueryIncrement

Public gQI_Category As String
Public gQI_Icount As Long

Public Function QueryIncrement(ByVal Init As Variant, ByVal StartValue As Long, ParamArray Categories() As Variant) As Long
    Dim k As Long, v As Variant
    Dim sAllCategories As String

    v = Init
    If IsMissing(Categories) Then
        sAllCategories = "$$$$$"
    Else
        For k = 0 To UBound(Categories)
            If IsNull(Categories(k)) Then Categories(k) = vbNullString
        Next
        sAllCategories = Join(Categories, "|")
    End If

    If gQI_Category = sAllCategories Then
        gQI_Icount = gQI_Icount + 1
    Else
        gQI_Category = sAllCategories
        gQI_Icount = StartValue
    End If
    QueryIncrement = gQI_Icount
End Function

Public Function Reset_Globals() As Boolean
    gQI_Category = vbNullString
    gQI_Icount = 0
    Reset_Globals = True
End Function
Click to expand...
tabel is on sql server
 
you can also do this in access using a UDF.
put this in a Module and replace "dbo_table_1" with the name of your linked table to mssql:
Code: 
Public Function fnRank1(ByVal id As Long) As Long
    '
    ' put the Linked table name here and replace dbo_table_1
    '
    Const the_linked_table As String = "packing_ship_mark_1a"
  
    Static d_obj As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Long

   ' note to reset this function pass 0 or negative value to the function
    If id < 1 Then
        Set d_obj = Nothing
        Exit Function
    End If
  
    If (d_obj Is Nothing) Then
      
        Set d_obj = CreateObject("scripting.dictionary")
        Set db = CurrentDb
        Set rs = db.OpenRecordset(the_linked_table, dbOpenSnapshot, dbReadOnly)
      
        With rs
            If Not (.BOF And .EOF) Then
                rs.MoveLast
                rs.MoveFirst
            End If
            Do Until .EOF
                i = i + 1
                d_obj(!id & "") = i
                .MoveNext
            Loop
            .Close
        End With
      
    End If
  
    fnRank1 = d_obj(id & "")
              
End Function

you then change your query to:
Code: 
SELECT A.id,  fnRank1(A.[id]) AS RowNum
FROM packing_ship_mark_1a AS A;

//Edit: i already replaced the table on the code
 
As already advised - Use the Row_Number() function on the server.
It is an inbuilt server side windowing function and incredibly efficient, as well as giving you flexible partitions and sort order.
 
Clearly we're missing something. Are these records grouped by something else and you want the sequence to restart when the group code changes? or is there something magical about the number 596 that you are not telling us?

What is the point of the sequence number? Why not number the records as you add them to the database?
 
not magical - a typo according to post #7
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom