Navnet på kolonne med største værdi

Først skal vi sørge for at have en tabel, så vi kan arbejde med tallene på en mere hensigtsmæssig måde. Tabellen skal se således ud:
PERSON, KOLONNE, RESULTAT. Vi kalder tabellen for resultat2.

Når vi har denne tabel kan vi udføre følgende steps for at få det ønskede resultat frem:

Step1 (nulstil tabel)
  DELETE resultat2

Step2 (overfør data)
  INSERT INTO resultater2
  SELECT    person, 1, resultat1 FROM resultater
  UNION
  SELECT    person, 2, resultat2 FROM resultater
  UNION
  SELECT    person, 3, resultat3 FROM resultater
  UNION
  SELECT    person, 4, resultat4 FROM resultater

Step 3 (Udvælg data)
  SELECT person, MAX(ra.resultat),
  (
    SELECT TOP 1 rb.kolonne
    FROM resultater2 rb
    WHERE ra.person = rb.person
    AND rb.resultat = MAX(ra.resultat)
  )
  FROM resultater2 ra
  GROUP BY person

Det skulle gerne give det ønskede resultat.

Hilsen
Webfreelancer.dk

-> webfreelancer
wauw - det vil jeg prøve lidt senere idag, selvom jeg ikke forstår det hele lige nu.

:-) k_l

Følgende SQl virker i Access, og giver største resultat og navnet på kolonnen
som kolonner i resultatsættet. functionen IIF er vidst specifik for Access, og kan hedde noget andet på andre db'er.

Eksempel:
ID    res1    res2    res3    MAXRES    MAXCOL
2    4    5    6    6    res3
3    9    7    8    9    res1

SQL:
SELECT
    Table1.res1,
    Table1.res2,
    Table1.res3,
    IIf(
        IIf(Table1.res1>Table1.res2,Table1.res1,Table1.res2) > Table1.res3,
        IIf(Table1.res1>Table1.res2,Table1.res1,Table1.res2),
        Table1.res3
    ) AS MAXRES,

    IIf(
        IIf(Table1.res1>Table1.res2,Table1.res1,Table1.res2) > Table1.res3,
        IIf(Table1.res1>Table1.res2,'res1', 'res2'),
        'res3'
    ) AS MAXCOL
FROM table1

Mvh. Jesperfj

-> webfreelancer.dk

Det lykkes ikke lige for mig - er det sådan du mener? Det vil ikke acceptere step 2

'Step 1 (nulstil tabel)
    Set objRec = Server.CreateObject("ADODB.RecordSet")
    sql = "DELETE FROM resultat2"
    objRec.Open sql, objConn, 1,3
    set objRec = nothing
 
' Step 2 (overfør data)
    Set objRec = Server.CreateObject("ADODB.RecordSet")
    sql = "INSERT INTO resultat2" &_
            "SELECT    person, 1, resultat1 FROM resultater" &_
          "UNION" &_
          "SELECT    person, 2, resultat2 FROM resultater" &_
          "UNION" &_
          "SELECT    person, 3, resultat3 FROM resultater" &_
          "UNION" &_
          "SELECT    person, 4, resultat4 FROM resultater"
    objRec.Open sql, objConn, 1,3
    set objRec = nothing
   
'Step 3 (Udvælg data)
      Set objRec = Server.CreateObject("ADODB.RecordSet")
    sql = "SELECT person, MAX(ra.resultat), " &_
          "("  &_
        "SELECT TOP 1 rb.kolonne" &_
        "FROM resultater2 rb" &_
        "WHERE ra.person = rb.person" &_
        "AND rb.resultat = MAX(ra.resultat)" &_
          ")"  &_
        "FROM resultater2 ra" &_
          "GROUP BY person"
   
    objRec.Open sql, objConn, 1,3
IF not objRec.EOF THEN
    Response.Write objRec"PERSON" & "<BR>"
    Response.Write objRec"KOLONNE" & "<BR>"
    Response.Write objRec"RESULTAT" & "<BR>"
End if
    set objRec = nothing
      set objConn = nothing

Tabel "resultater" ser sådan ud
person kolonne1 kolonne2 kolonne3 kolonne4
Hans    5        10        15      20
Birthe  10      15        20      5 
Amanda  15      20        5        10
Ingeborg 20      5        10      15

Tabel "resultat"  ser sådan ud uden værdier
PERSON  KOLONNE  RESULTAT


Resultatet burde blive
Hans
kollone4
20
Birthe
kolonne3
20
Amanda
kolonne2
20
Ingeborg
kolonne1
20

-> webfreelancer.dk

Det er en Access 2002 database, som jeg bruger.

:-) karsten_larsen

Prøv at ændre linien:
  sql = "INSERT INTO resultat2" &_
til:
  sql = "INSERT INTO resultat2 (person, kolonne, resultat) " &_

Husk desuden at have mellemrum i slutning af hver linie i din SQL streng. Ellers vil strengen indeholde ord som f.eks. "...UNIONSELECT..."

Hilsen
webfreelancer.dk

-> webfreelancer.dk

Jeg har rette det du skrev til

    Set objRec = Server.CreateObject("ADODB.RecordSet")
    sql = "INSERT INTO resultat2 (person, kolonne, resultat) " &_
            "SELECT person, 1, resultat1 FROM resultater " &_
          "UNION " &_
          "SELECT person, 2, resultat2 FROM resultater " &_
          "UNION " &_
          "SELECT person, 3, resultat3 FROM resultater " &_
          "UNION " &_
          "SELECT person, 4, resultat4 FROM resultater "
    objRec.Open sql, objConn, 1,3 (LiNIE 26 i fejlmeldelsen)
    set objRec = nothing

og fik følgende resultat

Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

/resultat/resultat.asp, line 26

Er det rigtigt forstået at "SELECT person, 1, resultat1 FROM resultater" skal tage værdierne fra den oprindelig tabel og kopier det over i "resultat2". Jeg har prøvet at ændre det til "SELECT person, kolonne1, kolonne2 FROM resultater", men det virker heller ikke

??:-) karsten_larsen

Hej Karsten

Har selv genskabt fejlen på min Access! Du har ret i at vi tager værdierne fra den oprindelige tabel og smider over i "resultat2".

Jeg har prøvet at finde en smart løsning, men det lykkedes ikke helt. En alternativ løsning er så at lave 4 INSERT INTO sætninger!

sql = "INSERT INTO resultat2 (person, kolonne, resultat) " &_
      "SELECT person, 1, resultat1 FROM resultater "

sql = "INSERT INTO resultat2 (person, kolonne, resultat) " &_
      "SELECT person, 2, resultat2 FROM resultater "

sql = "INSERT INTO resultat2 (person, kolonne, resultat) " &_
      "SELECT person, 3, resultat3 FROM resultater "

sql = "INSERT INTO resultat2 (person, kolonne, resultat) " &_
      "SELECT person, 4, resultat4 FROM resultater "



... og derefter fortsætte med step 3

Hilsen
webfreelancer.dk

-> webfreelancer

Nu fungere step 1 og step 2, men ikke step 3, som jeg ikke kan gennemskue.

Koden ser sådan ud nu:

    strSQL = "SELECT person, MAX(ra.resultat), " &_
              "(" &_
            "SELECT TOP 1 rb.kolonne " &_
            "FROM resultat2 rb " &_
            "WHERE ra.person = rb.person " &_
            "AND rb.resultat = MAX(ra.resultat)" &_
              ")" &_
            "FROM resultat2 ra " &_
            "GROUP BY person"
    Conn.Execute(strSQL)

Og fejl meldingen ser sådan ud:

Microsoft JET Database Engine error '80040e14'

Cannot have aggregate function in WHERE clause (ra.person=rb.person And rb.resultat=MAX(ra.resultat)).

/resultat/resultat.asp, line 87


?:-) Karsten

Det er ikke nemt, når noget der fungerer fint i MS SQL skal køre i MS Access! :o)

Nå, men så laver vi endnu en lille ændring.
Prøv med STEP 3 på følgende måde:

sql = "SELECT ra.person, MAX(ra.resultat), " &_
        "(SELECT TOP 1 rb.kolonne " &_
        "FROM resultat2 rb " &_
        "WHERE ra.person = rb.person " &_
        "AND rb.resultat = " &_
          "(SELECT MAX(rc.resultat)" &_
          "FROM resultat2 rc " &_
          "WHERE rc.person = ra.person) " &_
        ") " &_
      "FROM resultat2 ra " &_
      "GROUP BY ra.person"

Hilsen
webfreelancer.dk

-> webfreelancer

Det fungerer helt uden fejlmeldinger. Det er dog blevet mere kompliceret end jeg kan finde ud af bruge det.

Så 2 spørgsmål:
1 spørgsmål
Det helt elementære - hvor er resultatet? Jeg har prøvet fra rs("person") til forskelllige kombinationer af ra.kolonne, uden at finde et resultat.

2 spørgsmål
Vil du ikke sætte lidt tekst på, hvad der sker i de forskellige linier i step 3 for jeg vil meget gerne lære det. Jeg forstår ikke helt betydningen af ra, rb og rc. Eller hvis du kender et sted, hvor jeg kan læse lidt mere om det - jeg kan nemlig bedst lide, at jeg forstår de koder, som jeg bruger til fulde.

pfh tak
:-) karsten_larsen

Ad 1
-------------------------
Resultat får du i det recordsæt vi laver i step 3.

  rs = Conn.Execute(strSQL)

Du kan referere til de forskellige kolonner således:

  rs(0) = <person>
  rs(1) = <resultat>
  rs(2) = <kolonne>


Ad 2
-------------------------
Der er en par indlejrede SQL-sætninger, så jeg kan godt forstå den er lidt svær at overskue. Det der gør den besværlig er at vi udover person og resultat også skal have nummeret på den kolonne, der indeholder resultatet.

Jeg ved ikke om jeg kan forklare det, men jeg kan da prøve.

Vi selecter først person og max(resultat) fra tabellen (ra). Der er nogenlunde nemt at gå til.

Dernæst skal vi have selectet den tilhørende kolonne. Det gør vi ved at lave en ny select på samme tabel (rb), hvor person er lig med personen fra første select (ra), og resultat er lig med max(resultat) for personen.

Max(resultat) er vi nødt til at finde ved at lave endnu en select på samme tabel (rc), hvor vi finder max(resultat) for personen som vi kender fra tabel (ra).

Jeg ved ikke om det er til at finde hoved at hale i, men jeg ved ikke helt, hvordan jeg skal forklare det. Håber det gav lidt... :o)

Hilsen
webfreelancer.dk

-> webfreelancer

TUSIND TAK DIN HJÆLP - det var en rigtig brugbar beskrivelse - nu ved jeg hvad der sker.

:-)) karsten_larsen