SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    hard sql query....

    table one structure {node}:

    Code:
    nid        type             title                 created
    
    10          provider        test one              1298107010
    11          provider        test two              1298107555    
    12          provider        example one            1300524695 
    13          provider        example two            1298081391
    14          provider        example three          1298082340
    15          company         example four           1298083519
    16....      company         example five          1298083559
    table two structure {votingapi_vote}:
    Code:
    content_id      value    value_type
    
    10                1          option
    10                 0          option
    11                 1          option
    12                 0          option
    15                 3          percent
    15                 2          percent
    16.....            0          option
    i want:

    get 22 titles list

    Code:
    ...
    test one
    test two
    example one
    example two 
    ...
    the queue order is:

    first according to table 2(i want to use the count value = 1 minus the count value = 0. this is the content_id's descending rule,

    then table 1 (desc the nid create time) type is provider.

    ps:For each content_id, the number of rows with value=1 minus the number of rows with value=0.. each content_id has many value=0 or value=1

  2. #2
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1, the value of nid is equals the value of content_id in table 2.

    the title list queue order is:

    1, first according to table 2 content_id descending the tile list(decending content_id using "For each content_id, the number of rows with value=1 minus the number of rows with value=0" )

    2, because table2 maybe less than 22 records and has the same value when the number of rows with value=1 minus the number of rows with value=0. when emerge this condition. using the created field in table 1 to descending the tile

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your explanation makes very little sense

    this is as close as i got to understanding your very weird requirements --
    Code:
    SELECT 1 AS seq
         , content_id AS nid
         , COUNT(CASE WHEN value = 1
                      THEN 0 ELSE NULL END) -
           COUNT(CASE WHEN value = 0
                      THEN 1 ELSE NULL END) AS sort_value
      FROM votingapi_vote
    UNION ALL
    SELECT 2 AS seq
         , nid
         , created AS sort_value
      FROM node
     WHERE type = 'provider'
    ORDER
        BY seq
         , id DESC
         , sort_value DESC LIMIT 22
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    table two structure {votingapi_vote}:
    Code:
    Code:
    content_id      value    value_type
    
    10                1          option
    10                 0          option
    11                 1          option
    12                 0          option
    15                 3          percent
    15                 2          percent
    16.....            0          option
    output the content_id field and descending it, the rule is according to the value(the count of each content_id 's value=1 minus the count of each content_id 's value=0 ,each content_id has many value=0 or value=1). where value_type=option.

    namely:For each content_id, the number of rows with value=1 minus the number of rows with value=0

    is there a way to get that?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by runeveryday View Post
    namely:For each content_id, the number of rows with value=1 minus the number of rows with value=0
    Code:
    SELECT content_id 
         , COUNT(CASE WHEN value = 1
                      THEN 0 ELSE NULL END) -
           COUNT(CASE WHEN value = 0
                      THEN 1 ELSE NULL END) AS diff
      FROM votingapi_vote
    GROUP
        BY content_id
    it appears i forgot the GROUP BY clause earlier,,,
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i doesn't understand this well. could you explain it . many thanks.
    COUNT(CASE WHEN value = 1
    THEN 0 ELSE NULL END) -
    COUNT(CASE WHEN value = 0
    THEN 1 ELSE NULL END) AS diff

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the 1st COUNT function counts the number of 1 values

    the 2nd COUNT function counts the number of 0 values

    then the number of 0's is subtracted from the number of 1's, and the result is called "diff"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •