1

Consider below table tbl:

id    date
1     2016
1     2017
2     2017
2     2017
3     2016
3     2017
4     2018
5     2018

How can I get only rows with same id but different date.

Right now I can only get ids with more than 1 count like so:

select id, date
from tbl
group by id
having count(id)>1

How can I only get id 1 and 3 along with date?

  • 1
    I hope the table has more columns and a UNIQUE constraint defined and it's not just what you show here ;) – ypercubeᵀᴹ Apr 21 '18 at 16:35
6

Use an AGGREGATE function like MIN or MAX.

If you only want one record per ID, you have to choose which date you want. The first (MIN) or the latest (MAX).

https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-2017

^^Above information is not what was requested.

Edited per clarification in comments: Just use the HAVING to identify which ID values have more than one date, then apply that to a new SELECT.

SELECT id, Date
FROM tbl
WHERE id IN
    (
        select id 
        from tbl
        group by id
        having count(distinct date)>1
    )
  • 1
    I want both the rows. I idea is to get all the ids with same number but with different dates. – MYGz Apr 21 '18 at 12:51
  • Understood, edited answer accordingly. – MguerraTorres Apr 21 '18 at 13:00
  • 1
    Thanks. This is much easier to understand. – MYGz Apr 21 '18 at 13:05
5

A few more ways to get the same result:

First, using min(date) < max(date) as the HAVING condition - instead of the count(distinct date) > 1. Perhaps slightly more efficient:

select id, date
from tbl
where id in 
      ( select id
        from tbl
        group by id
        having min(date) < max(date)
      ) ;

or with a JOIN:

select t.id, t.date
from tbl as t
     join 
      ( select id
        from tbl
        group by id
        having min(date) < max(date)
      ) as g
      on g.id = t.id ;

and one converting the IN to a correlated EXISTS subquery. Bonus, we can remove the GROUP BY:

select id, date
from tbl as t
where exists
      ( select 1
        from tbl as t2
        where t.id = t2.id
          and t.date <> t2.date
      ) ;

Another that uses window functions:

select id, date
from
  ( select id, date,
           diff_dates = case when min(date) over (partition by id)
                                < max(date) over (partition by id)
                             then 1
                        end 
    from tbl
  ) as d
where diff_dates = 1 ;

and last, one for the obfuscation contest:

select id, date from tbl
except
select id, min(date) from tbl group by id
intersect
select id, max(date) from tbl group by id ;

Test in dbfiffle.

2

You should count , as you did, and count distinct per date

SELECT T.id,T.date
FROM Table1 AS T
CROSS APPLY
  (SELECT C.id 
          ,Count(id) as count_id
          ,Count(Distinct date) as count_Distinct_Records
      FROM Table1 AS C
      WHERE C.id = T.id
      GROUP BY id) AS CA
WHERE
  CA.count_id > 1
  AND count_Distinct_Records > 1

output here:

id  date
1   2016
1   2017
3   2016
3   2017

dbfiddle

-2

enter image description here

select id,date from table2 group by id,date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.