select multiple columns group by 1 column

Question

select multiple columns group by 1 column

Navigation

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

1

I have a table with 300mil records and inside it there are several different fields, I would like to take the line that contains the largest DATAEMISSAAPURADA for each CPF_1

What I have:

ID          NUMERO                    DATAEMISSAOAPURADA      COD                     NOMEMUNICIPIORESIDENCIA                            UFRESIDENCIA CPF_1         CPF_2         FONTE               NOME                                                                         DATA                        DATA         
----------- ------------------------- ----------------------- ----------------------- -------------------------------------------------- ------------ ------------- ------------- ------------------- ---------------------------------------------------------------------------- --------------------------- ---------------------------
12345       0000000000000009116092003 2007-11-19 00:00:00     4200051                 Abdon Batista                                      SC           123           321           TB1                 To***********                                                                2012-10-28 00:00:00         NULL
78999       0000000000080697220782003 2007-11-09 00:00:00     4200051                 Abdon Batista                                      SC           123           321           TB1                 To***********                                                                2012-10-28 00:00:00         NULL
12347       C000000000000000000821022 2006-04-12 00:00:00     2302404                 Boa Viagem                                         CE           356           978           TB1                 JO**********************                                                     2011-09-27 00:00:00         NULL
12399       C489895345345350000881240 2007-06-11 00:00:00     2306405                 Itapipoca                                          CE           876           093           TB1                 HA*************************                                                  2009-11-16 00:00:00         NULL

Expected result:

ID          NUMERO                    DATAEMISSAOAPURADA      COD                     NOMEMUNICIPIORESIDENCIA                            UFRESIDENCIA CPF_1         CPF_2         FONTE               NOME                                                                         DATA                        DATA         
----------- ------------------------- ----------------------- ----------------------- -------------------------------------------------- ------------ ------------- ------------- ------------------- ---------------------------------------------------------------------------- --------------------------- ---------------------------
12345       0000000000000009116092003 2007-11-19 00:00:00     4200051                 Abdon Batista                                      SC           123           321           TB1                 To***********                                                                2012-10-28 00:00:00         NULL
12347       C000000000000000000821022 2006-04-12 00:00:00     2302404                 Boa Viagem                                         CE           356           978           TB1                 JO**********************                                                     2011-09-27 00:00:00         NULL
12399       C489895345345350000881240 2007-06-11 00:00:00     2306405                 Itapipoca                                          CE           876           093           TB1                 HA*************************                                                  2009-11-16 00:00:00         NULL

sql-server select

asked by anonymous 14.08.2017 / 20:01

2 answers

1

The proposed problem can be solved as follows. It is still possible to filter the select that gets Max (DATAEMISSAAPURADA)

SELECT
ID          
,NUMERO 
,DATAEMISSAOAPURADA      
,COD                     
,NOMEMUNICIPIORESIDENCIA                            
,UFRESIDENCIA 
,CPF_1         
,CPF_2         
,FONTE               
,NOME
,DATA                        
FROM tabela A
where ID in (select ID from tabela B where DATAEMISSAOAPURADA = (SELECT MAX(DATAEMISSAOAPURADA) from tabela))

14.08.2017 / 20:20

Decompose the value of a ballot in Python Filter Select elements with PHP

score 0 · Accepted Answer

Rate

-- código #1
with MaisRecente as (
SELECT colunas,
       Seq= row_number() over (partition by CPF_1 order by DATAEMISSAOAPURADA desc)
  from tabela
)
SELECT colunas
  from MaisRecente
  where Seq = 1;