hi all
i need to select out the last 10 records for a particular condition, ie
select last_10_records from mytable where user_name = "Karl"
any ideas?
Slán
~M
____
Mark

What database are you using?

Also, note that without an ORDER BY clause in your statement, most databases provide no guarantee of any particular ordering, which makes the "last 10 rows" ambiguous.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

perhaps try something like this:

SELECT TOP 10 tStocks.ID, tStocks.idCompanyID, tStocks.dtTrade
FROM tStocks
WHERE tStocks.idCompanyID=485
ORDER BY tStocks.ID DESC

this obviously depends on the id column being sequential

try to adapt that to your schema, and/or research the key words
"TOP"

might help

conrad

Hi Mark, ColumnName(use the relavant columnname from your table) marked in red above, decides the order of rows by which you would like to retrieve the last 10 records, as DESC sorts it by descending order and thus lets you get the top 10 records of the descendingly ordered rows.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

TOP Count(*) - 10 rows are the rows which u don't want. So u can find the TOP Count(*) - 10 rows in a subquery and then select all rows from table where these rows should not be included.

Its not must to sort to do this.If u r sorting the table then, it becomes very easy. If u have a primery key with clustered index on table then also becomes very easy because table is sorted on the PK. But if ur table doesn't have any Clustered index then u can use this logic.


Example >> Suppose u have a Table T1 with Key colum Col1.

Then U can fire this script in a single batch.
--------------------
DECLARE @ROWSNUM NUMERIC
DECLARE @STRQUERY VARCHAR(1000)
SELECT @ROWSNUM = (COUNT(*)-10) FROM T1
SET @STRQUERY='SELECT * FROM T1 WHERE COL1 NOT IN ('
SET @STRQUERY=@STRQUERY+ 'SELECT TOP '+ CAST(@ROWSNUM AS VARCHAR(10))
SET @STRQUERY=@STRQUERY+' COL1 FROM T1 )'
EXECUTE (@STRQUERY)
----------------------
Any sorting order is not required with above. However if ur table doesn't have any unique key to identify a row, its really a bad table design and u have to find out some combination keys to identify a row.

Hope you got my point. By the way this is for SQL Server.

I hope the logic will help u if u r using some other RDBMS.

B. Anant