return only rownum=1 - 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

General SQL Server Forums

New to SQL Server Programming

return only rownum=1

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2011-02-03 : 11:56:23

using sql server 2005

I am returning the rownum of a table based on the latest orderDate which is good, however I want a query where I only return the record if rownum=1.

My original query that returns all records regardless of rownum value is below:


select 
row_number() over (partition by custID order by orderDate DESC) rownum,orderID, custID, orderDate 
from ORDERS

however I want to return only those records where rownum=1 but when I try this query it gives me an error, the query:


select row_number() over (partition by custNum order by orhDate DESC) rownum,orderNum, custNum, orhDate from ORDERHEAD WHERE rowNum=1

when I try this I get the error:
<error>
invalid column name "rownum"
</error>

anyways, do any of you know how I can fix the syntax of my query to only return the records where rownum=1?

thanks

MC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-03 : 11:59:54

Make your original query a derived table:

select *
from
(
select 
row_number() over (partition by custID order by orderDate DESC) rownum,orderID, custID, orderDate 
from ORDERS
) as t
where rownum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources