Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002

    Unhappy Unanswered: Self-Join Duplicates - Help!

    Can some kind person out there please help me, I've been stuck on this for daaaa-y-s.

    I have a database that allows users to search for pdf's of technical drawings.

    Basically I have one huge table with multiple columns, which the user can only search on any combination of one of these two columns
    "drawing_series" eg 0100, 0046, 1000
    "drawing_number" eg 0076000, 0000123, 0000004

    There is also a Revision column(which the user can't see) that goes up by 1 each time a drawing has been modified and resubmitted to the database.
    "revision" eg 01, 02, 03, ....... 99

    So a search on 0046 series might pull back drawings


    The problem is that I only want drawings with the highest revisions returned eg


    The code below worked like a charm in the test stages pulling back a few hundred records but now that i've uploaded 10's of thousands of records to the DB the whole lot dies if the search result pulls back more than a few thousand records.

    SELECT * FROM dbo.Drawing_Database
    where dbo.Drawing_Database.revision=(select max(revision) from dbo.Drawing_Database self where self.drawing_series + self.drawing_number = dbo.Drawing_Database.drawing_series + dbo.Drawing_Database.drawing_number) Drawing_Series like '0046' order by Drawing_Series, Drawing_Number

    There must be a simpler way of doing this as i can pull out duplicate series + numbers using " HAVING Count(*)>1" but dont know where to go from there.


  2. #2
    Join Date
    Dec 2003
    Try this....
    i am pasting the full script here...

    CREATE TABLE Drowing_Master(Series char(4),num char(7),rev int)

    insert into Drowing_Master
    select '0046','0010000','01' union all
    select '0046','0010000','02' union all
    select '0046','0010000','03' union all
    select '0046','0076000','01' union all
    select '0046','0076888','01' union all
    select '0046','0076888','02'


    select Series,num,rev from Drowing_Master dm
    where rev = (select max(rev) from Drowing_Master dm1
    where dm1.num = dm.num)
    and dm.series = '0046'
    order by Series, num

  3. #3
    Join Date
    Feb 2002

    Talking I've Got it working, Yahooooo!

    Hi cheriyan,

    Thanks for that.
    Your code works brilliantly. I can pull back thousands of records in under 2 seconds instead of the 3 minutes it used to take.
    I had to play about with your code a little though, but that was because in simplifying my question to make it easier to follow I over simplified it a little too much.
    None the less, after jiggling a couple of columns round on my db and tweaking your code a fraction I get the results I needed.

    Cheers and a big sloppy girly kiss on the bottom to you


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts