sql server - Copy Distinct Records Based on 3 Cols -


i have loads of data in table called temp. data consists of duplicates. not entire rows same data in 3 columns. houseno,dateofyear,timeofday.

i want copy distinct rows "temp" table, "thermdata."

basically want copy distinct rows temp thermdata distinct(houseno,dateofyear,timeofday). that.

i know can't that. alternative how can that.

do me out. have tried lots of things haven't solved got it.

sample data. values repeated like.... want delete duplicate row based on values of houseno,dateofyear,timeofday

houseno dateofyear timeofday count 102 10/1/2009    0:00:02 2 102 10/1/2009    1:00:02 2 102 10/1/2009    10:00:02    2

here northwind example based on orders table.

there duplicates based on (employeeid , shipcity , shipcountry) columns.

if execute code between these 2 lines:

/* run below line show crux of fix */ /* run above line show crux of fix */

you'll see how works. basically:

(1) run group on 3 columns of interest. (derived1duplicates)

(2) join table using these 3 columns. (on ords.employeeid = derived1duplicates.employeeid , ords.shipcity = derived1duplicates.shipcity , ords.shipcountry = derived1duplicates.shipcountry)

(3) each group, tag them cardinal numbers (1,2,3,4,etc) (using row_number())

(4) keep row in each group has cardinal number of "1". (where derived2duplicatedeliminated.rowidbygroupby = 1)

    use northwind     go   declare @destinationvariabletable table (      notneededbutforfunrowidbygroupby int not null ,     notneededbutforfunduplicatecount int not null ,     [orderid] [int] not null,     [customerid] [nchar](5) null,     [employeeid] [int] null,     [orderdate] [datetime] null,     [requireddate] [datetime] null,     [shippeddate] [datetime] null,     [shipvia] [int] null,     [freight] [money] null,     [shipname] [nvarchar](40) null,     [shipaddress] [nvarchar](60) null,     [shipcity] [nvarchar](15) null,     [shipregion] [nvarchar](15) null,     [shippostalcode] [nvarchar](10) null,     [shipcountry] [nvarchar](15) null )   insert @destinationvariabletable (notneededbutforfunrowidbygroupby , notneededbutforfunduplicatecount , orderid,customerid,employeeid,orderdate,requireddate,shippeddate,shipvia,freight,shipname,shipaddress,shipcity,shipregion,shippostalcode,shipcountry )  select rowidbygroupby , myduplicatecount , orderid,customerid,employeeid,orderdate,requireddate,shippeddate,shipvia,freight,shipname,shipaddress,shipcity,shipregion,shippostalcode,shipcountry ( /* run below line show crux of fix */ select  rowidbygroupby = row_number() over(partition ords.employeeid , ords.shipcity , ords.shipcountry order ords.orderid )  , derived1duplicates.myduplicatecount , ords.*    [dbo].[orders] ords  join  ( select employeeid , shipcity , shipcountry , count(*) myduplicatecount [dbo].[orders] group employeeid , shipcity , shipcountry /*having count(*) > 1*/ ) derived1duplicates on  ords.employeeid = derived1duplicates.employeeid ,  ords.shipcity = derived1duplicates.shipcity ,  ords.shipcountry = derived1duplicates.shipcountry  /* run above line show crux of fix */ ) derived2duplicatedeliminated derived2duplicatedeliminated.rowidbygroupby = 1  select * @destinationvariabletable

emphasized text*emphasized text*emphasized text


Comments

Post a Comment

Popular posts from this blog

monitor web browser programmatically in Android? -

i've got tricky question here. need users make payment bank (namely barclaycard) in uk. so, have https url , add parameters (such amount pay, order reference, etc) url, start http connection intent.actionview, redirect user browser can enter credit card details on bank's webpage , make payment our account successfully. far ? the code use below (i changed values privacy reasons) problem is, need app when user has completed/failed/cancelled payment. barclaycardautomatically redirects particular url when payment has succeeded, 1 if failed. there no way of knowing when barclaycard payment has succeeded go android app somehow ? button cardbutton = (button) findviewbyid(r.id.card_button); cardbutton.setonclicklistener(new view.onclicklistener() { @override public void onclick(view arg0) { string prehashstring = new string(); string prohashstring = new string(); string shapassphrase = new string(); shapassphrase = "gsvth£h70zkh...
Read more

Shrink a YouTube video to responsive width -

i have youtube video embedded on our website , when shrink screen tablet or phone sizes stops shrinking @ around 560px in width. standard youtube videos or there can add code make go smaller? you can make youtube videos responsive css. wrap iframe in div class of "videowrapper" , apply following styles: .videowrapper { float: none; clear: both; width: 100%; position: relative; padding-bottom: 56.25%; padding-top: 25px; height: 0; } .videowrapper iframe { position: absolute; top: 0; left: 0; width: 100%; height: 100%; } the .videowrapper div should inside responsive element. padding on .videowrapper necessary keep video collapsing. may have tweak numbers depending upon layout.
Read more

wpf - PdfWriter.GetInstance throws System.NullReferenceException -

i'm trying create pdf document using itextsharp 5.3.4 using following document document = new document(); filestream stm = new filestream(filename, filemode.create); pdfwriter writer = pdfwriter.getinstance(document, stm); i'm getting system.nullreferenceexception following stack trace: system.nullreferenceexception occurred hresult=-2147467261 message=la référence d'objet n'est pas définie à une instance d'un objet. source=itextsharp stacktrace: à itextsharp.text.version.getinstance() innerexception: i've verified neither document nor stm null, , if select "continue" in vs12 document created - exception thrown. updated itextsharp 5.4.0 , it's still occurring. can't find information on anywhere - got ideas? make sure not catching exceptions. nullreferenceexception can 1 caught , handled inside itextsharp, don't care it. fact can continue supports theory. change following setting verify: debug -> exceptions ...
Read more