SELECT DISTINCT on a field not appearing in recordset?

Question

I want to do a SELECT DISTINCT guid, ..., but I don't want guid appearing in the recordset. How do I do this?

score 5 · Answer 1 · answered May 13 '10 at 15:14

5

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

answered May 13 '10 at 15:14

dcp

54,410
22
144
164

damn, you were 20 seconds faster ;-) – meriton May 13 '10 at 15:15
1

That's why he has the gold :) – user151841 May 13 '10 at 15:20
@meriton - :) At least we both arrived at the same solution. – dcp May 13 '10 at 15:20

score 3 · Answer 2 · answered May 13 '10 at 15:22

3

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.

answered May 13 '10 at 15:22

egrunin

24,650
8
50
93

score 2 · Accepted Answer · answered May 13 '10 at 15:15

2

Wrap it in a subselect?

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

answered May 13 '10 at 15:15

meriton

68,356
14
108
175

Deleted my answer as it would have returned everything, this is the correct solution +1! – djdd87 May 13 '10 at 15:20
I have to choose this one as the answer, because it actually has an English answer in it! :P – user151841 May 13 '10 at 15:34

score 0 · Answer 4 · answered May 13 '10 at 15:15

0

select the distinct values into a temp table first.

Then select out only the values you want.

answered May 13 '10 at 15:15

sparkkkey

158
1
10

SELECT DISTINCT on a field not appearing in recordset?

4 Answers4