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.
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 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
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