asked on

how can i select nondublicated records on sql

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

ASKER CERTIFIED SOLUTION

Megan Brooks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

try post your sample data with expected result.

Mike Eghtebas

-- 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ão

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