Reverse equivalent to TOP

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen:

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen --

Use a subquery. Get IDs in the order you want using an "order by desc". Get the IDs you want using a "TOP". Get the data you want using a "select * ... where in". And so on.

Here is some sample code.

use northwind
go
--get all the rows, in order, to see what we are working with...
select * from Shippers order by ShipperID asc
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
3           Federal Shipping                         (503) 555-9931
(3 row(s) affected)
*/
--get the top 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID asc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
--get the bottom 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID desc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
3           Federal Shipping                         (503) 555-9931
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
 

HTH.

Thank you.

-- Mark Kamoski

|||

You can still use TOP 1 to get the bottom 1 by adding ORDER BY Column_C DESC

Like:

SELECT TOP 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

--edited

|||

Yes remember to changeBOTTOM -> top

SELECTtop 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC