Return unique rows

• micang

  SSC Eights!

  Points: 878

  More actions

  February 11, 2016 at 8:23 am

  #399715

  SQL 2008 R2

  Hi All,

  If I have a query like so:

  select contractno, Address1,dateJoined,DateLastTrans

  from tableTest

  where datejoined between 2012-12-01 and 2015-12-31

  and my results are:

  Contractno,address1,datejoined,dateLastTrans

  387, johnroad, 2013-06-01,2016-01-25

  267, bobroad, 2015-12-01,2016-01-25

  267, bobroad, 2015-12-01,2015-12-28

  578, ellenroad, 2012-11-24,2015-09-23

  Columns Contractno,address1,datejoined should be unique, but because of poor data, the result has duplicates (rows,2 and 3) - how can I write a query where if there are duplicates, then look at datelasttrans and get the max record?

  So my expected result would be:

  Contractno,address1,datejoined,dateLastTrans

  387, johnroad, 2013-06-01,2016-01-25

  267, bobroad, 2015-12-01,2016-01-25

  578, ellenroad, 2012-11-24,2015-09-23

  I am not sure if I can do this in one query?

  Thanks

  Michael

  EDIT - :

  My apologies, I just realized that the DateLastTrans column should not be in the result set - therefore the result should be:

  Contractno,address1,datejoined

  387, johnroad, 2013-06-01

  267, bobroad, 2015-12-01

  578, ellenroad, 2012-11-24

  I need to reference the DateLastTrans, but not show in the result.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  February 11, 2016 at 8:29 am

  #1858043

  micang (2/11/2016)

  ---

  SQL 2008 R2

  Hi All,

  If I have a query like so:

  select contractno, Address1,dateJoined,DateLastTrans

  from tableTest

  where datejoined between 2012-12-01 and 2015-12-31

  and my results are:

  Contractno,address1,datejoined,dateLastTrans

  387, johnroad, 2013-06-01,2016-01-25

  267, bobroad, 2015-12-01,2016-01-25

  267, bobroad, 2015-12-01,2015-12-28

  578, ellenroad, 2012-11-24,2015-09-23

  Columns Contractno,address1,datejoined should be unique, but because of poor data, the result has duplicates (rows,2 and 3) - how can I write a query where if there are duplicates, then look at datelasttrans and get the max record?

  So my expected result would be:

  Contractno,address1,datejoined,dateLastTrans

  387, johnroad, 2013-06-01,2016-01-25

  267, bobroad, 2015-12-01,2016-01-25

  578, ellenroad, 2012-11-24,2015-09-23

  I am not sure if I can do this in one query?

  Thanks

  Michael

  I think this should do it:

  select contractno, Address1,dateJoined,MAX(DateLastTrans) as MaxTranDate

  from tableTest

  where datejoined between 2012-12-01 and 2015-12-31

  group by contractno, Address1,dateJoined

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• micang

  SSC Eights!

  Points: 878

  More actions

  February 11, 2016 at 8:32 am

  #1858046

  TheSQLGuru (2/11/2016)

  ---

  micang (2/11/2016)

  ---

  SQL 2008 R2

  Hi All,

  If I have a query like so:

  select contractno, Address1,dateJoined,DateLastTrans

  from tableTest

  where datejoined between 2012-12-01 and 2015-12-31

  and my results are:

  Contractno,address1,datejoined,dateLastTrans

  387, johnroad, 2013-06-01,2016-01-25

  267, bobroad, 2015-12-01,2016-01-25

  267, bobroad, 2015-12-01,2015-12-28

  578, ellenroad, 2012-11-24,2015-09-23

  Columns Contractno,address1,datejoined should be unique, but because of poor data, the result has duplicates (rows,2 and 3) - how can I write a query where if there are duplicates, then look at datelasttrans and get the max record?

  So my expected result would be:

  Contractno,address1,datejoined,dateLastTrans

  387, johnroad, 2013-06-01,2016-01-25

  267, bobroad, 2015-12-01,2016-01-25

  578, ellenroad, 2012-11-24,2015-09-23

  I am not sure if I can do this in one query?

  Thanks

  Michael

  I think this should do it:

  select contractno, Address1,dateJoined,MAX(DateLastTrans) as MaxTranDate

  from tableTest

  where datejoined between 2012-12-01 and 2015-12-31

  group by contractno, Address1,dateJoined

  My apologies, I just realized that the DateLastTrans column should not be in the result set - therefore the result should be:

  Contractno,address1,datejoined

  387, johnroad, 2013-06-01

  267, bobroad, 2015-12-01

  578, ellenroad, 2012-11-24

  I need to reference the DateLastTrans, but not show in the result.

• Y.B.

  SSChampion

  Points: 11642

  More actions

  February 11, 2016 at 11:24 am

  #1858124

  Please include any DDL statements for test data next time. People will be much more willing to help.

  I think this will do...cheers.

  DECLARE @test-2 TABLE (contractno INT, Address1 VARCHAR(50), dateJoined DATE, DateLastTrans DATE)

  INSERT INTO @test-2

  VALUES

  (387, 'johnroad', '2013/06/01', '2016/01/25'),

  (267, 'bobroad', '2015/12/01', '2016/01/25'),

  (267, 'bobroad', '2015/12/01', '2015/12/28'),

  (578, 'ellenroad', '2012/11/24', '2015/09/23')

  SELECT

  contractno,

  Address1,

  dateJoined

  FROM

  (

  SELECT contractno, Address1, dateJoined, MAX(DateLastTrans) as MaxTranDate

  FROM @test-2

  GROUP BY contractno, Address1,dateJoined

  ) x

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• autoexcrement

  SSCertifiable

  Points: 5951

  More actions

  February 12, 2016 at 10:58 am

  #1858455

  If you don't need DateLastTrans in your resultset, and if the other 3 columns are unique identifiers, then why are you even looking at the 4th column?

  select DISTINCT contractno, Address1, dateJoined

  from tableTest

  where datejoined between 2012-12-01 and 2015-12-31

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura