First subquery - 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 2008 Forums

Transact-SQL (2008)

First subquery

Author

Topic

Alpha1980
Starting Member

3 Posts

Posted - 2011-07-27 : 12:23:55

I need to run the following query:

SELECT [orderno]
,[type]
,[customerno]
,[date]
,[pr_code]
,[quantity]
FROM [orders], [products]
WHERE [orders].[orderno] = [products].[orderno]

However, I only want to see the most recent orderno for each customer since May this year and for order types 1, 2 and 3.

The following query does this:

SELECT [customerno],MAX([orderno]) AS lastorderno
FROM [orders]
WHERE ([type] = '1' or [type] = '2' or [type] = '3') and [date] >= '2011-05-01'
GROUP BY [customer]

Can anybody advise of the best way to to combine the above two queries?

Many Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-27 : 12:49:32

YOu can use row_number function, like this:


WITH cte AS 
(
SELECT
	[orderno],
	[type],
	[customerno],
	[date],
	[pr_code],
	[quantity],
	ROW_NUMBER() OVER (PARTITION BY [customerno] ORDER BY [orderno] DESC) AS RN
FROM
	[orders],
	[products]
WHERE
	[orders].[orderno] = [products].[orderno]
	AND ([type] = '1' or [type] = '2' or [type] = '3') 
	AND  [date] >= '2011-05-01'
)
SELECT * FROM cte WHERE RN = 1;

Alpha1980
Starting Member

3 Posts

Posted - 2011-07-28 : 04:37:17

Thanks very much for your help - much appreciated.

I'm getting the message 'ROW_NUMBER' is not a recognized function name.

I'm using SQL Server 2008 Express - is the function supported?

Thanks again.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 06:18:11

ROW_NUMBER() function is supported in SQL 2008 (and even in SQL 2005).

I don't doubt you, but you can check the version of your SQL Server by running this command:

select @@version

Also, check the compatibility level of the database. You can do that using this command where you will need to replace MyDB with your database name

exec sp_dbcmptlevel 'MyDB'

It should display a number that is greater than or equal to 90.

Alpha1980
Starting Member

3 Posts

Posted - 2011-07-28 : 06:37:56

My mistake - the database I'm connecting to is SQL Server 2000.

I guess that explains it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 07:56:09

On SQL 2000, you can do it using a subquery - like this. I don't have SQL 2000, so this is not tested.

SELECT
	[orderno],
	[type],
	[customerno],
	[date],
	[pr_code],
	[quantity]
FROM
	[orders],
	[products],
	(
		SELECT
			[customerno],
			MAX([orderno]) AS lastorderno
		FROM
			[orders]
		WHERE
			([type] = '1' OR [type] = '2' OR [type] = '3')
			AND [date] >= '2011-05-01'
		GROUP BY
			[customer]
	) m
WHERE
	[orders].[orderno] = [products].[orderno]
	AND m.[customerno] = [orders].[customerno] AND m.lastorderno = [orders].[orderno]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources