Last 3 orders per customer - 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

Last 3 orders per customer

Author

Topic

noks
Starting Member

9 Posts

Posted - 2007-05-10 : 05:07:05

Good day.
Am new with SQL server and i use SQL Server 2005

i have 3 tables (1 = customers, 2 = orders, 3 = keys) both these tables are linked by the key table

I wanna be able to "view" (as am only allowed to view) the last three orders for every customer.

I tried using the top predicate/ clause with a group by & count but unfortunately am getting no where.

Please kindly help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-10 : 05:15:08

SELECT CustomerID FROM (
SELECT CustomerID, OrderNumber, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RecID
) AS d
WHERE RecID BETWEEN 1 AND 3

Peter Larsson
Helsingborg, Sweden

noks
Starting Member

9 Posts

Posted - 2007-05-10 : 05:37:34

thanks Peso.
I tried that as well (saw it in 1 of the previuosly posted threads) but i keep getting the following error msg
"The OVER SQL construct or statement is not supported."

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-05-10 : 05:39:33

Peter should be banned from posting until at least 3 others have a go and post first!

Drew

"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-05-10 : 05:41:58

Are you using the Query Designer or the Query window?

Drew

"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

noks
Starting Member

9 Posts

Posted - 2007-05-10 : 05:42:58

quote:
Originally posted by chiragkhabaria

which version of SQL SERVER you are using??

Chirag

http://chirikworld.blogspot.com/

Microsoft SQL Server Management Studio Express (2005)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 05:48:16

[code]
SELECT CustomerID FROM (
SELECT CustomerID, OrderNumber, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RecID
From YourTable
) AS d
WHERE RecID BETWEEN 1 AND 3
[/code]

Chirag

http://chirikworld.blogspot.com/

noks
Starting Member

9 Posts

Posted - 2007-05-10 : 05:48:44

quote:
Originally posted by drewsalem

Are you using the Query Designer or the Query window?

Drew

"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Query Designer

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-10 : 05:51:13

declare @cust table (cust_id int, cust_name varchar(50))
insert @cust
select 124, 'raja' union all
select 145, 'vikram' union all
select 160, 'visu'

declare @order table (orid int, ordername varchar(20))
insert @order
select 1, 'hamam' union all
select 2, 'rex' union all
select 3, 'bril' union all
select 4, 'ink' union all
select 5, 'food' union all
select 6, 'lays' union all
select 7, 'snacks'

declare @keys table (cust_id int, orid int, dt datetime)
insert @keys
select 124, 3, '02/01/2007' union all
select 160, 6, '03/02/2007' union all
select 124, 4, '03/03/2007' union all
select 124, 1, '04/06/2007' union all
select 145, 2, '05/05/2007' union all
select 124, 5, '05/02/2007' union all
select 160, 2, '04/05/2007' union all
select 160, 2, '02/02/2007' union all
select 160, 2, '05/02/2007'

Select b.cust_id, b.cust_name, b.orid, b.ordername, b.dt From
(Select *, count = (Select Count(Distinct(dt)) from @keys where dt >= a .dt and cust_id = a.cust_id) from
(Select k.cust_id, k.orid,k.dt, o.ordername, c.cust_name from @keys k left outer join
@order o on k.orid = o.orid left outer join
@cust c on c.cust_id = k.cust_id )as a ) as b where b.count between 1 and 3 order by b.cust_id, b.dt desc

Sorry if u have 2005...u may have easy way to do

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-05-10 : 05:52:24

I don't think Query Designer supports it. Try Peters suggestion in the Query window.

Drew

"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 05:54:12

Yes query designer doest support this kind of queries..

BOL Says..

quote:

This dialog box may appear when you enter or edit an SQL statement in the SQL pane; you then move to another pane, verify the query, or attempt to execute the query; and one of the following conditions applies:

The SQL statement is incomplete or contains one or more syntax errors.

The SQL statement is valid but is not supported in the graphical panes (for example, a Union query).

The SQL statement is valid but contains syntax specific to the data connection you are using.

Chirag

http://chirikworld.blogspot.com/

noks
Starting Member

9 Posts

Posted - 2007-05-10 : 06:12:35

WOW this is the first time ever am using windows query (didn't even know it existed

) and it's working. Now i have to see if it's gonna work if i select everything i need

Thanks guys and thank you for responding so quickly

Much appreciated
I can tell dat if i stick wit u, my sql server skills sure will improve

noks
Starting Member

9 Posts

Posted - 2007-05-10 : 06:58:39

SELECT CustomerCode FROM (
SELECT Customercode, OrderDate, OrderNumber, ROW_NUMBER() OVER (PARTITION BY CustomerCode ORDER BY OrderDate DESC) AS RecID
from Customer,PurchaseOrder
) AS d , purchaseorder
WHERE RecID BETWEEN 1 AND 3

this is working perfectly fine but now i wanna see the orderdate as well but when i add it in the first select statement i get an error message sayin that orderdate is ambigious . please kindly help

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 07:32:55

Somthing like this ??


 SELECT * FROM 
(
	SELECT C.Customercode, O.OrderDate, O.OrderNumber, ROW_NUMBER() 
	OVER (
			PARTITION BY C.CustomerCode ORDER BY O.OrderDate DESC
		 ) AS RecID
	from Customer C Left Outer Join Keys K 
		on K.Cust_ID = C.Cust_ID 
	Left Outer Join Orders O 
	    on O.Order_ID = K.Order_ID
) AS d  
WHERE RecID BETWEEN 1 AND 3

Chirag

http://chirikworld.blogspot.com/

noks
Starting Member

9 Posts

Posted - 2007-05-10 : 08:16:54

quote:

Originally posted by chiragkhabaria

Somthing like this ??


 SELECT * FROM 
(
	SELECT C.Customercode, O.OrderDate, O.OrderNumber, ROW_NUMBER() 
	OVER (
			PARTITION BY C.CustomerCode ORDER BY O.OrderDate DESC
		 ) AS RecID
	from Customer C Left Outer Join Keys K 
		on K.Cust_ID = C.Cust_ID 
	Left Outer Join Orders O 
	    on O.Order_ID = K.Order_ID
) AS d  
WHERE RecID BETWEEN 1 AND 3

Chirag

http://chirikworld.blogspot.com/

works perfectly.

Thank u so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources