How to Find Duplicate Records in Table | Interview Question

When generally asked for duplicate, every time for most of person start thing about the group by, count clause. Which is correct, but these will not work every where. Count(*), Group by , having are the for beginner level. Student get learnt from basics.

On other hand, when we are working in IT industry then window function are usable in real time examples.

What i think Count(*), group by, having are usable for OLAP database, and window function are usable at OLTP level.

Lets see result with some example:

XYZ
ID	FirstName	LastName	MiddleName
1	Gurjeet	Singh
2	Harry	Potter
3	Gurjeet	Singh
4	Prem	Singh	L
5	Harry	Potter

When i use the query 

SELECT count(*), FirstName, LastName, MiddleName
FROM XYZ
GROUP BY  FirstName, LastName, MiddleName

So result will be 

Count	FirstName	LastName	MiddleName
2	Gurjeet	Singh
2	Harry	Potter
1	Prem	Singh	L

I am agree the query return the correct result, but it is providing the Count and Name of Duplicate person. Means Gurjeet is the person exists by 2 times in table.

But for some cases we also need ID along with the duplicate person name, which is not possible with the help of GROUP by. So here we have to use window function.

select * from (
SELECT 
    ID, 
    FirstName, 
    LastName, 
    MiddleName,
    row_number() over(partition by Firstname, LastName, MiddleName order by ID) rowNumber
FROM XYZ
) t where rowNumber > 1

So how query will work, below table represent how the row_number will be assign to records

XYZ
ID	FirstName	LastName	MiddleName	rn
1	Gurjeet	Singh		1
2	Harry	Potter		1
3	Gurjeet	Singh		2
4	Prem	Singh	L	1
5	Harry	Potter		2

When we use the outer query, and apply the filter "where rowNumber > 1" below result set will be appear. Here main focus is column ID.

ID	FirstName	LastName	MiddleName
3	Gurjeet	Singh
4	Prem	Singh	L
5	Harry	Potter

Now in some cases we also need ID along with the all duplicate person name (only Duplicate), which is possible with the help of using Count(*) using Partition by clause.

select * from (
SELECT 
    ID, 
    FirstName, 
    LastName, 
    MiddleName,
    Count(*) over(partition by Firstname, LastName, MiddleName order by ID) TotalCount
FROM XYZ
) t where rowNumber > 1

XYZ
ID	FirstName	LastName	MiddleName	TotalCount
1	Gurjeet	Singh		2
2	Harry	Potter		2
3	Gurjeet	Singh		2
5	Harry	Potter		2

A Very Unique Case : Delete Duplicate from Cross Referenced Table [Parent Child relationship]

Cross referenced table, i do not know if i am pronounce this is correctly or not. First see the table structure below, and let me know what this table is known as?, how to pronounce it? What is name of structure storing this kind of data.

XYZ
ID	CustomerID [Reference to Cus.]	RelatedCustomerID [Reference to Cus.]	Relation Ship of Customers
1	20021	20022	is children of
2	20022	20021	is father of
3	20023	20024	is friend of
4	20024	20023	is brother of
5	20025	20026	is children of

Do not forget to provide your inputs in comment section

Now the case is earlier the application is designed in way to hold the relationship between 2 customers in either way. Now as per new requirement this relationship are duplicate. We need to keep only 1 relation ship from cross reference rows.

example - 

1. Row 1 and Row 2 are duplicate, we need to keep only 1 row (any one)

2. Row 3 and Row 4 are duplicate, we need to keep only 1 row  (any one)

3. Row 5 is itself is unique

So did workaround with friends, of-course nothing is found on internet.google. Tried Differnt type of join , inner join , self join, min , max, dense rank, pivot but nothing works

Below is the query we prepared after all.

SELECT 
    distinct
    CASE
        WHEN CustomerID < RelatedCustomerID  THEN CustomerID 
        ELSE RelatedCustomerID 
        END AS CustomerID_v,
     CASE
        WHEN CustomerID  > RelatedCustomerID  THEN CustomerID 
        ELSE RelatedCustomerID 
        END AS RelatedCustomerID_V
FROM 
    XYZ
	 

This could be a good interview question as well

database url and driver

JDBC drive used to connect between application or database or in many cases as well. such as Any third party tool or application where you want to pull out data. To do this we used JDBC URL/ class paths.

Every server have their own JDBC drivers and url, class paths. URL you need to put in you application where connections needs to define. Also every server have their own jdbc***.jar files, you need to download and place these jar files in lib folder of repository folder to make connection happen and live. So that you can read, edit, delete, the data in database.

sqlserver 
database url
jdbc:sqlserver://192.168.1.1:1433;databaseName=XYZ-DatabaseName


spago bi
url - jdbc:sqlserver://192.168.1.1
driver - com.microsoft.sqlserver.jdbc.SQLServerDriver


jtds driver
database url - jdbc:jtds:sqlserver://192.168.1.1:1433/XYZ-DatabaseName


oracle
url - jdbc:oracle:thin:@localhost:1531:XYZ-DatabaseName
driver - oracle.jdbc.driver.OracleDriver

Marvell Yukon 88E8040 PCI-E Fast Ethernet Controller Driver for Linux

That is the case, just install a linux system is red hat RHEL 5, or Enterprise Edition. Installed a good system, was found inside the network settings did not recognize the hardware, can not be network settings. My own laptop is a dell inspiron 1525, an integrated network card.
windows under the normal Internet access, and the card information: Marvell Yukon 88E8040 PCI-E Fast Ethernet Controller.
The problem now is I can not find this card in the drive under linux.

centos or rhel4&5 - ethernet not detected

Below are the reuire details.

$>uname -rmi
output - 2.6.18-92.e15PAE i686 i386

$>for BUSID in $(/sbin/lspci | awk '{ IGNORECASE=1 } /net/ { print $1 }'); do /sbin/lspci -s $BUSID -m; /sbin/lspci -s $BUSID -n; done

output -
09:00:0 "Ethernet controller" marvell Technology Group Ltd." "88E8040 PCI-E FastEthernet controller" -13 "dell" "unknown device 02aa"
09:00.0 0200: llab:4354 (rev 13)
0c:00:0 "network controller" "broadcom corporation" "BCM4310 USB controller" -r01 "dell" "unknown device 000c"
0c:00.0 0280: 14e4:4315 (rev 01)

SOLUTION:
The first step is to use another system and download the kABI tracking kmod-sk98lin package that is available from ELRepo.

As you are running a 32-bit system with a PAE kernel, the package you require is kmod-sk98lin-PAE-10.70.7.3-2.el5.elrepo.i686.rpm which can be found here.

Transfer that package to your laptop via some form of removable medium (USB memory stick, CD-RW, floppy disk, etc) and, as root, install it by --

rpm -ivh kmod-sk98lin-PAE-10.70.7.3-2.el5.elrepo.i686.rpm

Now edit your /etc/modprobe.conf file so that there is one alias line that references the eth0 device --

alias eth0 sk98lin

At this point I would recommend that you re-boot your laptop after
connecting it to a wired Internet source. You should now be able to
configure the system (if necessary) by running system-config-network.

No Content to display

No Content to display

DAA and GBI files to ISO

DAA2ISO description

Converts single and multipart DAA file images to the original ISO format.
The DAA2ISO application was designed to be an open source command-line/GUI tool that will help you convert single and multipart DAA file images to the original ISO format.





The DAA image (Direct Access Archive) in fact is just a compressed ISO which can be created through the commercial program PowerISO..

Download Link:
Click here to download file
----------------------------------------------------------
http://rapidshare.com/files/261960355/daa2iso.zip.html
----------------------------------------------------------