0

Could any one please help me with MySql database query:

I have one table with below data:

work_date   ticket no   category
------------------------------------
7/15/2013   111           a
7/15/2013   222           b
7/15/2013   333           c
7/16/2013   111           a
7/16/2013   555           e
7/16/2013   333           f
7/17/2013   111           H

I need help in writing a query which will read all table data, then create 2 columns one ticket no and second as category. Under ticket column it would show count of all distinct ticket number and value under category should be the last category assigned to that ticket.

i'm using the following query

SELECT category, count(distinct(ticket_no)) 
FROM master 
group by category 
order by 2 DESC`

the ticket which is present in 2 or more categories are being counted multiple times, I want it to be counted just once and that too the latest one

Expected Result: 

Ticket No   Category
--------------------
111           H 
222           b 
333           f
555           e
Barranka
  • 20,547
  • 13
  • 65
  • 83
user2628791
  • 1

4 Answers4

0

I don't get your sample query. It is grouping by category and not ticket, but the sample data is by ticket.

In any case, I think this does what you want:

select ticket,
       substring_index(group_concat(category order by work_date desc), ',', 1)
from master m
group by ticket;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can use the following SQL Query:

SELECT DISTINCT(ticket_no), category FROM table_name SORT BY DESC;
Barranka
  • 20,547
  • 13
  • 65
  • 83
Akshat Singhal
  • 33
  • 3
0
select * from
(select ticket_no,category from master order by work_date desc) abc
group by ticket_no;

fiddle

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
-1
SELECT *
 FROM distinct t1
 WHERE NOT EXISTS (
   SELECT *
   FROM distinct t2
   WHERE t2.ticket_no= t1.ticket_no
     AND t2.work_date   > t1.work_date   
   );

here work_date should be a formatted timestamp

Bala Kalatheeswaran
  • 1
  • While code-only answers are often OK, it's better to explain what your code actually does, e.g. how does `WHERE NOT EXISTS` work. – Zeta Jun 30 '15 at 11:31
  • in the check t2.work_date > t1.work_date we will get the dates which are greater than of t2 where not exists almost works like left outer join thus it will return rows which doesnt exist in the t2 – Bala Kalatheeswaran Jul 02 '15 at 06:32
  • You should [edit](http://stackoverflow.com/posts/31136876/edit) additional information into your answer. Comments are easily overlooked. – Zeta Jul 02 '15 at 06:35
  • [NOT EXISTS](http://stackoverflow.com/questions/18164798/sql-group-by-from-other-table-and-invert-result) – Bala Kalatheeswaran Jul 02 '15 at 06:41
  • Again, [edit additional information into your answer](http://stackoverflow.com/posts/31136876/edit). NOT into comments. – Zeta Jul 02 '15 at 06:44