SELECT Substring(customer, 1, 5) + '000', 
       VALUE, 
       COUNT(*) 
FROM   customers 
GROUP  BY Substring(customer, 1, 5) + '000', 
          VALUE 

Customer        Value  Count
-------------------------------------
12345000	120	5
12345000	123	2
12345000	997	1
98765000	120	5
11111000	390	1
22222000	120	1

Customer        Value
---------------------
12345000	120
12345001	123
12345002	123
12345023	997
98765000	120
11111000	390
22222000	120

Frozen Peach fucked around with this message at 16:55 on Mar 23, 2011

Frozen Peach fucked around with this message at 15:08 on Mar 24, 2011

McGlockenshire fucked around with this message at 07:32 on Mar 28, 2011

threads(
  id
  userId
  deleted
)

Peanut and the Gang fucked around with this message at 03:19 on Apr 6, 2011

SELECT masterlist.id,
       genre,
       medium,
       YEAR,
       country
FROM   masterlist,
       dvd,
       digital
WHERE  masterlist.id <> digital.id
        OR masterlist.id <> dvd.id  

Whilst farting I fucked around with this message at 03:29 on Apr 10, 2011

SELECT
  masterlist.id,
  genre,
  medium,
  YEAR,
  country
  FROM      masterlist M
  LEFT JOIN dvd        DV ON DV.id = M.id
  LEFT JOIN digital    DI ON DI.id = M.id
  WHERE
    DV.id IS NULL AND
    DI.id IS NULL

wellwhoopdedooo fucked around with this message at 04:31 on Apr 10, 2011

Cock Democracy fucked around with this message at 14:55 on Apr 10, 2011

  $items=Array();
  $result=db_query("
            SELECT DISTINCT subject_id
            FROM item_subject
            WHERE item_id = '$id'
  ");
  $in=array();
  while($sid=db_fetch_object($result)){
    $in[]=$sid->subject_id;
  }
  if($in){
    $in=implode(',',$in);
    $result=db_query("
            SELECT DISTINCT item_id
            FROM item_subject
            WHERE subject_id IN(".$in.")
    ");
    $in=array();
    while($iid=db_fetch_object($result)){
      $in[]=$iid->item_id;
    }
    if($in){
      $in=implode(',',$in);
      $result=db_query("
        SELECT item_id AS ID, Title
        FROM item
        WHERE item_id
        IN (".$in.")
        AND
        item_id != ".$id."
        ORDER BY RAND()
        LIMIT ".$num_items."
      ");
      while ($row = db_fetch_object($result)) {
        $items[$row->ID] = $row->Title;
      }
    }
  }

Tad Naff fucked around with this message at 22:26 on Apr 12, 2011

WITH      has_been_full 
          AS (SELECT loc_id
                    ,enddate AS date_under_capacity
              FROM   (SELECT el.loc_id
                            ,el.enddate
                            ,row_number() OVER (PARTITION BY el.loc_id ORDER BY el.enddate DESC) AS most_recent
                      FROM   (SELECT DISTINCT 
                                     el.el_loc_id  AS loc_id
                                    ,el.el_enddate AS enddate
                                    ,(SELECT count(*)
                                      FROM   entrylog 
                                      WHERE  el_loc_id = el.el_loc_id
                                      AND    el_startdate < el.el_enddate
                                      AND    (el_enddate IS NULL OR el_enddate > el.el_enddate)
                                     ) AS count_remaining
                                    ,(SELECT count(*)
                                      FROM   entrylog 
                                      WHERE  el_loc_id = el.el_loc_id
                                      AND    el_startdate < el.el_enddate
                                      AND    (el_enddate IS NULL OR el_enddate >= el.el_enddate)
                                     ) AS previous_count
                              FROM   entrylog el
                              WHERE  el.el_enddate IS NOT NULL
                             ) el
                      JOIN   location loc
                             ON loc.loc_id = el.loc_id
                      WHERE  el.count_remaining < loc.loc_capacity
                      AND    el.previous_count >= loc.loc_capacity
                     )
              WHERE  most_recent = 1
             )
         ,currently_full 
          AS (SELECT   loc.loc_id 
              FROM     location loc
              JOIN     entrylog el
                       ON el.el_loc_id = loc.loc_id
              HAVING   count(CASE 
                               WHEN el_startdate < getdate() 
                                    AND (el_enddate IS NULL OR el_enddate > getdate()) 
                               THEN
                                 1
                             END
                            ) >= loc.loc_capacity
              GROUP BY loc.loc_id
                      ,loc.loc_capacity
             )
SELECT    loc.loc_id
         ,CASE
            WHEN cf.loc_id IS NOT NULL THEN
              NULL
            WHEN hbf.loc_id IS NOT NULL THEN
              hbf.date_under_capacity
            ELSE
              loc.loc_opendate
          END AS date_under_capacity
FROM      location loc
LEFT JOIN currently_full cf
          ON loc.loc_id = cf.loc_id
LEFT JOIN has_been_full hbf
          ON loc.loc_id = hbf.loc_id

Goat Bastard fucked around with this message at 22:03 on Apr 13, 2011