quote:

---

Originally posted by Jeff Moden

For all those saying to "format in the front end"... WHY?

--Jeff Moden

---

quote:

---

Originally posted by Jeff Moden

Thanks, Jeff. Hadn't thought about having to reformat in the app based on a change in requirements.

Based on what you said, about the only time I can see doing any formatting in SQL is when you're exporting to a file that requires a given format and no app is involved.

Appreciate you taking the time!

--Jeff Moden

---

--DROP TABLE BigTest
GO
--===== Create and populate a million row test table.
     -- Column "SomeDate" has a range of  >=01/01/2000  <01/01/2010
     -- That's ten years worth of dates.
 SELECT TOP 1000000
        RowNum     = IDENTITY(INT,1,1),
        SomeID     = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),
        SomeValue  = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
                   + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),
        SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),
        SomeDate   = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)
   INTO dbo.BigTest
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== A table is not properly formed unless a Primary Key has been assigned
  ALTER TABLE dbo.BigTest
        ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Find all duplicated rows EXCEPT for the row with the latest date.
     -- This can be used as a DELETE (see further below) and only takes
     -- about 7 seconds to find the 15 thousand or so dupes that show up
     -- in the million row table.
 SELECT bt1.*
   FROM dbo.BigTest bt1,
        dbo.BigTest bt2
  WHERE bt1.SomeID = bt2.SomeID
    AND bt1.SomeValue = bt2.SomeValue
    AND bt1.SomeDate < bt2.SomeDate

--===== This is what the delete would look like.  Again, it finds and 
     -- deletes all but the LATEST dupe.  To find and delete all but
     -- the earliest dupe, all you need do is change the "<" to ">".
     -- Again, it finds and deletes more than 15000 dupes in only 
     -- 7 seconds.  
 DELETE bt1
   FROM dbo.BigTest bt1,
        dbo.BigTest bt2
  WHERE bt1.SomeID = bt2.SomeID
    AND bt1.SomeValue = bt2.SomeValue
    AND bt1.SomeDate < bt2.SomeDate

Id       Descrip
10       Test1
10       Test2
10       Test3
11       Test1
11       Test2
12       Test2

Id       Descrip
10       Test1
         Test2
         Test3
11       Test1
         Test2
12       Test2

--===== Produce the full million row report with dupe "SomeID" supressed
     -- in about 40 seconds Use the GRID mode if in Query Analyser)...
 SELECT Some_ID = CASE 
                    WHEN bt.RowNum = d.RowNum
                    THEN ''
                    ELSE CAST(bt.SomeID AS VARCHAR(10))
                  END,
        bt.SomeValue,
        bt.SomeNumber,
        bt.SomeDate
   FROM dbo.BigTest bt
   LEFT OUTER JOIN
        (
         SELECT DISTINCT
                bt1.RowNum
           FROM dbo.BigTest bt1,
                dbo.BigTest bt2
          WHERE bt1.SomeID = bt2.SomeID
            AND bt1.SomeValue > bt2.SomeValue
        ) d
     ON bt.RowNum = d.RowNum
  ORDER BY bt.SomeID,Some_ID DESC,bt.SomeValue,SomeDate

 SELECT Some_ID = CASE 
                    WHEN bt.RowNum = d.RowNum
                    THEN ''
                    ELSE CAST(bt.SomeID AS VARCHAR(10))
                  END,
        bt.SomeValue,
        bt.SomeNumber,
        bt.SomeDate
   FROM dbo.BigTest bt
   LEFT OUTER JOIN
        (
         SELECT DISTINCT
                bt1.RowNum
           FROM dbo.BigTest bt1,
                dbo.BigTest bt2
          WHERE bt1.SomeID = bt2.SomeID
            AND bt1.SomeValue > bt2.SomeValue
        ) d
     ON bt.RowNum = d.RowNum
     WHERE bt.RowNum>0
  ORDER BY bt.SomeID,Some_ID DESC,bt.SomeValue,SomeDate

quote:

---

Originally posted by Jeff Moden

Ok... thanks for the "serial number" info... that might be faster in the app but I'll have to take a look...

So far as the dupe report goes that you identified in the previous post, I actually thought of a way to do it without having to use an aggregate, temp table, While loop, or cursor... does a million row (a bit of overkill but wanted to show how fast it is) report in about 40 seconds... you can use the same "BigTest" table I created in my last example. Here's the code for the report...

--===== Produce the full million row report with dupe "SomeID" supressed
     -- in about 40 seconds Use the GRID mode if in Query Analyser)...
 SELECT Some_ID = CASE 
                    WHEN bt.RowNum = d.RowNum
                    THEN ''
                    ELSE CAST(bt.SomeID AS VARCHAR(10))
                  END,
        bt.SomeValue,
        bt.SomeNumber,
        bt.SomeDate
   FROM dbo.BigTest bt
   LEFT OUTER JOIN
        (
         SELECT DISTINCT
                bt1.RowNum
           FROM dbo.BigTest bt1,
                dbo.BigTest bt2
          WHERE bt1.SomeID = bt2.SomeID
            AND bt1.SomeValue > bt2.SomeValue
        ) d
     ON bt.RowNum = d.RowNum
  ORDER BY bt.SomeID,Some_ID DESC,bt.SomeValue,SomeDate

--Jeff Moden

---