CODEWITHSUNDEEP
sqlsql-server
Alex Miller
Alex Miller

Reputation: 11

Select last n rows matching a criteria

I'm new to SQL and I'm trying to write a query to filter values and retrieve the last n records matching that criteria.

I have data that has a MainAssemblyIdnty, Barcode, Cellname. For the Cellname field, there are 3 cells; cell 1, cell 2, and cell 3. I'm trying to retrieve the last n records for just cell 3. I've tried using TOP and MAX functions, but the results displayed only shows 30 results. I also looked in to the LIMIT function but it doesn't seem to be supported with my version of SQL. Check out the codes below:

SELECT TOP 100 (Barcode) FROM Results WHERE Cellname= 'Cell 3'

SELECT  (Barcode)
FROM Results
WHERE IIf(Cellname='Cell 3',MainAssemblyIdnty,null) > 
    (
    Select Max(MainAssemblyIdnty) - 100 
    From Results 
    WHERE Cellname='Cell 3'
    )

It appears to take the last 100 records total (doesn't matter which cellname) then filter by cellname, but I want it to filter by cellname then give 100 records of only that cellname.

Upvotes: 1

Views: 863

Answers (3)

John Tamburo
John Tamburo

Reputation: 447

You mention in several responses that you are looking for the 100 "most recent" rows where cellname = 'Cell 3'

First Do you have any kind of a timestamp? If so then use TOP and order by the timestamp DESC. For better performance you may be advised to create a nonclustered index on that column and the cellname column, like this:

CREATE NONCLUSTERED INDEX 
    IX_RESULTS_CELLNAME_TIMESTAMP
ON [RESULTS]
(
    [CELLNAME]
    ,[TIMESTAMP]
)

;

If you don't have a timestamp, then is the MainAssemblyIdnty column a sequential number?

If so, then you can do exactly as Gordon suggested above. For performance, you should consider creating the nonclustered index above but with MainAssemblyIdnty as the second indexed column.

Now be advised that the others who have informed you that you will get the top 100 rows in the order specified, and if fewer than 100 rows exist, you will get those, are entirely correct.

Hope this helps. Next time, please consider giving us the schema of the table(s) in your SELECT.

Thanks, John.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You should have an ORDER BY clause:

SELECT TOP 100 (Barcode)
FROM Results
WHERE Cellname = 'Cell 3'
ORDER BY MainAssemblyIdnty DESC ;

For performance, you want an index on Results(Cellname, MainAssemblyIdnty DESC).

If you only have 30 rows, then you will only get 30 results. It is not clear what you really intend in this case.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

TOP is the correct way to do this, and the only reason your attempt may not have worked is that you didn't supply an ORDER BY. Which means, get ANY 100 rows...not get the LAST 100 rows.

If you use MainAssemblyIdnty to determine which rows are "last", then you just needed to do this:

SELECT TOP 100 (Barcode) 
FROM Results 
WHERE Cellname= 'Cell 3' 
ORDER BY MainAssemblyIdnty DESC

EDIT based on comments: I'm pretty sure you are misinterpreting the results you are seeing. You're seeing less than 100 rows returned and you think that means that the query is FIRST getting the top 100 rows and THEN filtering those rows to only those with Cellname = 'Cell 3'.

That, however is not the case. SQL doesn't work that way. The filter is applied FIRST, and THEN the results are limited by the TOP 100. If you get less than 100 rows, that means there are less than 100 rows in the ENTIRE TABLE that match the filter.

If you doubt it, simply because there are "thousands of records in the table", test it with this simple query:

SELECT COUNT(*) 
FROM Results 
WHERE Cellname= 'Cell 3'

Upvotes: 1

Related Questions