Remove the first name from each SQL SERVER office-box

Navigation
0

Good, I have a little problem to see if you can help me.

I start from the following query 

SELECT Caja, Oficina, DNI, NombreCompleto, Importe
FROM miTabla
WHERE Importe > 50 and Caja LIKE '0%'

What they ask me now is to group it so that it takes the first full name of each box-office The query would return those three fields: Box / office / full name (the first)

So what I've done is: 

SELECT Caja, Oficina, NombreCompleto
FROM(
     SELECT Caja, Oficina, DNI, NombreCompleto, Importe
     FROM movimientos
     WHERE Importe > 50 and Caja LIKE '0%') as miTabla
GROUP BY Caja, Oficina, NombreCompleto

But he takes out all the records and I just want the first of each box and office alphabetically speaking.

    
asked by Miguel 01.02.2018 в 12:41
source

2 answers

1

What you need is the added function Min () Returns the lowest value (the first one alphabetically speaking and starting from 0 if they are characters), under the groupings of Caja and Oficina , in the following way: 

SELECT Caja, Oficina, Min(NombreCompleto)
FROM movimientos
WHERE Importe > 50 and Caja LIKE '0%'
GROUP BY Caja, Oficina
    
answered by 01.02.2018 / 13:13
source
0

You just have to sort by Full Name and say you just want a record. In SQL server it should be something like this: 

SELECT TOP 1 Caja, Oficina, NombreCompleto
FROM(
     SELECT Caja, Oficina, DNI, NombreCompleto, Importe
     FROM movimientos
     WHERE Importe > 50 and Caja LIKE '0%') as miTabla
GROUP BY Caja, Oficina, NombreCompleto
ORDER BY NomberCompleto ASC
    
answered by 01.02.2018 в 12:45
Visitor / user permissions Screenshot with canvas