top 2000 records or last 2000 records

[Mestidor]

Could some help me with this,
I want to selecte from a table the last 2000 record.
with sql it's easy but with crystal report even I insert in the sql statement top 2000, I only have the first 2000 rows

 

[Naith]

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

 

[Mestidor]

I am using msqsl databe

 

[Naith]

The principal is the same, Mestidor. The subselect I gave you should do it.

What sql were you using, out of curiosity?

Naith

 

[Guest_imported]

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.