SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot skanxalot's Avatar
    Join Date
    Mar 2003
    Location
    Dallas, TX
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting same column twice, with different conditions - Possible?

    I have a table that looks like this:

    DATA
    ID (pk)
    KeywordID
    Rank
    Date
    URL

    Can I use one select statement to pull a resultset that looks like this:

    ID | KeywordID | Rank1 | Rank2 | URL

    Where Rank1 would be based on one condition and Rank2 would be based on a different condition. I was hoping I could use a query similar to this, but apparently not.

    Code:
    SELECT     ID, KeywordID, Rank,
    (SELECT     Rank FROM          RankingData
    WHERE      DateStamp = '1/19/2006') AS Expr1
    FROM         RankingData
    WHERE     (DateStamp = '1/18/2006')
    Can this be done?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    your query will work, with one addition: you have to tell the SQL server how to relate the two queries. i'm guessing that would be KeywordID:
    Code:
    SELECT ID
         , KeywordID
         , Rank as Rank1
         , (SELECT Rank
              FROM RankingData AS inner
             WHERE outer.KeywordID = inner.KeywordID
               AND DateStamp = '1/19/2006') AS Rank2
      FROM RankingData AS outer
     WHERE DateStamp = '1/18/2006'

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    inner and outer are not that good correlation names, as they are reserved words in SQL.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    #$!%^!T$@#$@
    Code:
    SELECT ID
         , KeywordID
         , Rank as Rank1
         , (SELECT Rank
              FROM RankingData AS i
             WHERE o.KeywordID = i.KeywordID
               AND DateStamp = '1/19/2006') AS Rank2
      FROM RankingData AS o
     WHERE DateStamp = '1/18/2006'
    good catch, SB!

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    code
    SELECT ID, KeywordID, Rank, from data where rank1 = 'your_criteria' and rank2 = 'another'
    Only draw back i see, is that in your above table description, there is no rank1 or rank2. If they are seperate tables, what is the relationship bw them ?

  6. #6
    SitePoint Zealot skanxalot's Avatar
    Join Date
    Mar 2003
    Location
    Dallas, TX
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys...that's awesome!

  7. #7
    SitePoint Zealot skanxalot's Avatar
    Join Date
    Mar 2003
    Location
    Dallas, TX
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm...follow up question. It appears that this query only works if the recordset selected by the subquery (those rows matching 1/19/2006 in this case) is less in number than the rows matched by the second condition (1/18/2006). Why is this, and is there a work-around?

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hold on. the query as written depends on tthere being only ONE row per date per rankingid. are you saying that there will be multiple rows? if so, how will you decide which one you want to use?

  9. #9
    SitePoint Zealot skanxalot's Avatar
    Join Date
    Mar 2003
    Location
    Dallas, TX
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure I understand your question, forgive me. Yeah, I'm creating a datagrid, and there could be 100 for each date, potentially. I successfully selected multiple rows from the subquery earlier using the above code, but when I raise the number of records, I get an error.

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, example data time. does your data look like this?
    Code:
    ID, KeywordID, Rank, Date
    1, 1, 4, 1/18/06
    2, 1, 3, 1/20/06
    3, 2, 5, 1/18/06
    4, 2, 5, 1/19/06
    5, 2, 5, 1/20/06
    or does it look like this?
    Code:
    ID, KeywordID, Rank, Date
    1, 1, 4, 1/18/06
    2, 1, 3, 1/19/06
    3, 2, 5, 1/18/06
    4, 2, 3, 1/19/06
    5, 2, 5, 1/19/06
    notice in the second set of data, for keywordid 2, there are two rankings on the same day.

    so which one matches your data, or am i completely off?

  11. #11
    SitePoint Zealot skanxalot's Avatar
    Join Date
    Mar 2003
    Location
    Dallas, TX
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, it looks similar to the second example. Typically, each keyword is ranked 5-6 times per day. I'll try to provide a better picture of my schema in the morning.

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, my original question stands: when the subqery returns multiple results, how do you want to handle that? i.e., which ranking do you want to return? as written, the subquery can return only ONE result. NO MORE!

    maybe this is what you want?
    Code:
    SELECT ID
         , KeywordID
         , avg(Rank) as Rank1
         , (SELECT ang(Rank)
              FROM RankingData AS i
             WHERE o.KeywordID = i.KeywordID
               AND DateStamp = '1/19/2006') AS Rank2
      FROM RankingData AS o
     WHERE DateStamp = '1/18/2006'
    GROUP
        BY ID
         , KeywordID


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
  •