Sql Query with unique rows

Here is a some sample table data


CustomerID    FirstName       LAstName     city             State    Zip            OrderDate         OrderTotal
1                        John                 Doe                Chicago     IL          60067       05/20/2015        150
1                        John                 Doe                Chicago     IL          60067       05/03/2015        120
1                        John                 Doe                Chicago     IL          60067       07/03/2015        200
2                        John                Smith              Chicago     IL          60067       05/20/2015        150
2                        John                Smith              Chicago     IL          60067       05/03/2015        120
3                        John                Jones              Chicago     IL          60067       07/03/2015        200
I am trying to write a query that will take into account if a customer ID occurs more than once and only return one row for the customer that has the highest order total

Using Above sample I would want

CustomerID    FirstName       LAstName     city             State    Zip            OrderDate         OrderTotal
1                        John                 Doe                Chicago     IL          60067       07/03/2015        200
2                        John                Smith              Chicago     IL          60067       05/20/2015        150
3                        John                Jones              Chicago     IL          60067       07/03/2015        200
johnnyg123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Knee jerk reaction> Subquery for CustomerID and Max(OrderTotal), then join on main table using CustomerID and max OrderTotal
SELECT yt.CustomerID, yt.FirstName, yt.LAstName, yt.city, yt.State, yt.Zip, yt.OrderDate, yt.OrderTotal
FROM YourTable yt
   JOIN (SELECT CustomerID, Max(OrderTotal) OrderTotalMax FROM YourTable GROUP BY CustomerID) 
      ytmax ON yt.CustomerID = ytmax.CustomerID AND yt.OrderTotal = ytmax.OrderTotalMax

Open in new window

 See SQL Server GROUP BY Solutions for more
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
Perfect....Thanks!
0
Brian CroweDatabase AdministratorCommented:
WITH cteOrder AS
(
   SELECT CustomerID, FirstName, LastName, City, State, Zip, OrderDate, OrderTotal,
      ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RowNumber
   FROM myTable
)
SELECT *
FROM cteOrder
WHERE RowNumber = 1
1
PortletPaulfreelancerCommented:
I thoroughly recommend the use of ROW_NUMBER() for this style of need.

It is excellent in performance terms when compared to other methods as it identifies the WHOLE ROW for the wanted criteria (e.g. "highest value" or "most recent" etc.) and it does this in a single pass of the data.

slight variation to Brian's is to just use a simple derived table instead of CTE:

SELECT *
FROM (
   SELECT CustomerID, FirstName, LastName, City, State, Zip, OrderDate, OrderTotal,
      ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RN
   FROM YourTable
   ) as D
WHERE RN = 1

By the way, using row_number will always only produce a single row output too. If the data has a "tie" like this:

    (1, 'John', 'Doe', 'Chicago', 'IL', 60067, '2015-05-20 00:00:00', 150),
    (1, 'John', 'Doe', 'Chicago', 'IL', 60067, '2015-05-03 00:00:00', 120),
    (1, 'John', 'Doe', 'Chicago', 'IL', 60067, '2015-07-03 00:00:00', 200),
    (1, 'John', 'Doe', 'Chicago', 'IL', 60067, '2015-07-03 00:00:00', 200),


then a row_number() approach would still return a single row, whereas using a group by/join would return both those bold rows for that person. So if you really want one row only, use row_number(0 but of you need all the rows with the "highest order total" then use a group by/join approach.

Try them out here: http://sqlfiddle.com/#!6/4f857/1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.