Using Max in group by

Question

I have the following query and I want it to generate the result with just the latest date for the category for a store instead of giving out per date transaction:

SELECT c.store,d.node_name category, x.txn_dt, x.txn_tm time, count(c.txn_id) Buyer
FROM pos_swy.5_centerstore_triptype c
join pos_swy.3_txn_itm t on c.txn_id=t.txn_id
join pos_swy.1_upc_node_map d on t.upc_id=d.upc_id
join pos_swy.3_txn_hdr x on t.txn_id=x.txn_id
group by store,txn_dt,node_name;

I tried using max(x.txn_dt) but it really didn't solve the purpose.

possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Marc B, Jul 17 '13 at 14:25
you shouldn't group by field for which you are applying aggregate function — triclosan, Jul 17 '13 at 14:27

echo_Me · Accepted Answer · 2013-07-17T17:15:27.223

0

you may need the order by ?

    SELECT c.store,d.node_name category, max(x.txn_dt) max_date, x.txn_tm time, count(c.txn_id) Buyer
    FROM pos_swy.5_centerstore_triptype c
    join pos_swy.3_txn_itm t on c.txn_id=t.txn_id
    join pos_swy.1_upc_node_map d on t.upc_id=d.upc_id
    join pos_swy.3_txn_hdr x on t.txn_id=x.txn_id
    group by node_name
    order by max_date desc 

         -- you can change limit 1 to what ever you want to get results

edited Jul 17 '13 at 17:15

answered Jul 17 '13 at 14:26

echo_Me

37,078
5
58
78

Hey but I need to get only the max/current date for each category and not not repeat the categories for per date. It just needs to display the most recent date only. – user2562256 Jul 17 '13 at 15:15
its difficult to understand like that can you provide fiddle example ? – echo_Me Jul 17 '13 at 15:28
so the query you gave me gives the result with the most recent date and goes to the last date. So data is generated for each day. But I want: c.store, d.node_name, max(x.txn_dt), time, count where x.txn_dt should be containing one value for each category and that value should be the most recent one – user2562256 Jul 17 '13 at 15:32
Well it still gives me all the dates in the descending order – user2562256 Jul 17 '13 at 15:51
yeah the only latest date that category was purchased – user2562256 Jul 17 '13 at 16:06
that returns only one row :( – user2562256 Jul 17 '13 at 17:11
you want every category , one max date ? then try this edited answer – echo_Me Jul 17 '13 at 17:15

Using Max in group by

1 Answers1