Select the last row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select the last row

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2011-02-02 : 04:55:33

Hi Guys

I would like to use a statement that selects the last row and compares it with a condition. See table below

Id	Name	Date	Amount
1	John	1/1/2011	234
2	John	1/2/2011	345
3	John	1/3/2011	233
4	John	1/4/2011	456

I want to see if the last row of column Date equals 1/5/2011. The expectant result should show be a negative answer or not return any result at all.

I DON'T want to use SELECT TOP 1

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-02 : 05:31:16

As far as I know, SQL doesn't store the data in entered order.

You can try max(date) or use rank function

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-02-02 : 05:36:54

Hi,

try this!

create Table #tab(Id int,Name varchar(10),tDate datetime,Amount int)

insert into #tab
select 1,'John','1/1/2011',234 union all
select 2,'John','1/2/2011',345 union all
select 3,'John','1/3/2011',233 union all
select 4,'John','1/4/2011',456

sol:

select id ,name,tdate,amount from
(select ROW_NUMBER() OVER (ORDER BY id desc) as rownum,id ,name,tdate,amount from #tab) t
where t.rownum=1 and tdate='1/5/2011'

sathish

tkotey
Yak Posting Veteran

75 Posts

Posted - 2011-02-02 : 06:09:44

quote:
Originally posted by pk_bohra

As far as I know, SQL doesn't store the data in entered order.

You can try max(date) or use rank function

How do I use the max(date) or rank function

eralper
Yak Posting Veteran

66 Posts

Posted - 2011-02-02 : 07:36:06

Satish has suggested a good solution.
Using ranking functions is new with SQL2005.
But using TOP 1 is also a good solution I did not understand why you do not want to use it.

You can extend the solution of Satish to one more step


with cte as (
	select 
		*, ROW_NUMBER() OVER (PARTITION BY tdate ORDER BY id desc) as rownum
	from tab
)
select 
	id ,name,tdate,amount 
from
cte
	where rownum = 1 
	and tdate = '1/2/2011'

If you commend the tdate filter criteria, you will get the last rows for each day in a single select.
The PARTITION BY clause enables ranking for each partition.
Check [url]http://www.kodyaz.com/articles/sql-select-count-resultset-rows-count-over-partition-by.aspx[/url] and [url]http://www.kodyaz.com/articles/delete-duplicate-rows-using-row-number-partition-by-order-by.aspx[/url] for samples

-------------
Eralper
http://www.kodyaz.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-02 : 07:38:20

select * from table where Date = '20110105' and ID = (select max(ID) from table)

If that isn't what you want then please be more specific.

No, you're never too old to Yak'n'Roll if you're too young to die.

tkotey
Yak Posting Veteran

75 Posts

Posted - 2011-02-02 : 08:42:32

Thank you all. I managed to solve it. I've learnt a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources