2

Let's say I have a table with 2 columns number and name.

number  name 
--------------
0       name1
3       name4
3       name2
5       name1

So the number column has repeats in it and one or more (distinct) names assigned to each number. But for example, number 3 could have the same name as number 5.

How do I show all the distinct names for each number using group by in such a way that I get as a result both number and name.

I am trying to do this but I can't figure it due to must having the columns in SELECT in GROUP BY also.

user229044
  • 232,980
  • 40
  • 330
  • 338
user461316
  • 893
  • 3
  • 12
  • 31

2 Answers2

2

Is this what you want?

SELECT NUMBER, NAME
  FROM TABLENAME
    GROUP BY NUMBER, NAME
    ORDER BY NUMBER
VoodooChild
  • 9,776
  • 8
  • 66
  • 99
  • In a way, I want to group everything by Number. So I want to get the DISTINCT number and for each number I want to get all the names that correspond with that number. – user461316 Sep 29 '10 at 04:19
  • Not sure if could do that, unless you meant that you wanted apply an order? Also, what if you have the number 3 two times with different names?- you will have to show the result twice: one for (3, name1) and one for (3, name2) – VoodooChild Sep 29 '10 at 04:21
  • I think I lose myself in the thoughts. You are right. I can just order it. – user461316 Sep 29 '10 at 04:25
0

First Thing 'Number' is a reserved word in sql so don't use it as a column name

Suppose your 'Number' column name is 'Number1"

then try this

select name, number1 from tableName GROUP BY name, number1

adi78
  • 1