I have a script that needs to join to 9 tables, they are all on outer joins as they don't all have matching data and some tables will bring back multiple rows. Ideally I would like my script to bring back only unique fields. To replicate my issue use the following:
** Edited to remove the insert statements ***
What I would really like is:
And I'm not sure how to do this, I'm using SQL 2000 otherwise I would have used ROWNUMBER function.
---------- Excerpt from real data added:
The tables join on FieldA, value 900075752 (for this example), and for this example TableA is the primary table to use and left outer join to the others.
TableB for this value has: 3 5 7 8 10 11 14
TableC for this value has: 57, 80, 433 481 675 742 1207
When I run the script the data comes back giving all options for the three tables.
As a starter.....
This pre-2005 solution creates a sequence number (or rank) by joining the table back onto itself and counting the number of rows with a value less than or equal to the current one.
---- Edit: going code-blind now...
does this give the right results
Includes a pre-2005 tally table to give something common for all the tables to join on, even if there are differing numbers of rows.
OK, one option (edit - based on original query - edit) would appear to be create a fourth temporary table and select from that. So change your
Then use the following:
select field1,field2,count(*) as count into #temp from tablename groupby field1,field2 truncate table tablename select field1,field2 into tablename from #temp
answered Jun 23 '11 at 03:03 AM
select field1,field2,count(*) as count into #temp from tablename groupby field1,field2
truncate table tablename
select field1,field2 into tablename from #temp
answered Jun 23 '11 at 03:04 AM