Select rows from table order by date and limit similar date by 1

Question

I have a table which has

id | name    | datetime            | percentage
-----------------------------------------------
1  | amit    | 2015-06-20 18:30:00 | 60
2  | pavan   | 2015-06-20 18:30:00 | 60
3  | vineet  | 2015-06-20 18:30:00 | 50
4  | pavan   | 2015-06-21 19:30:00 | 70
5  | vineet  | 2015-06-21 19:30:00 | 70
6  | amit    | 2015-06-21 18:30:00 | 60
7  | vineet  | 2015-06-19 18:30:00 | 80

i want to select rows and order them by datetime and percentage and limit them by 1 row. i dont want multiple dates

    id | name    | datetime            | percentage
    -----------------------------------------------
    7  | vineet  | 2015-06-19 18:30:00 | 80
    1  | amit    | 2015-06-20 18:30:00 | 60   
    6  | amit    | 2015-06-21 18:30:00 | 60    
    4  | pavan   | 2015-06-21 19:30:00 | 70

Mukesh Kalgude · Accepted Answer · 2015-07-28T06:11:14.463

2

Try this query

select a.* from tablename a
join (select min(c.id) as id ,c.datetime
      from tablname c
      join(
         select max(percentage) as percentage, datetime
      from tablname
        group by datetime
        )  d on c.datetime=d.datetiem and c.percentage=d.percentage
      group by c.datetime) b on a.id=b.id
order by a.datetime,a.percentage

edited Jul 28 '15 at 06:11

answered Jul 28 '15 at 05:44

Mukesh Kalgude

4,814
2
17
32

the inner query should also order by percentage. in the above table i have 1,2,3 with same datetime and percentage are different so i want the row with maximum percentage – amit Jul 28 '15 at 06:03
the query which u gave got the desired output but when i changed the percentage of id 1 to 50 the output should be 7,2,6,4 but still its showing 7,1,6,4 – amit Jul 28 '15 at 06:09
@amit now you can try – Mukesh Kalgude Jul 28 '15 at 06:11
thanks mukesh it worked – amit Jul 28 '15 at 06:22
i have another problem. When i tried the above query with normal table it worked but when i tried with temporary table it says cannot reopen table. – amit Jul 28 '15 at 11:43
@amit Try to your self if not getting result to create other question – Mukesh Kalgude Jul 28 '15 at 11:45

score 0 · Answer 2 · edited Jul 28 '15 at 07:44

0

Try this query

SELECT * 
    FROM mytable 
    WHERE id IN (SELECT MAX(id) 
                FROM mytable 
                GROUP BY datetime)
    ORDER BY datetime

edited Jul 28 '15 at 07:44

Adrian C.

1,647
1
23
27

answered Jul 28 '15 at 05:44

Akhil

2,602
23
36

Select rows from table order by date and limit similar date by 1

2 Answers2