Getting the last X amount of records from a table

[johnpp]

Hi , I want to get, say the last 3 records
from a table according to an ascending ID (where ID is a integer)

eg

in oracle i could do , say,
select * from X
order by ID
and rownum < 4 ;

this would get me the 3 most recent records (assuming ID+1 is always added as the new ID)

Is there something similar to rownum in MS Access ??

cheers
pls reply to john.perez@optus.com.au

 

[KenReay]

Hi

Will Top do it for you?

Syntax is SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table

TOP n [PERCENT] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;If you don't include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.The TOP predicate doesn't choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer.TOP doesn't affect whether or not the query is updatable.

Regards Ken Reay
Freelance Solutions Developer
UK
kenneth.reay@talk21.com

 

[johnpp]

Sorry, i forgot to include something.
Im using a group by clause in my SQL.
so i want the top X rows per grouping.

Is this possible ?

 

[KenReay]

Hi

It would appear so:

SELECT TOP 5 PordersDetails.POrderId, PordersDetails.Price
FROM PordersDetails
GROUP BY PordersDetails.POrderId, PordersDetails.Price
HAVING (((PordersDetails.Price)>0))
ORDER BY PordersDetails.Price;
Ken Reay
Freelance Solutions Developer
UK
kenneth.reay@talk21.com

 

[soslippy]

yes that works perfectly, thx v much