Select record with the highest value in a field

Question

Select record with the highest value in a field

Navigation

#1 by (1 votes)

2

I am making a query in SQLServer where I require the record of days worked of x number of employees.

On the days when they are at Rest, they can work obtaining the additional day.

The query I have generates the following

id  cliente puesto  empleado    anno    mes dia turno
1   ACME    PUERTA  CORRECAMINOS    2016    6   1   001
1   ACME    PUERTA  CORRECAMINOS    2016    6   2   DESCANSO
1   ACME    BODEGA  CORRECAMINOS    2016    6   3   001
1   ACME    BODEGA  CORRECAMINOS    2016    6   4   001
1   ACME    PUERTA  CORRECAMINOS    2016    6   5   001
1   ACME    BODEGA  CORRECAMINOS    2016    6   6   001
1   ACME    BODEGA  CORRECAMINOS    2016    6   7   001
1   ACME    PUERTA  CORRECAMINOS    2016    6   8   001
1   ACME    BODEGA  CORRECAMINOS    2016    6   9   DESCANSO
2   ACME    BODEGA  CORRECAMINOS    2016    6   9   001 EXTRA
1   ACME    BODEGA  CORRECAMINOS    2016    6   10  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   11  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   12  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   13  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   14  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   15  DESCANSO

On the 9th he was resting but he worked the day as an extra shift, what I need is that I do not get duplicated on day 9 but show only the record with the extra shift.

My query is something like this:

select id_registro, cliente,  puesto, empleado,
DATEPART(YEAR,fecha) anno, DATEPART(MONTH,fecha) mes, 
DATEPART(DAY,fecha) dia, turno
from programacion
where DATEPART(YEAR,fecha)=2016 and DATEPART(MONTH,fecha)=6

The result I need would be:

id  cliente puesto  empleado    anno    mes dia turno
1   ACME    PUERTA  CORRECAMINOS    2016    6   1   001
1   ACME    PUERTA  CORRECAMINOS    2016    6   2   DESCANSO
1   ACME    BODEGA  CORRECAMINOS    2016    6   3   001
1   ACME    BODEGA  CORRECAMINOS    2016    6   4   001
1   ACME    PUERTA  CORRECAMINOS    2016    6   5   001
1   ACME    BODEGA  CORRECAMINOS    2016    6   6   001
1   ACME    BODEGA  CORRECAMINOS    2016    6   7   001
1   ACME    PUERTA  CORRECAMINOS    2016    6   8   001
2   ACME    BODEGA  CORRECAMINOS    2016    6   9   001 EXTRA
1   ACME    BODEGA  CORRECAMINOS    2016    6   10  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   11  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   12  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   13  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   14  001
1   ACME    BODEGA  CORRECAMINOS    2016    6   15  DESCANSO

I tried MAX (id) but it does not work.

sql sql-server

asked by EunSoo 13.06.2016 в 20:00

source

1 answer

Pass java objects to stored procedure in oracle Problems with getting files created in Swift 2

score 1 · Accepted Answer

You can use a CTE:

WITH ProgramacionOrdered ([id_registro], cliente, puesto, empleado, anno, mes, dia, turno, num)
AS (SELECT 
    id_registro, cliente, puesto, empleado, DATEPART(YEAR, fecha), DATEPART(MONTH, fecha), DATEPART(DAY, fecha),
    turno, ROW_NUMBER() OVER (PARTITION BY cliente, puesto, empleado, fecha ORDER BY id_registro DESC)
FROM programacion)
SELECT id_registro, cliente, puesto, empleado, anno, mes, dia, turno
FROM ProgramacionOrdered WHERE num=1