Selecting Multipal Row in Mysql

Question

I have these table in my database:

id  Name    Order Date
1   A       29-Dec
2   B       15-Dec
3   C       12-Oct
4   A       15-Dec
5   B       15-Sep
6   A       15-Sep
7   B       15-Aug
8   C       15-Aug
9   A       15-Jul

Expected Output:-

Name    QTY     Last Order Date
A       4       29-Dec
B       3       15-Dec
C       2       12-Oct

How to do this, Please help.

Also I have to select only those record whose last order date is greater than 10 days.

The purpose behind this query is to send such customer a notification who are not ordering since 10 days. How to do this, Please help...

Possible duplicate of [Get records with max value for each group of grouped SQL results](http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — Henrique Barcelos, Jan 18 '16 at 13:17

Cédric Miachon · Answer 1 · 2016-01-19T09:09:34.853

1

Try this :

SELECT name, count(name), MAX(date) as latest_order_date
FROM Orders 
GROUP BY name 
ORDER BY name ASC

For your comment about how to select only those record where last order date is greater than 10 days, you can use HAVING and DATEDIFF

SELECT name, count(name), MAX(date) as latest_order_date
FROM Orders 
GROUP BY name 
HAVING DATEDIFF(NOW(),latest_order_date) > 10
ORDER BY name ASC

edited Jan 19 '16 at 09:09

answered Jan 18 '16 at 13:20

Cédric Miachon

344
1
8

I was about to answer the same thing. Here is a [working fiddle](http://sqlfiddle.com/#!9/7cfd2e/4). – Henrique Barcelos Jan 18 '16 at 13:30
Hi Sir, it worked.... Great Job, Thank you Cedric Miachon, Stay Blessed – Saleem Jafar Jan 19 '16 at 05:41
From the above table I want to select only those record where last order date is greater than 10 days. For Example if any customer did not placed any order since last 10 day, send them notification. How to do this. please help.... – Saleem Jafar Jan 19 '16 at 08:17
like that : SELECT name, count(name), MAX(date) as latest_order_date FROM Orders GROUP BY name HAVING DATEDIFF(NOW(),latest_order_date) > 10 ORDER BY name ASC – Cédric Miachon Jan 19 '16 at 09:05
@SaleemJafar I update the answer below – Cédric Miachon Jan 19 '16 at 09:10
Your are absolutely wright. It worked perfectly. Thank YOu – Saleem Jafar Jan 19 '16 at 11:08

score 0 · Accepted Answer · answered Jan 18 '16 at 12:39

0

Please check this... the field and table names are different, please change it accordingly

select name, count(name), date from abc group by name order by date desc

answered Jan 18 '16 at 12:39

Aswani Prakash

127
5

Yes It worked, I got it very helpful – Saleem Jafar Jan 19 '16 at 05:42

Selecting Multipal Row in Mysql

2 Answers2