CODEWITHSUNDEEP
sqlsql-server-2000
suely
suely

Reputation: 434

Return top 3 rows per group

When I made this select it shows as result all “rut” with it phone numbers, my problem is that each “rut” has almost 10 phone numbers, and I need just 3 phone numbers for each “rut”, I tryied using TOP, but just shows the first 3 rows of all table and not the first rows by “rut”, how can I use TOP just for the row “rut” and not in the all the table

Select Distinct  
t1.rut_cliente as rut_cliente,
t1.nro_fono  as numero 
--Into #tmp_numeros
From dat_clientes_sucursales_contactos_telefonos t1,dat_rut_clientes t2
where  t1.rut_cliente = t2.rut_cliente 
and cod_prioridad = 1

this is what I get with this query: 

  Rut_cliente   Nro_fono 
  60506000-5    2046840
  60506000-5    3507935
  60506000-5    4106886
  60506000-5    5440000
  60506000-5    5445000
  81698900-0    2373281
  81698900-0    3541342
  81698900-0    3541438
  81698900-0    3541518
  81698900-0    3542101

and this is what I want: 

  Rut_cliente   Nro_fono 
  60506000-5    2046840
  60506000-5    3507935
  60506000-5    4106886
  81698900-0    2373281
  81698900-0    3541342
  81698900-0    3541438

thanks in advance.

Upvotes: 0

Views: 1203

Answers (3)

anon
anon

Reputation:

The question was originally tagged SQL Server 2008, where you can do this using a common table expression:

;WITH x AS 
 (
   SELECT Rut_cliente, Nro_fono, rn = ROW_NUMBER()
     OVER (PARTITION BY Rut_cliente ORDER BY Nro_fono)
   FROM dbo.dat_clientes_sucursales_contactos_telefonos AS t1
   INNER JOIN dbo.dat_rut_clientes AS t2
   ON t1.rut_cliente = t2.rut_cliente
   WHERE cod_prioridad = 1
)
SELECT Rut_cliente, Nro_fono FROM x
WHERE rn <= 3
ORDER BY Rut_cliente, Nro_fono;

Other comments:

  • Please don't use table, table syntax. Use proper INNER JOINs. I explain why here.
  • Please add proper aliases to the inner query, so people know which columns come from t1 and which columns come from t2.

But now we learn the user is actually using SQL Server 2000. This is how I would do it there, I think, but performance is going to be horrible. I'm not 100% sure this works (because again I'm making guesses about which columns come from which table).

SELECT x.rut_cliente, x.nro_fono, COUNT(*) FROM 
(
  SELECT t1.rut_cliente, t1.nro_fono
    FROM dat_clientes_sucursales_contactos_telefonos AS t1
    INNER JOIN dat_rut_clientes AS t2
    ON t1.rut_cliente = t2.rut_cliente
    WHERE cod_prioridad = 1
) AS x
INNER JOIN dat_clientes_sucursales_contactos_telefonos AS b
ON b.rut_cliente = x.rut_cliente
AND b.nro_fono <= x.nro_fono
GROUP BY x.rut_cliente, x.nro_fono
HAVING COUNT(*) <= 3
ORDER BY x.rut_cliente, x.Nro_fono;

Upvotes: 7

rs.
rs.

Reputation: 27427

Try this

;with CTE AS (
select Rut_cliente, Nro_fono , ROW_NUMBER() OVER 
(PARTITION BY Rut_cliente ORDER BY Nro_fono) rn
 FROM dbo.dat_clientes_sucursales_contactos_telefonos AS a
INNER JOIN dbo.dat_rut_clientes AS b
ON a.rut_cliente = b.rut_cliente
WHERE cod_prioridad = 1

)
SELECT * FROM CTE where rn <=3

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460128

For example with ROW_NUMBER which is a window function and returns a row-number for each partition(similar to group by) determined by the order by.:

WITH CTE AS(
   SELECT t1.rut_cliente as rut_cliente, t1.nro_fono  as numero,
      RN = ROW_NUMBER()OVER(PARTITION BY Rut_cliente ORDER BY Nro_fono)
   FROM dbo.dat_clientes_sucursales_contactos_telefonos AS t1
   INNER JOIN dbo.dat_rut_clientes AS t2 ON t1.rut_cliente = t2.rut_cliente
   WHERE cod_prioridad = 1
)
SELECT rut_cliente as rut_cliente, nro_fono  as numero
FROM CTE
WHERE RN <= 3

Upvotes: 5

Related Questions