ansaurus

Question

SELECT DISTINCT on a field not appearing in recordset?

Answer 1

+5 A:

SELECT a.Field2
     , a.Field3
  FROM (SELECT DISTINCT a.guid
                      , a.Field2
                      , a.Field3
                   FROM table1 a)  a

dcp 2010-05-13 15:14:39

damn, you were 20 seconds faster ;-)

meriton 2010-05-13 15:15:51

That's why he has the gold :)

2010-05-13 15:20:02

@meriton - :) At least we both arrived at the same solution.

dcp 2010-05-13 15:20:27

Answer 2

+2 A:

Wrap it in a subselect?

select my, interesting, columns
from (
    select distinct GUID, ...
    from ...
)

meriton 2010-05-13 15:15:08

Deleted my answer as it would have returned everything, this is the correct solution +1!

GenericTypeTea 2010-05-13 15:20:21

I have to choose this one as the answer, because it actually has an English answer in it! :P

2010-05-13 15:34:41

Answer 3

A:

select the distinct values into a temp table first.

Then select out only the values you want.

sparkkkey 2010-05-13 15:15:50

Answer 4

+3 A:

You can also do

SELECT x, y FROM tbl GROUP BY guid, x, y

The drawback here is that you have to duplicate the column list in the GROUP BY clause, which is annoying, but the other answers do as well.

egrunin 2010-05-13 15:22:12

ansaurus

tags:

views:

answers:

SELECT DISTINCT on a field not appearing in recordset?

related questions