case in group by in a store procedure

Question

is possible do a case in a group by? similar to this:

select * from table

    GROUP BY 
      CASE WHEN @Attivita=0 THEN (RANK() OVER (GROUP BY Nome,AccountID,Matricola DESC))   

     END

thanks

Answer 1

you have to group by all selected (non-aggregated) columns..

so if you select * you will need to group by all of them ...

If instead of group by you mean order by then yes you can..

Answer 2

No: this would make no sense.

You can't

• use GROUP BY and SELECT *
• use RANK in a subclause
• use GROUP BY in the OVER clause
• GROUP BY over a ranking function makes no sense

What are you trying to do, with input/output and schema please.

Edit, based on gaby's answer

select
   *
from
    (
    SELECT
        *, RANK() OVER (GROUP BY Nome,AccountID,Matricola DESC) as bar
    from
        table
    ) foo
ORDER BY
   CASE WHEN @Attivita=0 THEN bar END