select bringing all un-duplicated records based on a single column

Asked

Viewed 1,019 times

6

How can I bring all records from a column that are not repeated. For example:

|   tela  |    url    |     perfil     |      dataCriacao    |
---------------------------------------------
|   dica  |   /dica/  |    ROLE_CDL    | 2019-07-05 09:00:00 |
|   dica  |   /dica/  | ROLE_MARKETING | 2019-07-05 09:00:00 |
| monitor | /monitor/ |    ROLE_CDL    | 2019-07-05 09:00:00 |

In this table as I could do a SELECT * FROM that would eliminate duplicate records from the column tela and to return to me:

|   tela  |    url    |     perfil     |      dataCriacao    |
--------------------------------------------------------------
|   dica  |   /dica/  |    ROLE_CDL    | 2019-07-05 09:00:00 |
| monitor | /monitor/ |    ROLE_CDL    | 2019-07-05 09:00:00 |

I have tried to perform a select distinct, but I was unsuccessful.

sql database query mysqli select-sql

  • select DISTINCT tela from sua_tabela didn’t work?

  • Tried the function ROW_NUMBER ? with CTE_RN as
(
 select 
 tela,
 url,
 perfil,
 dataCriacao,
 ROW_NUMBER() OVER(PARTITION BY tela ORDER BY tela DESC) as RN -- Função ROW_NUMBER CRIA UMA NUMERAÇÃO PARA CADA REC QUE SE REPETE 
 from Table_1
)

select * from CTE_RN 
where RN = 1

  • @Tmilitino it would work if I only wanted the screens so I could give select distinct tela from nomedatabela, but I need to get the whole registry. and not just the screen field.

  • if it works, then you can do select DISTINCT tela, url, perfil, dataCriacao from sua_tabela ai returns the rest of the fields

  • 1

    I can’t do that, because this is a JPA query, so I needed to return all the data right, for Binding to occur correctly and it already store as object, if I put field by JPA field returns me an array and then ends up breaking me. I guess I’ll have to remove the duplicates on the server.

  • understood now your scolding. it is good you put this in the question, because it is understood that it is a doubt only of sql and such, but it has much more involved.

  • The problem has been solved?

Show 2 more comments

2 answers

7


Try this.

SELECT * FROM nome_tabela
WHERE tela NOT IN (
    SELECT tela FROM nome_tabela
    GROUP BY tela
    HAVING Count(*) > 1
)

Or you can try it

SELECT tela FROM nome_tabela
        GROUP BY tela
        HAVING Count(*) = 1

0

Tried the ROW_NUMBER function ? 

  with CTE_RN as
 (
select 
    tela,
    url,
    perfil,
    dataCriacao,
     ROW_NUMBER() OVER(PARTITION BY tela ORDER BY tela DESC) as RN -- Função 
   ROW_NUMBER CRIA UMA NUMERAÇÃO PARA CADA REC QUE SE REPETE 
         from  Table_1
     )

  select * from CTE_RN 
   where RN = 1;

Browser other questions tagged sql database query mysqli select-sql

You are not signed in. Login or sign up in order to post.