Return all columns with distinct on multiple columns in SQL table

Question

I have 5 columns in sql table and I need all the 5 columns as out put but with distinct operation on three columns

Need to return all the columns with distinct operation on three column

How to achive this?

Timestamp                   Name    State     Context
2013-06-24 11:51:03.2550000 tst1    Started     E1
2013-06-24 11:56:03.2550000 tst1    Completed   E1
2013-06-24 11:51:03.2550000 tst1    Started     E1
2013-06-24 11:56:03.2550000 tst1    Completed   E1
2013-06-24 11:45:03.2550000 tst1    Started     E1
2013-06-24 11:50:03.2550000 tst1    Completed   E1
2013-06-24 11:45:03.2550000 tst1    Started     E1
2013-06-24 11:50:03.2550000 tst1    Completed   E1

Here I am getting all the distinct result for above table by applying distinct on three columns using below query. But I need the distinct of these three columns also need to select context column without applying distict on Context column

SELECT DISTINCT Timestamp,Name,State FROM TableName group by Timestamp,Name,State

Rephrasing my question :

I need to select unique columns from above table . Here only unquie column selection considered as Timestamp,Name,State

Timestamp                   Name    State     Context
2013-06-24 11:51:03.2550000 tst1    Started     E1
2013-06-24 11:56:03.2550000 tst1    Completed   E1
2013-06-24 11:45:03.2550000 tst1    Started E1
2013-06-24 11:50:03.2550000 tst1    Completed   E1

What about the other two columns? What kind of aggregation/modification you wanna do with them? Please add an example of the result you're trying to achieve — Vincenzo Maggio, Jun 26 '13 at 12:52
There are two unique Started in that data. A sample a single value for Context does not address the question. — paparazzo, Jun 26 '13 at 14:05
Blam : Updated the sample result data...Also not sure why people down voting this question. — usr021986, Jun 26 '13 at 14:36
Because in 6 edits you still don't have a clear question. You state 5 columns and the sample is 4 columns. In the sample there is only one value for the 4th so not clear how to handle that. For that sample you could just select distinct on all 4 columns. Did you try my answer? — paparazzo, Jun 26 '13 at 14:39
If it worked then consider checking it as the correct answer. — paparazzo, Jun 28 '13 at 08:06

score 7 · Accepted Answer · answered Jun 26 '13 at 12:59

7

Can group by 3 but must use some type of aggregate function like min or max on the other two

select col1, col2, col3, max(col4), min(col5) 
from tbl 
group by col1, col2, col3

answered Jun 26 '13 at 12:59

paparazzo

44,497
23
105
176

score -2 · Answer 2 · answered Jun 26 '13 at 12:53

-2

You can use a groupby for the three columns you want 'distinct'.

answered Jun 26 '13 at 12:53

Alicia

1,152
1
23
41

3

Wrong, you must have some sort of aggregate for the other columns! – Vincenzo Maggio Jun 26 '13 at 12:55

Return all columns with distinct on multiple columns in SQL table

2 Answers2

Linked