CODEWITHSUNDEEP
sqlsql-servert-sql
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Select rows based on one column value exists more than once

I have a table like this

Pcode     LogId    ExtDate
------------------------------
p123      2        2021-01-02
p342      3        2021-01-16
p456      4        2021-05-02
p456      5        2021-07-26
p634      6        2021-05-02
p764      7        2021-01-18
p764      8        2021-06-25

I am looking for a query which returns only those rows with column one value exists more than one.

So the output should be like this. Which means we are taking only items at which the Pcode exists more than once

Pcode     LogId    ExtDate
-----------------------------
p456      4        2021-05-02
p456      5        2021-07-26
p764      7        2021-01-18
p764      8        2021-06-25

I tried using dense_rank, but I got stuck here... can't move any further

select 
    pcode,
    LogId,
    extdate,
    rn
from
    (select 
         pcode,
         L.logid,
         extdate,
         dense_rank() over (partition by pcode order by L.extdate desc) rn
     from 
         kip_project_master P
     inner join 
         kip_report_extraction_log L on L.LogId = P.LogId) tbl

Upvotes: 0

Views: 1258

Answers (4)

Suraj S.
Suraj S.

Reputation: 91

you can do this like,

select * from yourtable join (select 
pcode,count(1) as cnt from yourtable group by 
pcode having COUNT(1)>1)b on a.pcode=b.pcode

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming another column is unique, just use exists:

select kpm.*
from kip_project_master kpm
where exists (select 1
              from kip_project_master kpm2
              where kpm2.pcode = kpm.pcode and kpm2.logid <> kpm.logid
             )
order by kpm.pcode, kpm.logid;

In particular, this can take advantage of an index on (pcode, logid), which should make it pretty fast.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try the below - demo here

select pcode,logid,extdate from
(
select *,count(pcode) over(partition by pcode) cnt from t1
)A where cnt>1

Upvotes: 2

GrahamH
GrahamH

Reputation: 360

maybe ?

DROP TABLE IF EXISTS #yourtable
CREATE TABLE #yourtable(
   Pcode   VARCHAR(30) NOT NULL
  ,LogId   INT 
  ,ExtDate DATE 
);
INSERT INTO #yourtable(Pcode,LogId,ExtDate) VALUES
('p123',2,'2021-01-02'),('p342',3,'2021-01-16'),('p456',4,'2021-05-02')
,('p456',5,'2021-07-26'),('p634',6,'2021-05-02'),('p764',7,'2021-01-18')
,('p764',8,'2021-06-25');

WITH cte AS
(
SELECT   Pcode
FROM      #yourtable
GROUP BY Pcode
HAVING   (COUNT(Pcode) > 1) 
)
SELECT   yt.Pcode, yt.LogId, yt.ExtDate
FROM      yourtable yt 
        INNER JOIN
          cte ON yt.Pcode = cte.Pcode

Upvotes: 1

Related Questions