Selecting same column twice, with different conditions - Possible?

[skanxalot]

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.

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?

[longneck]

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:

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'

[swampBoogie]

inner and outer are not that good correlation names, as they are reserved words in SQL.

[longneck]

#$!%^!T$@#$@

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!

[afrika]

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 ?

[skanxalot]

Thanks guys...that's awesome!

[skanxalot]

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?

[longneck]

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?

[skanxalot]

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.

[longneck]

ok, example data time. does your data look like this?

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?

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?

[skanxalot]

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.

[longneck]

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?

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