Home / Questions / 267454
Accepted
Bryan Romero
Asked: 2020-05-29 03:49:08 +0800 CST 2020-05-29 03:49:08 +0800 CST

Query to return record with different value

  • 772

I'm trying to create a query to interrogate this table:

|---------------------|------------------|
|      Column1        |     Column2      |
|---------------------|------------------|
|          A          |        1         |
|---------------------|------------------|
|---------------------|------------------|
|          A          |        1         |
|---------------------|------------------|
|---------------------|------------------|
|          B          |        2         |
|---------------------|------------------|
|---------------------|------------------|
|          B          |        3         |
|---------------------|------------------|
|---------------------|------------------|
|          B          |        2         |
|---------------------|------------------|
|---------------------|------------------|
|          A          |        1         |
|---------------------|------------------|

The query in question has to return all the queries letrasthat column1contain a number with a different value:

in this case it has to regresarme la B, ya que esta tiene como valor 3, si B tuviera solo 2sas numbers it would not have to return anything. The A does not return since it has only 1S as numbers, that is, they are all the same.

This is what I've done so far:

SELECT distinct (column1)
FROM (SELECT b.numero_contratto FROM table b) column1
WHERE column1.periodo_date = to_date('2019-04-01', 'yyyy-mm-dd');
sql

1 Answers

  1. Best Answer

    I understand, that actually you should count the distinct values ​​not in b.numero_contrattobut in the column whose alias is columna2. Then we group by numero_contrattoand filter those whose quantity is greater than 1, withHAVING COUNT(...) > 1

      SELECT b.numero_contratto, 
      COUNT(DISTINCT <columna2>)
      FROM TIM_TRCELL_ANALITICO b
      WHERE periodo_date = to_date('2019-04-01', 'yyyy-mm-dd')
      HAVING COUNT(DISTINCT <columna2>) > 1
    • 2