How to see if last five records have same value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to see if last five records have same value?

Author

Topic

Hammerklavier
Starting Member

26 Posts

Posted - 2015-01-06 : 15:01:01

Hi,

Suppose I have a table called Order that stores an OrderID, CustomerID, OrderType and OrderDate. How could I write a query to return a list of customers whose five most recent orders (based on the OrderDate) feature the same OrderType?

If only four of the five most recent for a given customer feature the OrderType, then none of them would be returned.

I greatly appreciate any assistance!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-06 : 16:17:24

[code]
select *
from order
where OrderDate in (
select top(5) OrderDate
from Order
order by OrderDate desc
where OrderDate is not null
)
[/code]

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2015-01-06 : 16:26:59

[code]
select
d.customerid,
d.orderID,
d.ordertype
from
(
select
*,
row_number() over (partition by customerID, orderID, orderType order by orderDate desc) as ct
from @order
) d
where
d.ct = 5
group by
d.customerid,
d.orderID,
d.ordertype
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-01-07 : 07:53:02

[code]-- SwePeso
SELECT c.CustomerID,
MIN(f.OrderType) AS OrderType
FROM (
SELECT CustomerID
FROM dbo.[Order]
GROUP BY CustomerID
HAVING COUNT(*) >= 5
) AS c
CROSS APPLY (
SELECT TOP(5) o.OrderType
FROM dbo.[Order] AS o
WHERE o.CustomerID = c.CustomerID
ORDER BY o.OrderDate DESC
) AS f
GROUP BY c.CustomerID
HAVING MIN(f.OrderType) = MAX(f.OrderType);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Hammerklavier
Starting Member

26 Posts

Posted - 2015-01-07 : 12:52:59

Thank you for the responses.

SwePeso, your query did the trick!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-01-08 : 03:49:31

You can find some other methods too at http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources