Issue with query - 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)

Issue with query

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2014-03-17 : 10:42:09

Hi
Could you help me with below?

Write a query that returns all orders placed
-- by the customer(s) who placed the highest number of orders
-- * Note: there may be more than one customer
-- with the same number of orders
Orders table

-- Desired output:
custid orderid orderdate empid
----------- ----------- ----------------------- -----------
71 10324 2006-10-08 00:00:00.000 9
71 10393 2006-12-25 00:00:00.000 1
71 10398 2006-12-30 00:00:00.000 2
71 10440 2007-02-10 00:00:00.000 4

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-17 : 11:05:27

[code]

;with aCTE
AS
(select 71 as CustID,10324 as OrderID,'2006-10-08' as OrderDate,9 as EmpID union all
select 71,10393,'2006-12-25', 1 union all
select 71,10398,'2006-12-30', 2 union all
select 71,10440,'2007-02-10', 4 union all

select 72,12393,'2006-12-25', 1 union all
select 72,12393,'2006-12-25', 1 union all
select 72,12398,'2006-12-30', 2 union all
select 72,12440,'2007-02-10', 4 union all

select 70,11111,'2008-02-02',3)

select *
from aCTE as A
inner join
(select top 1 WITH TIES
CustID, count(*) as noOrders
from aCTE
group by CustID
order by 2 desc ) as C
on A.CustID=C.CustID

[/code]

the key code is With Ties

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources