List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:June 28 2004 9:31am
Subject:Re: SQL challenge
View as plain text  
From: "John Mistler" <johnmistler@stripped>
> I have a difficult query for you that has me stumped.  The table has two
> columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate
(DATETIME).
> I need it to find:
>
> 1. The COUNT of REPEATED instances of the rowID in the last month.

This is relatively easy:

SELECT rowID, COUNT( rowID )  -1 AS countrepeat
FROM  <tablename>
WHERE theDate < CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-'), '01') AND
theDate >= CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-'), '01') - INTERVAL 1 MONTH
GROUP  BY rowID
HAVING countrepeat >0

See if I have time for #2 later...

Regards, Jigal

Thread
SQL challengeJohn Mistler28 Jun
  • Re: SQL challengeMartijn Tonies28 Jun
  • Re: SQL challengeJigal van Hemert28 Jun
  • Re: SQL challengeRoger Baklund28 Jun
    • Re: SQL challengeJohn Mistler28 Jun
      • Re: SQL challengeJigal van Hemert29 Jun
Re: SQL challengeSGreen29 Jun