CODEWITHSUNDEEP
sqldb2
user2447298
user2447298

Reputation: 13

SQL stuck with this one

I need to select rows from a table where only one row exists with that ID.

Example Table, with two columns.
ID first and timestamp.
so first row is ID 1, Timestamp 01/01/2013

ID      Timestamp
1       01/01/2013
2       02/02/2013
3       04/02/2013
3       07/02/2013
4       20/03/2013

So, for this table I'd only like to return the records with ID 1,2,4 as these all have only 1 row.

I then need to only return these rows if they have a timestamp of more than one month ago. The timestamp is in this format 2012-11-12-22.00.15.541231

Can anyone help please?

Upvotes: 1

Views: 234

Answers (5)

Andriy M
Andriy M

Reputation: 77667

If you only need to return IDs and timestamps, you can avoid using a subquery, going like this:

SELECT ID, MAX(Timestamp) AS Timestamp
FROM atable
GROUP BY ID
HAVING COUNT(*) = 1
   AND MAX(Timestamp) < CURRENT TIMESTAMP - 1 MONTH
;

Upvotes: 0

pratik garg
pratik garg

Reputation: 3342

you can use group by clause for this output -

Edit

adding Condition on datestamp column

SELECT *
  FROM Your_Table_Name
 WHERE Id IN
       (SELECT Id FROM Your_Table_Name GROUP BY Id HAVING COUNT(*) = 1)
 AND time_created < current_timestamp - 1 month

Upvotes: 0

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

SQL Fiddle

MySQL 5.5.30 Schema Setup:

CREATE TABLE Table1
    (`ID` int, `Timestamp` date)
;

INSERT INTO Table1
    (`ID`, `Timestamp`)
VALUES
    (1, '2013-01-01'),
    (2, '2013-02-02'),
    (3, '2013-04-02'),
    (3, '2013-07-02'),
    (4, '2013-03-20')
;

Query 1:

select ID,Timestamp
from Table1
where DATEDIFF(curdate(),DATE_SUB(Timestamp,INTERVAL 1 MONTH))>=1
group by ID
having count(ID)=1

Results:

| ID |                       TIMESTAMP |
----------------------------------------
|  1 |  January, 01 2013 00:00:00+0000 |
|  2 | February, 02 2013 00:00:00+0000 |
|  4 |    March, 20 2013 00:00:00+0000 |

Upvotes: 0

mvp
mvp

Reputation: 116127

This should work for you:

SELECT *
FROM mytable
WHERE id NOT IN (
    SELECT id
    FROM mytable
    GROUP BY id
    HAVING COUNT(*) > 1
) AND time_created < current_timestamp - 1 month

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

Something like this should work.

select id
from yourtable
where timestampfield <= current timestamp - 1 month
group by id
having count(*) = 1

The minus 1 month part came from here.

Upvotes: 0

Related Questions