-1

I am trying to get distinct result by only one column( message). I tried 

SELECT DISTINCT 
  [id], [message]
  FROM Example table
GROUP BY [message]

But it doesn't show desired result. Please let me know how can I do it?

Example table:

  id | Message     | 
    --  ------------
     1 | mike       | 
     2 | mike       |
     3 | star       | 
     4 | star       |  
     5 | star       | 
     6 | sky        |
     7 | sky        | 
     8 | sky        |

Result table:

id | Message     | 
--  ------------
 1 | mike       | 
 3 | star      |
 6 | sky       |
Cameroon P
  • 119
  • 3
  • 10
  • 1
    use `max` or `min` on the id column. – Vamsi Prabhala Apr 13 '16 at 14:30
  • Please google and learn about the GROUP BY clause in SQL. Also google the error message you got when you ran the query you posted. – Tab Alleman Apr 13 '16 at 14:32
  • _"it doesn't show desired result"_ No, because you got an error instead which is quite meaningful. You cannot use `Group By` on one column but select two columns. Consider that every `Message`-group can contain multiple `Id`'s, but you only want to see one. Sql-Server now wants to know which. Therefore either use `Min`,`Max` or other aggregate functions on the column(s) which are not part of the `Group By`. – Tim Schmelter Apr 13 '16 at 14:33
  • So *what* value do you want for the other column? You need to make a choice and then inform us (and, ultimately, SQL Server) what rules you want to use to select one of possibly many *different* values for the other column(s). – Damien_The_Unbeliever Apr 13 '16 at 14:33
  • Possible duplicate of [SELECT DISTINCT on one column](http://stackoverflow.com/questions/966176/select-distinct-on-one-column) – Tab Alleman Apr 13 '16 at 14:33

1 Answers1

2

Group by the column you want to be unique and use an aggregate function on the other column. You want the lowest id for every message, so use MIN()

select min(id) as id,
       message
from your_table
group by message
juergen d
  • 201,996
  • 37
  • 293
  • 362