Select rows based on two columns in SQL Server -

Select rows based on two columns in SQL Server -

i have table stores info accidentally info has been stored multiple times because of case sensivity username field on server side code. username field should regarded case insensitive. of import columns , info table can found below.

my requirements delete recent saved data. i'm writing sql script this, , started out identifying rows duplicates. selection returns table below.

for each row, recent save lastupdateddate if exist, otherwise createddate. example, recent save 'username' row 3.

id createddate lastupdateddate username -- ----------- --------------- -------- 1 11-nov-11 username 2 01-nov-11 02-nov-11 username 3 8-jan-12 username

my script (which selects rows duplicated username appears) looks like:

class="lang-sql prettyprint-override"> select id, createddate, lastupdateddate, username table lower(username) in ( select lower(username) table grouping lower(username) having count(*) > 1 ) order lower(username)

my question is: how select row 3? have searched stack overflow match question, found no match enough. know have create bring together of kind, can't head around it. thankful force in right direction.

we using sql server, quite new version.

to delete duplicates, can use:

with todelete ( select t.*, row_number() on (partition lower(username) order createddate desc) seqnum table ) delete t seqnum > 1

this assigns sequential number each row, starting 1 recent. deletes recent.

for 2 dates, can use:

with todelete ( select t.*, row_number() on (partition lower(username) order thedate desc) seqnum (select t.*, (case when createddate >= coalesdce(updateddate, createddate) createddate else updateddate end) thedate table ) t ) delete t seqnum > 1

sql sql-server