Help with Select - 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

Help with Select

Author

Topic

celsomineiro
Starting Member

1 Post

Posted - 2009-04-22 : 16:02:47

Hello everybody, this is my first time here, and I´d like some help:

I have this order table tb_order

O_Id OrderDate OrderNumber Customer CustID
1 2008/11/12 001 Hansen 01
2 2008/10/23 002 Nilsen 02
3 2008/09/02 003 Hansen 01
4 2008/09/03 004 Hansen 01
5 2008/08/30 005 Jensen 03
6 2008/10/04 006 Nilsen 02

and the script below:

Select CustID, OrderNumber, OrderDate

from tb_order

it brings some invoices of each customer.

I´d like to get just the last record of each customer. It´s like a group by but group by only works if I select just the CustID.

Any Ideias

Thanks,

Celso.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-22 : 16:13:19

What version of SQL Server are you using? If 2005 or above you can use the ROW_NUMBER function:

SELECT *
FROM
	(
		Select 
			CustID, 
			OrderNumber, 
			OrderDate,
			ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY OrderDate DESC) AS RowNum
		from tb_order
	) AS T
WHERE
	RowNum = 1

amilojko
Starting Member

2 Posts

Posted - 2009-04-22 : 19:20:50

Try
SELECT Customer, Max(Date) as MaxOfDate FROM Table GROUP BY Customer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources