Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
top 2000 records or last 2000 records
- Thread starter Mestidor
- Start date
Top 2000 means the first 2000. Not the last.
How are you defining the 'last 2000' records? In a database, records aren't usually ordered chronologically, unless you've ordered by a timestamp or a similar unique field of which you know the current maximum value.
I don't know what type of database you're querying, so for the sake of argument, I'll imagine it's Oracle.
If you were querying an Oracle database to find out the first 5 records in a table, you could either say
'Select * from table'
and look at the first 5 records returned, or
'Select * from table where rownum < 6'
- you'll find that it's the latter that gives you the real first 5 records.
There isn't a command in Crystal or RDBMS 3GLs which will act like a reverse rownumber command. You would have to execute a query similar to :
select * from table_name
minus
select * from table_name where rownum < ( select count(*)-2000 from table_name)
to get the last 2000 rows. You'd then point Crystal at the results and query off that.
Naith
How are you defining the 'last 2000' records? In a database, records aren't usually ordered chronologically, unless you've ordered by a timestamp or a similar unique field of which you know the current maximum value.
I don't know what type of database you're querying, so for the sake of argument, I'll imagine it's Oracle.
If you were querying an Oracle database to find out the first 5 records in a table, you could either say
'Select * from table'
and look at the first 5 records returned, or
'Select * from table where rownum < 6'
- you'll find that it's the latter that gives you the real first 5 records.
There isn't a command in Crystal or RDBMS 3GLs which will act like a reverse rownumber command. You would have to execute a query similar to :
select * from table_name
minus
select * from table_name where rownum < ( select count(*)-2000 from table_name)
to get the last 2000 rows. You'd then point Crystal at the results and query off that.
Naith
- Thread starter
- #3
Guest_imported
New member
- Jan 1, 1970
- 0
To get the last 2000 records shouldn't be a problem, providing all the record selection criteria are included in the SQL statement.
It's simply a case of reversing the sort order on the relevant field and selecting the top 2000 (or where rownum<2001) depending on the RDBMS.
If the SQL doesn't include the selection criteria, the principle remains the same but you would have to sort in the crystal report and use a rowcounter, where records beyond 2000 are suppressed.
It's simply a case of reversing the sort order on the relevant field and selecting the top 2000 (or where rownum<2001) depending on the RDBMS.
If the SQL doesn't include the selection criteria, the principle remains the same but you would have to sort in the crystal report and use a rowcounter, where records beyond 2000 are suppressed.
- Status
Similar threads
- Locked
- Question