SQL: how to include row number in result?

Posted on 2000-04-23
Last Modified: 2010-04-04
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.

Question by:rwv
LVL 15

Accepted Solution

simonet earned 100 total points
ID: 2743042
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 }




Author Comment

ID: 2752199
  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;

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Printing problem 2 93
Tviruailstringtree sort multi columns on header click 1 63
Delphi inherited method 6 70
Need Help Delphi 2010 CheckBox1 Stored value in memo 13 67
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question