user2801618 user2801618 - 4 months ago 8
SQL Question

How to select first or last value from phone numbers?

I have a TABLE

select * from TEL_NUM

+----+---------+----------|
| id | TEL_NUM |REG_DATE |
+----+---------+----------+
| 1 | 6111 |20150101 |
| 2 | 6112 |20150102 |
| 3 | 6112 |20150102 |
| 4 | 6112 |20150103 |
| 5 | 6111 |20150103 |
| 6 | 6113 |20150103 |
| 7 | 6113 |20150104 |
| 8 | 6113 |20150105 |
| 9 | 6113 |20150106 |
+----+---------+----------+


In my case I have to display record just 3 rows because there is just 3 tel number with different date.
How to select one by one tel number? Should I use
max
date?
I need how many times they called on each different date for all the dates in table. Like this information:


6111 called once on 2015 01 01 6112 called 2 times on 2015 01 02 6112
called once on 2015 01 03 6111 called once on 2015 01 03 6113 called
once 2015 01 03 6113 called once 2015 01 04 6113 called once 2015 01
05 6113 called once 2015 01 06

Answer

It's not clear what you need. Just the phone numbers? Then use SELECT DISTINCT. The phone number and the most recent reg_date for each phone number? Then GROUP BY tel_num and in the SELECT clause, include MAX(reg_date). If neither, then what?

EDIT: Based on further input from OP - he needs caller TEL_NUM, the REG_DATE, and how many times that caller called on that date. This is a job for the aggregate function COUNT(*).

select   tel_num, reg_date, count(*) as cnt
from     table_name
group by tel_num, reg_date
;

You may need to add ORDER BY TEL_NUM, REG_DATE (or if you need it the other way around, ORDER BY REG_DATE, TEL_NUM).