SQL query select record with Max

Question

I have these records below :

CustomerID | Name | Store | Quantity  
1          | Elie |   HO  |    16    
1          | Elie |   S1  |    4

I would like to filter customers by taking only their max quantity? I tried it with Max, but the problem I cannot render all the fields with it. If I add main.store in the first line, the second row shows. Is there any solution?

Select main.CUSTOMER_ID, main.Name
from
(
    Select Name = cus.FIRST_NAME + ' ' + cus.LAST_NAME, 
           Store = cs.NAME
           ,Transaction_Number = count(ts.TRANSACTION_SUMMARY_ID) 
           ,cus.CUSTOMER_ID
    from TRANSACTION_SUMMARY ts
    inner join dbo.CUSTOMER cus 
        on ts.CUSTOMER_ID = cus.CUSTOMER_ID
    inner join dbo.CORPORATE_STORE cs 
        on ts.CORPORATE_STORE_ID = cs.CORPORATE_STORE_ID
    Group by cus.CUSTOMER_ID
        ,cus.FIRST_NAME
        ,cus.LAST_NAME
        ,cs.Name
) as main
Group by CUSTOMER_ID
    ,main.Name
order by main.CUSTOMER_ID

What should be the answer?

TechDo
– TechDo

2014-07-21 11:22:48 +00:00
Commented Jul 21, 2014 at 11:22 — TechDo
– TechDo, Commented Jul 21, 2014 at 11:22
just the first row @TechDo

Samidjo
– Samidjo

2014-07-21 11:25:37 +00:00
Commented Jul 21, 2014 at 11:25 — Samidjo
– Samidjo, Commented Jul 21, 2014 at 11:25

Gordon Linoff · Accepted Answer · 2014-07-21 11:33:26Z

This is a good use of window functions:

with t as (
      Select Name = cus.FIRST_NAME + ' ' + cus.LAST_NAME, 
             Store = cs.NAME,  
             Transaction_Number = count(ts.TRANSACTION_SUMMARY_ID) , cus.CUSTOMER_ID
      from TRANSACTION_SUMMARY ts
      inner join dbo.CUSTOMER cus on ts.CUSTOMER_ID = cus.CUSTOMER_ID
      inner join dbo.CORPORATE_STORE cs on ts.CORPORATE_STORE_ID = cs.CORPORATE_STORE_ID
      Group by cus.CUSTOMER_ID, cus.FIRST_NAME, cus.LAST_NAME, cs.Name
     )
select name, store, Transaction_Number, CUSTOMER_ID
from (select t.*,
             row_number() over (partition by customer_id order by transaction_number desc) as seqnum
      from t
     ) t
where seqnum = 1;

You can actually dispense with the subquery. However, using window functions with aggregations looks funny at first:

with t as (
      Select Name = cus.FIRST_NAME + ' ' + cus.LAST_NAME, 
             Store = cs.NAME,  
             Transaction_Number = count(ts.TRANSACTION_SUMMARY_ID) , cus.CUSTOMER_ID,
             row_number() over (partition by cus.CUSTOMER_ID
                                order by count(ts.TRANSACTION_SUMMARY_ID) desc
                               ) as seqnum
      from TRANSACTION_SUMMARY ts
      inner join dbo.CUSTOMER cus on ts.CUSTOMER_ID = cus.CUSTOMER_ID
      inner join dbo.CORPORATE_STORE cs on ts.CORPORATE_STORE_ID = cs.CORPORATE_STORE_ID
      Group by cus.CUSTOMER_ID, cus.FIRST_NAME, cus.LAST_NAME, cs.Name
     )
select name, store, Transaction_Number, CUSTOMER_ID
from t
where seqnum = 1;

TechDo · Accepted Answer · 2014-07-21 11:28:35Z

2

Please try:

select * From tbl a
where a.Quantity=
    (select MAX(b.Quantity) from tbl b where a.CustomerID=b.CustomerID)

answered Jul 21, 2014 at 11:28

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Comments

Randy · Accepted Answer · 2014-07-21 11:28:33Z

1

what you want is

select customer_id, max( quantity ) 
from main
group by customer_id

then you can use this to join to itself if you want

select * 
from main 
, (
select customer_id, max( quantity ) qty
from main
group by customer_id
) m
where main.customer_id = m.customer_id
and main.quantity = m.qty

answered Jul 21, 2014 at 11:28

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Comments

Strawberry · Accepted Answer · 2014-07-21 11:28:26Z

0

Obviously, name has no business being in this table, but you included it, so I did too...

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT customerid
            , name
            , MAX(quantity) max_quantity 
         FROM my_table 
        GROUP 
           BY customerid
            , name
     ) y 
    ON y.customerid = x.customerid 
   AND y.name = x.name 
   AND y.max_quantity = x.quantity;

answered Jul 21, 2014 at 11:28

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

SQL query select record with Max

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related