CODEWITHSUNDEEP
mysqlsql
ATMathew
ATMathew

Reputation: 12856

Select the earliest and latest dates

So I have a table which is structured in the following format:

in_click  first_name      create_date 
100       joe             2011-10-01 10:01
100       joe             2011-10-01 10:05
100       joe             2011-10-01 10:07
100       joe             2011-10-01 10:08
100       joe             2011-10-01 10:10
101       sara            2011-10-01 10:15
101       sara            2011-10-01 10:17
101       sara            2011-10-01 10:20
101       sara            2011-10-01 10:22

For each first name, I want to select the first and last rows. So it should be the first date with the name joe and the last date where the first name is joe. Kinda like an if-else statement, but I'm just not sure how to get that information in MySQL.

Help?!

I'm running the following query to just get everything:

SELECT t.in_click_id, t.keyword, c.lead_id, c.first_name, c.last_name, 
ls.create_date, ls.discriminator, l.affiliate_id 
FROM lead_status AS ls
INNER JOIN leads AS l ON l.id = ls.lead_id 
INNER JOIN tracker AS t ON l.in_click_id = t.in_click_id
INNER JOIN contacts AS c ON ls.lead_id = c.lead_id 
WHERE l.affiliate_id NOT IN('1002','1003')
AND ls.create_date BETWEEN '2011-11-09' AND '2011-11-10';

This is what I'm trying to get:

in_click  first_name      create_date 
100       joe             2011-10-01 10:01
100       joe             2011-10-01 10:10
101       sara            2011-10-01 10:15
101       sara            2011-10-01 10:22

Upvotes: 2

Views: 16559

Answers (3)

Wil P
Wil P

Reputation: 3371

This works with the data you provided, can't say it would work in MySQL, but it works in SQL Server.

select t.in_click, 
    t.first_name,
    t.create_date
from tracker t
where 
    t.create_date = (select min(create_date) from tracker where in_click = t.in_click)
    or t.create_date = (select max(create_date) from tracker where in_click = t.in_click)

Upvotes: 7

Jared Peless
Jared Peless

Reputation: 1120

By first and last do you mean the earliest date and latest date in the table? If so, you will need to query and use the MAX and MIN functions for the dates.

Upvotes: 0

r0ast3d
r0ast3d

Reputation: 2635

Have you use the min and max functions ?

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_min

Upvotes: 0

Related Questions