USE MAX and CASE at the same time SQLSERVER

Question

USE MAX and CASE at the same time SQLSERVER

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (-1 votes)

-1

Good morning, I created a simple database in SQLSERVER, which has repeated employees of different ages, plus an abbreviation to see if I was single or married. What I want is for me to show the maximum age per employee, I know it is not correct, but I only did it out of curiosity, since I had not had that consultation before.

The query used is:

 SELECT nom_emp,MAX(edad_emp) 'Edad',(CASE estado_civi 
                                        WHEN 'C' THEN 'Casado'
                                        ELSE 'Soltero' 
                                        END 
                                        ) 'Estado Civil'
 FROM empleado a
 GROUP BY nom_emp,estado_civi

As you can see, it forces me to group by marital status, which means that the record of the employee with the highest age does not necessarily appear but can be repeated.

Please, if you could answer this question, thank you very much.

sql sql-server

asked by Antony Alza Perez 16.12.2017 в 23:05

source

3 answers

Laravel error please help Save word template on server

score 1 · Answer 1

First of all, I imagine you are clear that a table of employees that seems to be a teacher with several records for the same employee is a model inconsistent with reality. Obviating this, I commented that the aggregation functions, in this case MAX() work on the groups, that is, you will always return the maximum value of the grouped records, in your case: nom_emp and estado_civi . If you are looking for the maximum value at the level of nom_emp , you can make a subquery in the following way:

SELECT  E1.nom_emp,
        E2.Edad,
        (CASE E1.estado_civi 
              WHEN 'C' THEN 'Casado'
              ELSE 'Soltero' 
        END) 'Estado Civil'
FROM empleado E1
INNER JOIN (SELECT nom_emp, MAX(edad_emp) 'Edad'
        FROM empleado
        GROUP BY nom_emp
    ) E2
    ON E2.nom_emp = E1.nom_emp
GROUP BY E1.nom_emp,
    E1.estado_civi,
    E2.Edad

As you can see, the INNER JOIN points to a query grouped only by nom_emp and where we recover the maximum that you are waiting for.

score 0 · Answer 2

It would have been better to use an example that makes a little more sense.

If you only want to reach the maximum age by value nom_emp , a simple GROUP BY nom_emp + MAX(edad_emp) is enough.

But if you wish to filter and return only the records that correspond to the maximum age by grouping nom_emp , but including other values such as estado civil of those records, then the most efficient way to do it is using the window function ROW_NUMBER() :

select nom_emp,
       edad_emp as edad,
       case estado_civi
            when 'C' then 'Casado'
                     else 'Soltero' 
       end as [Estado Civil]
  from (select *,
               row_number() over (
                 partition by nom_emp
                     order by edad_emp desc) as rn
          from empleado) t
 where rn = 1

Another possible interpretation of your question is that you want to see all the records without a filter, but including in each record the maximum age for the grouping of nom_emp . In this case, the most direct way is to use the window function MAX() :

select nom_emp,
       max(edad_emp) over (partition by nom_emp) as edad,
       case estado_civi
            when 'C' then 'Casado'
                     else 'Soltero' 
       end as [Estado Civil]
  from empleado

score -1 · Answer 3

You have many ways to make the query you want, to me, so first of all it occurs to me:

SELECT nom_emp,edad_emp,(CASE estado_civi 
                                    WHEN 'C' THEN 'Casado'
                                    ELSE 'Soltero' 
                                    END 
                                    ) 'Estado Civil'
FROM empleado a
GROUP BY nom_emp,estado_civi
HAVING MAX(edad_emp)

Check also that case, which I think is wrong.