January 18, 2007 at 9:48 am
i know that using the DISTINCT keyword will filter out all rows where all the fields are the same but what if i want to filter out all rows where just one field is the same as in another row?
how would I accomplish that?
January 18, 2007 at 9:52 am
SELECT * FROM MyTable t JOIN
(SELECT DISTINCT DistinctRow FROM MyTable) d
ON t.DistinctRow = d.DistinctRow
John
January 18, 2007 at 10:10 am
ok i think that makes sense. now the complication is that i am already doing a join.
my current query looks like this
SELECT products.ID, products.Prod_Name, products.QTY, vendor.VENDOR
FROM products, vendors
WHERE products.vendor = vendors.vendor
so how would i integrate your posted code into this?
January 18, 2007 at 11:35 am
Actually, I must have been asleep when I posted that query. It won't work. What we need to know is where there are duplicates in your column, which row do you want to display?. Is it the one most recently created, the one with the most sales, or something else?
So your query will look something like this:
SELECT * FROM MyTable t JOIN
(SELECT DISTINCT DistinctRow, MAX(SomeOtherCol) AS Tiebreaker FROM MyTable) d
ON t.DistinctRow = d.DistinctRow
AND t.SomeOtherCol = d.Tiebreaker
If you give me the table names and column names to replace the ones I've made up, then we can look at inserting it into what you already have.
John
January 18, 2007 at 3:21 pm
It is painful if we have a table with complete identical rows. To delete such dumplicates, we may do as follow:
1. Create a column (col2) with identity;
col1 col2
A 1
A 2
a 3
B 4
B 5
2. Then run
DELETE FROM Table1 WHERE col2 NOT IN (SELECT MIN(col2) FROM Table1 GROUP BY col1 HAVING COUNT(col1) > 1)
3. Drop the column (col2);
January 20, 2007 at 12:11 am
run this sql query:
select distinct * from
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply