CODEWITHSUNDEEP
sqloracle-database
Marco_dev
Marco_dev

Reputation: 53

Query : Group By one column and select antoher column

I would have a problem with a query

I should make a query that takes the last state ( so check the date), Grouped for a called column mat_calc.

mat_calc | STATE | DATE

1 | NEW | 25/03/2016

1 | DONE |25/01/2016

2 |PROC |25/04/2016

2 |PROC |25/07/2016

2 |DONE |25/09/2016

3 |NEW |25/01/2016

3 |PROC |25/06/2016

3 |DONE |25/02/2016

3 |OK |25/12/2016

4 |OK |25/03/2016

So I should give it back :

the mat_cal With its status

1 | NEW

2 | DONE

3 | OK

4 | OK

My query is

select mat_cal AS mat_cal , STATO AS STATO, MAX(DATA) AS DATA
from CALC
group by mat_cal ;

It gives me trouble on the group id because it looks like I DO NOT use it.

How can i do it? Thanks

Sorry,i can't do a tables with stack overflow

Upvotes: 0

Views: 64

Answers (3)

MagdielAybar
MagdielAybar

Reputation: 187

When you use the Group By clause you need to do it with all the columns selected. If you don't do that you'll get the ORA-00979 exception.

Try adding the STATO column to the Group By.

   select mat_cal AS mat_cal , STATO AS STATO, MAX(DATA) AS DATA
    from CALC
    group by mat_cal, STATO ;

Upvotes: 0

Elad L.
Elad L.

Reputation: 639

you can try the following:

--creating the data you publishied
 with calc (mat_cal,STATO,date)
 as 
 (
 select '1' as mat_cal,'NEW' as STATO,'25/03/2016' as date
 union
 select '1','DONE','25/01/2016'
 union
 select '2','PROC','25/04/2016'
 union
 select '2','PROC','25/07/2016'
 union 
 select '2','DONE','25/09/2016'
 union 
 select '3','NEW','25/01/2016'
 union 
 select '3','PROC','25/06/2016'
 union 
 select '3','DONE','25/02/2016'
 union 
 select '3','OK','25/12/2016'
 union 
 select '4','OK','25/03/2016')


--the query to solve the problem 
select mat_cal , STATO ,date
from CALC as c
where c.date = (select max(date) from calc as c2 where c.mat_cal = c2.mat_cal group by c2.mat_cal)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use row_number():

select c.*
from (select c.*,
             row_number() over (partition by mat_cal order by data desc) as seqnum
      from calc c
     ) c
where seqnum = 1;

Upvotes: 1

Related Questions