3

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
RHPT
  • 2,560
  • 5
  • 31
  • 43

6 Answers6

6

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
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

Community
  • 1
  • 1
Dan Williams
  • 4,910
  • 11
  • 37
  • 46
  • Moving the `Table2.Type = 1` condition to the JOIN clause will make no difference in performance. – Ryan Tenney Aug 05 '10 at 20:13
  • @Ryan W Tenney: But it can affect results returned, because when the criteria is on the JOIN, it occurs prior to. – OMG Ponies Aug 05 '10 at 20:16
  • @OMG Ponies: No, it won't affect the results. Moving criteria between the `on` clause in a join and the `where` clause will only affect the results returned for outer joins, but not for inner joins. – Shannon Severance Aug 05 '10 at 20:56
  • I think saying it will make no difference in performance is generalizing a bit. For simple queries I would agree, but it's worth testing it out and looking at the query plan. http://www.cftips.net/post.cfm/instantly-speed-up-sql-query-by-using-conditional-in-your-joins – Dan Williams Aug 06 '10 at 12:43
1

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.

Mark Canlas
  • 9,385
  • 5
  • 41
  • 63
0
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
STT LCU
  • 4,348
  • 4
  • 29
  • 47
user2916283
  • 1
  • 1
  • 2
    Welcome to stackoverflow! Thanks for contributing an answer. An explanation, even a small one, about your code will be very helpful to others in deciding whether it will be useful to them. – LarsH Oct 24 '13 at 15:02
0

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

Vidar Nordnes
  • 1,334
  • 10
  • 20
0

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

Mr Shoubs
  • 14,629
  • 17
  • 68
  • 107