How to get duplicate values unique ID

Question

0.00/5 (No votes)

See more:

Example Table values:

ID	AcountNo	Createddate
1	11111		2008-12-23 00:00:00.000 
2	11111		2008-12-23 00:00:00.000 
3	11111		2008-12-24 00:00:00.000 
4	22222		2008-12-23 00:00:00.000 
5	22222		2008-12-24 00:00:00.000 
6	22222		2008-12-23 00:00:00.000 
7	33333		2008-12-23 00:00:00.000 
8	33333		2008-12-23 00:00:00.000 
9	44444		2008-12-23 00:00:00.000 
10	55555		2008-12-23 00:00:00.000

I want only duplicate Values Id No like

I need sql query for 1 lacks data.

Posted 13-Apr-15 3:23am

ThahirAhmed

Updated 13-Apr-15 3:37am

Kornfeld Eliyahu Peter

v2

Add a Solution

Comments

ZurdoDev 13-Apr-15 9:33am

How do we know ID 1 is a duplicate?

2 solutions

Solution 2

An alternative solution is:

SQL

SELECT ID, AcountNo, Createddate
FROM (
    SELECT ID, AcountNo, Createddate, ROW_NUMBER() OVER(PARTITION BY Createddate ORDER BY ID) AS RowNo
    FROM TableName
) AS T 
WHERE RowNo=1

For further information, please see ranking functions[^].

Posted 13-Apr-15 9:34am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-04-13T03:34:00

Solution 1

Try this:

SQL

SELECT
   ID
FROM
   YourTable As T1
WHERE
   Exists
   (
      SELECT 1
      FROM YourTable As T2
      WHERE T2.ID != T1.ID
      And T2.AccountNo = T1.AccountNo
      And T2.CreatedDate = T1.CreatedDate
   )
;

Posted 13-Apr-15 3:34am

Richard Deeming

Comments

ThahirAhmed 13-Apr-15 10:03am

Thanks brother, Its 100% good.

Maciej Los 13-Apr-15 15:44pm

5ed!