SQL: how to include row number in result?

Is it possible to include the row number of a query result in the result?
I want to use the result in a TDBGrid with the row numbers displayed.  Even better would be to have the row numbers in the left fixed column of the grid.

Who is Participating?
Yes, it's possible.

First of all, you need to create a calculated column for the table. This column will hold the record number, and the rec# will be calculated at runtime.

Once you create that column, for each record, you have to run the procedure below, taken from the Borland TIs. YOu can call it in the OnCalculate event of the TTable component.

Note: if you're using Paradox, you can only do this using approaches like the one below. For a true SQL database server (like SQL Server, Oracle, etc), you simply retrieve the ROWID.

Q:  "How can I determine the current record number for a dataset?"

A:  If the dataset is based upon a Paradox or dBASE table then
the record number can be determined with a couple of calls to
the BDE (as shown below).  The BDE doesn't support record
numbering for datasets based upon SQL tables, so if your server
supports record numbering you will need to refer to its

    The following function is given as part of a whole unit and
takes as its parameter any component derived from TDataset
(i.e. TTable, TQuery, TStoredProc) and returns the current
record number (greater than zero) if it is a Paradox or dBASE
table.  Otherwise, the function returns zero.

    NOTE: for dBASE tables the record number returned is always
the physical record number.  So, if your dataset is a TQuery or
you have a range set on your dataset then the number returned
won't necessarily be relative to the dataset being viewed,
rather it will be based on the record's physical position in
the underlying dBASE table.

  DB, DBTables, DbiProcs, DbiTypes, DbiErrs;

function GetRecordNumber(Dataset: TDataset): Longint;
  CursorProps: CurProps;
  RecordProps: RECProps;
  { Return 0 if dataset is not Paradox or dBASE }
  Result := 0;
  with Dataset do
    { Is the dataset active? }
    if State = dsInactive then
      raise EDatabaseError.Create('Cannot perform this operation '+
                                  'on a closed dataset');

    { We need to make this call to grab the cursor's iSeqNums }
    Check(DbiGetCursorProps(Handle, CursorProps));

    { Synchronize the BDE cursor with the Dataset's cursor }

    { Fill RecordProps with the current record's properties }
    Check(DbiGetRecord(Handle, dbiNOLOCK, nil, @RecordProps));

    { What kind of dataset are we looking at? }
    case CursorProps.iSeqNums of
      0: Result := RecordProps.iPhyRecNum;  { dBASE   }
      1: Result := RecordProps.iSeqNum;     { Paradox }



rwvAuthor Commented:
  I found it wasn't neccessary to use the above function GetRecordNumber using a TQuery and Paradox tables.
  Simply define the OnCalcFields proc for the query and update the calculated field, in this case RecordNum:

   DataSet.FieldByName('RecordNum').AsInteger := DataSet.RecNo;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.