CODEWITHSUNDEEP
mysqlsql
Ace
Ace

Reputation: 33

how to get distinct result in sql?

I am trying to get distinct result of following table 

id  | name   | created_on
1   | xyz    | 2015-07-04 09:45:14
1   | xyz    | 2015-07-04 10:40:59
2   | abc    | 2015-07-05 10:40:59

I want distinct id with latest created_on means following result

1   | xyz    | 2015-07-04 10:40:59
2   | abc    | 2015-07-05 10:40:59

How to get above result by sql query?

Upvotes: 2

Views: 69

Answers (4)

Vivek_Neel
Vivek_Neel

Reputation: 1353

You can use the keyword DISTINCT like 

SELECT DISTINCT

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Assuming that id/name is always a pair:

select id, name, max(created_on)
from table
group by id, name;

It is safer to include both in the group by. I also find it misleading to name a column id when it is not unique for the table.

Upvotes: 0

Ormoz
Ormoz

Reputation: 3013

Try:

select id,max(name), max(created_on) from table_name group by id

Additional Note:

As it appears, your table is not normalized. That is, you store the name along with id in this table. So you may have these two rows simultaneously:

 id  |  name  | created_on   
  1  |    a   |  12-12-12 
  1  |    b   |  11-11-11

If that state is not logically possible in your model, you should redesign your database by splitting this table into two separate tables; one for holding id-name relationship, and another to hold id-created_on relationship:

table_1 (id,name)
table_2 (id,created_on)

Now, to get last created_on for each id:

 select id,max(created_on) from table_2

And if you want to hold name in the query:

 select t1.id, t1.name, t2.created_on from table_1 as t1 inner join
 (select id, max(created_on) as created_on from table_2) as t2
 on t1.id=t2.id

Upvotes: 1

Nikhil Batra
Nikhil Batra

Reputation: 3148

Try this:

Select id, name, max(created_on) as created_on from table group by id

Upvotes: 3

Related Questions