Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 30th, 2012  |  syntax: None  |  size: 1.15 KB  |  hits: 17  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Words unique to a user - where not in
  2. Word Table:
  3.     word - varchar(50)
  4.     user_id - integer
  5.        
  6. SELECT  
  7.     word, count(word) as count
  8. FROM
  9.     words
  10. WHERE        
  11.     word not in (select word from words where user_id <> 99 group by word)
  12.     and user_id = 99
  13. GROUP BY word
  14. ORDER BY count desc LIMIT 20
  15.        
  16. SELECT  
  17.     word, count(word) as count
  18. FROM
  19.     words
  20. WHERE        
  21.     word not in (select distinct word from words where user_id <> 99)
  22.     and user_id = 99
  23. GROUP BY word
  24. ORDER BY count desc LIMIT 20
  25.        
  26. SELECT  
  27.     w1.word,
  28.     COUNT(w1.word) as count
  29. FROM
  30.     words w1
  31. WHERE        
  32.     NOT EXISTS (
  33.         SELECT 1
  34.         FROM
  35.             words w2
  36.         WHERE
  37.             w2.user_id <> 99
  38.         AND
  39.             w1.word = w2.word
  40.         )
  41. AND
  42.     w1.user_id = 99
  43. GROUP BY
  44.     w1.word
  45. ORDER BY
  46.     count DESC
  47. LIMIT 20;
  48.        
  49. SELECT
  50.     w1.word,
  51.     COUNT(w1.word) AS count
  52. FROM
  53.     words w1
  54.         LEFT JOIN words w2 ON (w1.word = w2.word AND w1.user_id <> w2.user_id)
  55. WHERE
  56.     w1.user_id = 99
  57. AND
  58.     w2.word IS NULL
  59. GROUP BY
  60.     w1.word
  61. ORDER BY
  62.     count DESC
  63. LIMIT 20;
  64.        
  65. CREATE INDEX idx_word_user ON words ( word, user_id);
create a new version of this paste RAW Paste Data