Sql query

Transact-SQL
1

there is a table with below 3 columns:

Could you please help me to find which id is same with NAME& record
ex:15302411 ,15420719
this is sample table we have 3000 different NAMES .

id NAME record
09160436 lang.ja 1
09160436 useragent.os.windows.browser.gecko..firefox.35 1
15231542 useragent.os.windows.browser.gecko..firefox.35 2
15231542 lang.en 2
15302411 useragent.os.windows.browser.gecko..firefox.35 1
15302411 lang.en 1
15331141 lang.en 1
15331141 useragent.os.windows.browser.gecko..firefox.35 1
15420719 lang.en 1
15420719 useragent.os.windows.browser.msie.9 1
15450671 useragent.os.windows.browser.gecko..firefox.34 2
15450671 lang.en 2
15471735 useragent.os.windows.browser.chrome.22.0.1229.79 1
15471735 lang.en 1
15591088 useragent.os.windows.browser.gecko..firefox.37 1
15591088 lang.en 1
16030166 useragent.os.windows.browser.msie.9 1
16030166 lang.en 1
16130420 useragent.os.windows.browser.gecko..firefox.37 1
16130420 lang.en 1
16450199 lang.en 1
16450199 useragent.os.windows.browser.gecko..firefox.36 1
17140238 useragent.os.windows.browser.chrome.40.0.2214.93 1
17140238 lang.en 1
18090279 lang.en 2
18090279 useragent.os.windows.browser.chrome.40.0.2214.115 2
18310116 lang.en 1
18310116 useragent.os.windows.browser.chrome.41.0.2272.101 1
18350027 lang.en 1
18350027 useragent.os.macintosh.browser.chrome.41.0.2272.118 1
21220766 useragent.os.windows.browser.gecko..firefox.29 1
21220766 lang.en 1
21261087 lang.en 1
21261087 useragent.os.windows.browser.gecko..firefox.33 1
22111199 lang.en 1
22111199 useragent.os.windows.browser.chrome.42.0.2311.90 1
22390653 useragent.os.windows.browser.chrome.40.0.2214.115 1
22390653 lang.en 1

3

select id
from YourTable
group by Name, record
having count(*) > 1

4

Thanks for you help but

Column 'T_OCSM_1.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Getting error

5

select id from
(
select t1.id,t1.name,t2.name as lang,t1.record from
(
select * from YOURTABLE
where left(name,5)<>'lang.'
)t1
left join
(
select * from YOURTABLE
where left(name,5)='lang.'
)t2
on t1.id=t2.id
)tab

join

(
select name,lang,record from
(
select t1.id,t1.name,t2.name as lang,t1.record from
(
select * from YOURTABLE
where left(name,5)<>'lang.'
)t1
left join
(
select * from YOURTABLE
where left(name,5)='lang.'
)t2
on t1.id=t2.id
)t10
group by name,lang,record
having count(*) > 1
)duptable
on tab.name=duptable.name and tab.lang=duptable.lang and tab.record=duptable.record

6

Alternative option 1:

select *
  from yourtable as a
 where exists (select 1
                 from yourtable as b
                where b.name=a.name
                  and b.record=a.record
                  and b.id<>id
              )

Alternative option 2:

select distinct a.*
  from yourtable as a
       inner join yourtable as b
               on b.name=a.name
              and b.record=a.record
              and b.id<>a.id

Alternative option 3:

select b.*
  from (select name
              ,record
          from yourtable
         group by name
                 ,record
         having count(*)>1
       ) as a
       inner join yourtable as b
               on b.name=a.name
              and b.record=a.record
7

Thanks a lot ....