Calculation max dates

Question

I need to return two max dates from two different columns within the same table. I want the result to be in the same line. This is my table data:

If i have this          
store   item    tran-code   date
1788    2004635    1      17.05.27
1788    2004635    2      17.05.27
1788    2004635   30      17.05.26
1788    2004635    2      17.05.21
1788    2004635    1      17.05.21
1788    2004635    2      17.05.20
1788    2004635    1      17.05.20

and i want this…

store   item    tran-code   date
1788    2004635    1      17.05.27
1788    2004635    30     17.05.26

but if you could get me to return this, it will be perfect

store   item      date_1    date_30
1788    2004635  17.05.27   17.05.26

where date_1 is the max date for tran-code = 1 and date_30 is the max date for tran-code = 30

but what database engine are you using?, Oracle, SQL Server, Postgresql, mysql...? — Lamak, May 29 '17 at 17:41

score 2 · Answer 1 · edited May 19 '20 at 04:44

2

Use this approach to get the TOP N rows for each partition:

SELECT store, item, tran_code, date1 FROM 
(
    SELECT a.*, ROW_NUMBER() OVER (PARTITION BY tran_code ORDER BY date1 DESC) rnk FROM tran a
)
WHERE tran_code IN (1,30) AND rnk = 1

Another similar use case is here

edited May 19 '20 at 04:44

Fer R

141
2
9

answered May 29 '17 at 18:58

praveen

75
1
2

ScaisEdge · Accepted Answer · 2017-05-30T05:04:14.227

1

you could use

select a.store, a.item, max(a.date) as date_1, t.date_30 
from my_table a 
inner join (
select store, item, max(date) as date_30
from my_table a 
where a.trans-code = 30 
group by store, item
) t on a.store = t.store and a.item = t.item 
group by a.store, a.item, t.date_30

edited May 30 '17 at 05:04

answered May 29 '17 at 17:57

ScaisEdge

131,976
10
91
107

i've tried this and gives error ORA-00937: "not a single-group group function". line 1 column 8. don't know what i'm doing wrong... – tech_guy May 29 '17 at 19:21
still error, ORA-00979: "not a GROUP BY expression" on line 1 column 52. column 52 matches t.date_30 – tech_guy May 29 '17 at 19:44

Utsav · Answer 3 · 2017-05-30T00:16:56.787

1

If you are selecting only 1 set if store and item, then you can use this. But if you add more store and item, then use join like @scaisEdge's answer.

select distinct store,item,
(select max(date) from table1 where tran-code=1) as date_1 
,(select max(date) from table1 where tran-code=30) as date_30
from table1;

edited May 30 '17 at 00:16

answered May 29 '17 at 17:57

Utsav

7,914
2
17
38

i've tried your sugestion and return error: ORA-00923: 00923. 00000 - "FROM keyword not found where expected" – tech_guy May 29 '17 at 19:14
I put from clause after where, hence the error. Updated. Check now – Utsav May 30 '17 at 00:18

Calculation max dates

3 Answers3