CODEWITHSUNDEEP
sqlsql-server
Paweł Janiszek
Paweł Janiszek

Reputation: 23

Need to select repeated records

I need to select only repeated records from my tables. Query executed successfully but there is no data into it.

My code:

SELECT PRACOWNK.X_I, Imie,Nazwisko, HISTORIA.X_I, HISTORIA.NrKartyRCP 
FROM R2P_psou_dane_1.dbo.PRACOWNK 
JOIN R2P_psou_dane_1.dbo.HISTORIA on PRACOWNK.X_I=HISTORIA.X_IPracownik
WHERE AktZatrudnienie = 1 
GROUP BY PRACOWNK.X_I,Nazwisko,Imie,HISTORIA.X_I, HISTORIA.NrKartyRCP
HAVING COUNT(PRACOWNK.X_I) > 1;

Could you please help?

Upvotes: 2

Views: 74

Answers (3)

Rahul
Rahul

Reputation: 77866

Since SQL Server you can use ROW_NUMBER() function like

SELECT * FROM (
SELECT PRACOWNK.X_I, Imie,Nazwisko, HISTORIA.X_I, HISTORIA.NrKartyRCP,
ROW_NUMBER() OVER(PARTITION BY PRACOWNK.X_I ORDER BY PRACOWNK.X_I) AS rn 
FROM R2P_psou_dane_1.dbo.PRACOWNK 
JOIN R2P_psou_dane_1.dbo.HISTORIA on PRACOWNK.X_I=HISTORIA.X_IPracownik
WHERE AktZatrudnienie = 1 ) xxx
WHERE rn = 1;

Upvotes: 1

Nithila Shanmugananthan
Nithila Shanmugananthan

Reputation: 336

Take all data and put into a temporary table.

SELECT PRACOWNK.X_I, Imie,Nazwisko, HISTORIA.X_I, HISTORIA.NrKartyRCP
INTO #temp
FROM R2P_psou_dane_1.dbo.PRACOWNK 
JOIN R2P_psou_dane_1.dbo.HISTORIA on PRACOWNK.X_I=HISTORIA.X_IPracownik
WHERE AktZatrudnienie = 1

Then check the having condition

SELECT * FROM #temp
group by  PRACOWNK.X_I, Imie,Nazwisko, HISTORIA.X_I, HISTORIA.NrKartyRCP
HAVING COUNT(PRACOWNK.X_I) > 1;

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

If you want to get records based on count of PRACOWNK.X_I then remove this column from select and Group by.

SELECT  Imie,Nazwisko, HISTORIA.X_I, HISTORIA.NrKartyRCP 
FROM R2P_psou_dane_1.dbo.PRACOWNK 
JOIN R2P_psou_dane_1.dbo.HISTORIA on PRACOWNK.X_I=HISTORIA.X_IPracownik
WHERE AktZatrudnienie = 1 
GROUP BY  Nazwisko,Imie,HISTORIA.X_I, HISTORIA.NrKartyRCP
HAVING COUNT(PRACOWNK.X_I) > 1;

Upvotes: 0

Related Questions