Alternative to DISTINCT Function

Question

Is there a better way to get all distinct values from three columns in one table other than using the DISTINCT function? I've also tried GROUP BY, but there doesn't seem to be any noticeable difference in the cost.

SELECT DISTINCT Table1.Col1, Table2.Col1, Table1.Col3
FROM Table1 
INNER JOIN Table2 ON Table1.FK = Table2.ID
WHERE Table1.Foo = 1865 AND Table2.Type = 1

Answer 1

GROUP BY is intended for aggregate function use; DISTINCT just removes duplicates (based on all column values matching on a per row basis) from visibility.

If TABLE2 allows duplicate values associated to TABLE1 records, you have to use either option. Depends on the data and what you want to see, but you could use a group by & aggregate function to get the highest (using MAX) or lowest (using MIN) values from TABLE2...

Answer 2

Nope, that's how it's done.

Although, you could try:

SELECT DISTINCT Table1.Col1, Table2.Col2
FROM Table1
INNER JOIN Table2 ON Table1.FK = Table2.ID AND Table2.Type = 1
WHERE Table1.Foo = 1865

Speed will depend on your data.

Also see sql group by versus distinct

Answer 3

Have you tried creating an index on the fields you're selecting?

The relative costs of DISTINCT and GROUP BY make sense. One way of (and probably the way it's using) of processing the data is to sort the rows by the fields you provide. Then the difference between the two is that DISTINCT skips rows that are equal to the previous row, and GROUP by happens to run a count using the same metric of equality.

Answer 4

No, there's not, but if you're struggeling with performance on this, you might want to consider indexes. If you provide more details, maybe we can help with this

Answer 5

you could try moving the conditions in your 'where' to your joins, though I expect they'll be parsed the same.

If your trying to increase performance, add an index to Table1.Foo and Table2.Type

Answer 6

create table #temp (col1 int, col2 int, col3 int)
create index temp_index on #temp (col 1)

insert into #temp
SELECT Table1.Col1, Table2.Col1, Table1.Col3
FROM Table1 
INNER JOIN Table2 ON Table1.FK = Table2.ID
WHERE Table1.Foo = 1865 AND Table2.Type = 1

select distinct col1, col2, col3
from #temp

Answer 7

Can certainly try row_number() function.