Click here to Skip to main content
15,745,430 members
Home / Discussions / Database
   

Database

 
AnswerRe: call sql scripts from others folders Pin
CHill6020-Jun-16 3:41
mveCHill6020-Jun-16 3:41 
QuestionPrioritized joining - Updated Pin
Jörgen Andersson16-Jun-16 9:39
professionalJörgen Andersson16-Jun-16 9:39 
AnswerRe: Prioritized joining Pin
Mycroft Holmes16-Jun-16 13:05
professionalMycroft Holmes16-Jun-16 13:05 
GeneralRe: Prioritized joining Pin
Jörgen Andersson16-Jun-16 21:58
professionalJörgen Andersson16-Jun-16 21:58 
GeneralRe: Prioritized joining Pin
Mycroft Holmes17-Jun-16 2:52
professionalMycroft Holmes17-Jun-16 2:52 
GeneralRe: Prioritized joining Pin
Jörgen Andersson17-Jun-16 3:59
professionalJörgen Andersson17-Jun-16 3:59 
GeneralRe: Prioritized joining Pin
Jörgen Andersson29-Jun-16 10:28
professionalJörgen Andersson29-Jun-16 10:28 
AnswerRe: Prioritized joining - Updated Pin
Richard Deeming17-Jun-16 1:57
mveRichard Deeming17-Jun-16 1:57 
How about something like this:
  1. Cross-join the two tables;
  2. Generate the ranking value for each pair;
  3. Generate a ROW_NUMBER, partitioned by the primary key of Y, and ordered by the ranking value (descending);
  4. Select the rows where the row number is 1;

SQL
WITH cteRanked As
(
    SELECT
        X.PK As XPK,
        X.OtherColumnsFromX,
        Y.PK As YPK,
        Y.OtherColumnsFromY,
        RankingFunction(X.Value, Y.Value) As R
    FROM
        X CROSS JOIN Y
),
cteOrdered As
(
    SELECT
        XPK,
        OtherColumnsFromX,
        YPK,
        OtherColumnsFromY,
        ROW_NUMBER() OVER (PARTITION BY YPK ORDER BY R DESC) As RN
    FROM
        cteRanked
)
SELECT
    XPK,
    OtherColumnsFromX,
    YPK,
    OtherColumnsFromY
FROM
    cteOrdered
WHERE
    RN = 1
;




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


GeneralRe: Prioritized joining - Updated Pin
Jörgen Andersson17-Jun-16 3:30
professionalJörgen Andersson17-Jun-16 3:30 
GeneralRe: Prioritized joining - Updated Pin
Richard Deeming17-Jun-16 12:29
mveRichard Deeming17-Jun-16 12:29 
GeneralRe: Prioritized joining - Updated Pin
Jörgen Andersson18-Jun-16 4:18
professionalJörgen Andersson18-Jun-16 4:18 
GeneralRe: Prioritized joining - Updated Pin
Jörgen Andersson29-Jun-16 10:44
professionalJörgen Andersson29-Jun-16 10:44 
QuestionPivot Not Working Pin
MadDashCoder11-Jun-16 8:01
MadDashCoder11-Jun-16 8:01 
AnswerRe: Pivot Not Working Pin
Mycroft Holmes11-Jun-16 14:51
professionalMycroft Holmes11-Jun-16 14:51 
AnswerRe: Pivot Not Working Pin
CHill6012-Jun-16 0:33
mveCHill6012-Jun-16 0:33 
GeneralRe: Pivot Not Working Pin
MadDashCoder12-Jun-16 3:56
MadDashCoder12-Jun-16 3:56 
GeneralRe: Pivot Not Working Pin
CHill6012-Jun-16 4:43
mveCHill6012-Jun-16 4:43 
GeneralRe: Pivot Not Working Pin
MadDashCoder12-Jun-16 8:36
MadDashCoder12-Jun-16 8:36 
GeneralRe: Pivot Not Working Pin
CHill6012-Jun-16 8:54
mveCHill6012-Jun-16 8:54 
GeneralRe: Pivot Not Working Pin
Mycroft Holmes12-Jun-16 12:58
professionalMycroft Holmes12-Jun-16 12:58 
QuestionDatabase backed up type virtual device- not mainteance ? Pin
SreeDBA5-Jun-16 5:28
SreeDBA5-Jun-16 5:28 
AnswerRe: Database backed up type virtual device- not mainteance ? Pin
Richard Deeming6-Jun-16 5:13
mveRichard Deeming6-Jun-16 5:13 
Questionsyntax error for simple IF - THEN Pin
Jassim Rahma1-Jun-16 7:50
Jassim Rahma1-Jun-16 7:50 
AnswerRe: syntax error for simple IF - THEN Pin
thatraja1-Jun-16 9:04
professionalthatraja1-Jun-16 9:04 
GeneralRe: syntax error for simple IF - THEN Pin
Jassim Rahma1-Jun-16 21:01
Jassim Rahma1-Jun-16 21:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.