We help IT Professionals succeed at work.

how can i select nondublicated records on sql

53 Views
Last Modified: 2017-04-12
Hello experts,

I am trying an inner join query on SQL and i get a lot of duplicated results. Is there a way i can pull only undublicated records. What makes my table records unique is firstname and lastname.

thanks
Comment
Watch Question

SQL Server Consultant
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
try post your sample data with expected result.
Mike EghtebasDatabase and Application Developer

Commented:
-- 1) how can i select nondublicated records on sql
--    To illustrate, create @temp TABLE with 27 rows of date (used in another question at EE)
use ee
DECLARE @temp TABLE (Tran_Order int, acc1 int, [TranDate] date, rate decimal(3,2));
insert into @temp(Tran_Order, acc1, [TranDate], rate) values
(4,	11188368,	'4/27/2007',	1.35)
,(6,	11188368,	'4/30/2008',	0.95)
,(6,	12863288,	'4/27/2007',	1.35)
,(8,	12863288,	'5/16/2008',	0.95)
,(3,	12978348,	'4/27/2007',	1.35)
,(4,	12978348,	'5/3/2007',	0.8)
,(3,	14878928,	'12/8/2006',	1.35)
,(5,	14878928,	'12/13/2007',	0.8)
,(2,	98743553,	'3/30/2007',	0.9)
,(3,	98743553,	'5/10/2007',	0.75)
,(5,	98743553,	'12/3/2007',	0.75)
,(6,	98743553,	'5/3/2011',	0.75)
,(7,	98743553,	'4/3/2013',	0.75)
,(8,	98743553,	'9/14/2016',	0.75)
,(1,	98771726,	'4/30/2007',	0.75)
,(2,	98771726,	'8/2/2011',	0.75)
,(3,	98771726,	'2/18/2013',	0.75)
,(4,	98771726,	'7/11/2016',	0.75)
,(1,	98784330,	'4/25/2007',	1.1)
,(2,	98784330,	'5/21/2008',	1)
,(3,	98784330,	'7/1/2011',	1)
,(4,	98784330,	'10/9/2013',	1)
,(5,	98784330,	'3/18/2014',	1)
,(1,	98790248,	'5/3/2007',	1.25)
,(2,	98790248,	'6/21/2007',	1.1)
,(3,	98790248,	'11/21/2007',	0.8)
,(4,	98790248,	'8/2/2011',	0.8);

--Select * from @temp;   --check the content of @temp. 

Open in new window

--2) Next, we will use a derived table subquery (see number 3 at Subqueries at a glance) and include Row_Number and Partition By to isolate the qualified rows. You are asking to: "Select nondublicated records"
-- For Row_Number() see: https://technet.microsoft.com/en-us/library/ms186734(v=sql.110).aspx Please do the necessary adjustments to line 3 below based on you data.  
Select RN, Tran_Order, acc1, [TranDate], rate From(
Select Tran_Order, acc1, [TranDate], rate,
   ROW_NUMBER() OVER(PARTITION BY Tran_Order ORDER BY acc1, [TranDate] Desc ) AS RN
from @temp)D
where RN=1; --first try without where RN=1 to see all returned rows.

Open in new window


-- Results without where RN=1, all rows (27 rows)
RN	Tran_Order	acc1		[TranDate]	rate
1	1			98771726	2007-04-30	0.75
2	1			98784330	2007-04-25	1.10
3	1			98790248	2007-05-03	1.25
1	2			98743553	2007-03-30	0.90
2	2			98771726	2011-08-02	0.75
3	2			98784330	2008-05-21	1.00
4	2			98790248	2007-06-21	1.10
1	3			12978348	2007-04-27	1.35
2	3			14878928	2006-12-08	1.35
3	3			98743553	2007-05-10	0.75
4	3			98771726	2013-02-18	0.75
5	3			98784330	2011-07-01	1.00
6	3			98790248	2007-11-21	0.80
1	4			11188368	2007-04-27	1.35
2	4			12978348	2007-05-03	0.80
3	4			98771726	2016-07-11	0.75
4	4			98784330	2013-10-09	1.00
5	4			98790248	2011-08-02	0.80
1	5			14878928	2007-12-13	0.80
2	5			98743553	2007-12-03	0.75
3	5			98784330	2014-03-18	1.00
1	6			11188368	2008-04-30	0.95
2	6			12863288	2007-04-27	1.35
3	6			98743553	2011-05-03	0.75
1	7			98743553	2013-04-03	0.75
1	8			12863288	2008-05-16	0.95
2	8			98743553	2016-09-14	0.75

Open in new window


 
-- Results with where RN=1, 8 rows
RN	Tran_Order	acc1		[TranDate]	rate
1	1			98771726	2007-04-30	0.75
1	2			98743553	2007-03-30	0.90
1	3			12978348	2007-04-27	1.35
1	4			11188368	2007-04-27	1.35
1	5			14878928	2007-12-13	0.80
1	6			11188368	2008-04-30	0.95
1	7			98743553	2013-04-03	0.75
1	8			12863288	2008-05-16	0.95

Open in new window


Now, you have two choices.
A) based on the illustration above, make it work for the data you have. This will be a better way to go. You get lot out of it.
B) Post your TSQL to create @temp table so we can help you.

Mike
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Please post your query and some sample data so we can help you better.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.