Duplicate Record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate Record

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2011-03-21 : 02:42:35

Hi all,
I have a table with

TranId, ReqId, CompletedBy

1 1 1
2 1 2
3 2 1
4 2 1
5 3 1

Here Rows 3,4

ReqId, CompletedBy by are same...In that case i want to get ReqId as 2.

My table contains these sort of Duplicate records...

Please help me to find that type of values

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-21 : 07:27:00

If you just want to select ReqId and CompletedBy, you can do this:

select distinct
   ReqId,
   CompletedBy
from
   YourTable

If you also want to select the first column - TranId while also eliminate duplication in the last two columns, then you have to decide how to pick the value for TranId. That is, in your example of rows 3 and 4, do you wan to pick row 3 or row 4. And what if there are many more duplicates. One way to do that is to simply say that I will pick the maximum TranId. If that is acceptable, you can do it as

select
   max(TranId) as TranId,
   ReqId,
   CompletedBy
from
   YourTable
group by
   ReqId,
   CompletedBy

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-21 : 21:24:45

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

What you are doing is wrong; prevent the problem instead of always and forever repairing it. Here is my guess, based on the lack of information your gave us:

CREATE TABLE Foobar
(trans_nbr INTEGER NOT NULL PRIMARY KEY,
reg_id INTEGER NOT NULL,
completion_something INTEGER NOT NULL,
UNIQUE (reg_id, completion_something));

Please learn why rows are not records; it will make SQL much easier for you.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources