Fejl: Værdi gentages ved select

Jeg er ikke helt sikker på, at jeg har forstået det rigtigt. Men hvis jeg har tolket dit spørgsmål rigtigt burde det vel skulle være noget lignende dette:

SELECT
FORUM_MEMBERS.M_FIRSTNAME, MAX(FORUM_REPLY.R_DATE)
FROM FORUM_REPLY INNER JOIN
FORUM_MEMBERS ON FORUM_REPLY.R_AUTHOR = FORUM_MEMBERS.MEMBER_ID INNER JOIN
FORUM_MODERATOR ON FORUM_REPLY.FORUM_ID = FORUM_MODERATOR.FORUM_ID AND
FORUM_MEMBERS.MEMBER_ID = FORUM_MODERATOR.MEMBER_ID
GROUP BY FORUM_MEMBERS.M_FIRSTNAME
ORDER BY MAX(FORUM_REPLY.R_DATE) DESC

Yes, yes, yes :-) Takker, tryk svar :-)

Det var bare max-funktionen jeg skulle bruge :-)

Det er vist blevet lidt for kompliceret for mig, kan du ikke også hjælpe mig med at indstætte det i mit eksisterende script (som virker)?

Scriptet er:

SELECT TOP 100 PERCENT FORUM_FORUM.F_SUBJECT, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME,
  FORUM_MEMBERS.M_PHOTO_URL, FORUM_FORUM.FORUM_ID, FORUM_MEMBERS.M_HOMEPAGE,
  findDomain(FORUM_MEMBERS.M_HOMEPAGE) AS M_HOMEPAGENew
FROM FORUM_FORUM INNER JOIN
  FORUM_MODERATOR ON FORUM_FORUM.FORUM_ID = FORUM_MODERATOR.FORUM_ID INNER JOIN
  FORUM_CATEGORY ON FORUM_FORUM.CAT_ID = FORUM_CATEGORY.CAT_ID INNER JOIN
  FORUM_GROUPS ON FORUM_CATEGORY.CAT_ID = FORUM_GROUPS.GROUP_CATID INNER JOIN
  FORUM_MEMBERS ON FORUM_MODERATOR.MEMBER_ID = FORUM_MEMBERS.MEMBER_ID
WHERE (FORUM_GROUPS.GROUP_ID = 6) AND (FORUM_CATEGORY.CAT_ID <> 41) AND (FORUM_FORUM.F_STATUS = 1) AND
  (FORUM_MEMBERS.M_PHOTO_URL <> '')
ORDER BY ***R_DATE <-indsættes her ****

Det skal indsættes, så det er det, som den søger på den fundne R_DATE. Og det som skal indsættes er ovenståeden script (næsten):

SELECT
MAX(FORUM_REPLY.R_DATE)
FROM FORUM_REPLY INNER JOIN
FORUM_MEMBERS ON FORUM_REPLY.R_AUTHOR = FORUM_MEMBERS.MEMBER_ID INNER JOIN
FORUM_MODERATOR ON FORUM_REPLY.FORUM_ID = FORUM_MODERATOR.FORUM_ID AND
FORUM_MEMBERS.MEMBER_ID = FORUM_MODERATOR.MEMBER_ID
GROUP BY FORUM_MEMBERS.M_FIRSTNAME
ORDER BY MAX(FORUM_REPLY.R_DATE) DESC

Igen er jeg ikke helt sikker på, om jeg forstår det rigtigt, men her er et hurtigt skud fra bøssen:

SELECT TOP 100 PERCENT FORUM_FORUM.F_SUBJECT, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME,
  FORUM_MEMBERS.M_PHOTO_URL, FORUM_FORUM.FORUM_ID, FORUM_MEMBERS.M_HOMEPAGE,
  findDomain(FORUM_MEMBERS.M_HOMEPAGE) AS M_HOMEPAGENew
  MAX(FORUM_REPLY.R_DATE) AS LASTREPLY
FROM FORUM_FORUM INNER JOIN
  FORUM_MODERATOR ON FORUM_FORUM.FORUM_ID = FORUM_MODERATOR.FORUM_ID INNER JOIN
  FORUM_CATEGORY ON FORUM_FORUM.CAT_ID = FORUM_CATEGORY.CAT_ID INNER JOIN
  FORUM_GROUPS ON FORUM_CATEGORY.CAT_ID = FORUM_GROUPS.GROUP_CATID INNER JOIN
  FORUM_MEMBERS ON FORUM_MODERATOR.MEMBER_ID = FORUM_MEMBERS.MEMBER_ID
WHERE (FORUM_GROUPS.GROUP_ID = 6) AND (FORUM_CATEGORY.CAT_ID <> 41) AND (FORUM_FORUM.F_STATUS = 1) AND
  (FORUM_MEMBERS.M_PHOTO_URL <> '')
GROUP BY
PERCENT FORUM_FORUM.F_SUBJECT, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME,
  FORUM_MEMBERS.M_PHOTO_URL, FORUM_FORUM.FORUM_ID, FORUM_MEMBERS.M_HOMEPAGE,
  findDomain(FORUM_MEMBERS.M_HOMEPAGE)
ORDER BY MAX(FORUM_REPLY.R_DATE) DESC

Jeg skal blot have den til at liste efter den moderator, som har postet det seneste indlæg (reply).

Men den siger: "The column prefix 'FORUM_REPLY' does not match with a tabel name or alias name used in the query." Men FORUM_REPLY eksistere alt, så der må være noget andet galt.

SELECT TOP 100 PERCENT FORUM_FORUM.F_SUBJECT, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME,
  FORUM_MEMBERS.M_PHOTO_URL, FORUM_FORUM.FORUM_ID, FORUM_MEMBERS.M_HOMEPAGE,
  findDomain(FORUM_MEMBERS.M_HOMEPAGE) AS M_HOMEPAGENew, MAX(FORUM_REPLY.R_DATE)
  AS LASTREPLY
FROM FORUM_FORUM INNER JOIN
  FORUM_MODERATOR ON FORUM_FORUM.FORUM_ID = FORUM_MODERATOR.FORUM_ID INNER JOIN
  FORUM_CATEGORY ON FORUM_FORUM.CAT_ID = FORUM_CATEGORY.CAT_ID INNER JOIN
  FORUM_GROUPS ON FORUM_CATEGORY.CAT_ID = FORUM_GROUPS.GROUP_CATID INNER JOIN
  FORUM_MEMBERS ON FORUM_MODERATOR.MEMBER_ID = FORUM_MEMBERS.MEMBER_ID
WHERE (FORUM_GROUPS.GROUP_ID = 6) AND (FORUM_CATEGORY.CAT_ID <> 41) AND (FORUM_FORUM.F_STATUS = 1) AND
  (FORUM_MEMBERS.M_PHOTO_URL <> '')
GROUP BY FORUM_FORUM.F_SUBJECT, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME,
  FORUM_MEMBERS.M_PHOTO_URL, FORUM_FORUM.FORUM_ID, FORUM_MEMBERS.M_HOMEPAGE,
  findDomain(FORUM_MEMBERS.M_HOMEPAGE)
ORDER BY MAX(FORUM_REPLY.R_DATE) DESC

eksistere alt = eksisterer altså

Okay, det er nok fordi jeg tildelte den aliaset LASTREPLY i SELECT.

Prøv at ændre ORDER BY til

ORDER BY LASTREPLY DESC

Rettet, men samme fejl igen.

Okay, så prøver vi med

SELECT TOP 100 PERCENT FORUM_FORUM.F_SUBJECT, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME,
  FORUM_MEMBERS.M_PHOTO_URL, FORUM_FORUM.FORUM_ID, FORUM_MEMBERS.M_HOMEPAGE,
  findDomain(FORUM_MEMBERS.M_HOMEPAGE) AS M_HOMEPAGENew, MAX(FORUM_REPLY.R_DATE)
  AS LASTREPLY
FROM FORUM_FORUM INNER JOIN
  FORUM_MODERATOR ON FORUM_FORUM.FORUM_ID = FORUM_MODERATOR.FORUM_ID INNER JOIN
  FORUM_CATEGORY ON FORUM_FORUM.CAT_ID = FORUM_CATEGORY.CAT_ID INNER JOIN
  FORUM_GROUPS ON FORUM_CATEGORY.CAT_ID = FORUM_GROUPS.GROUP_CATID INNER JOIN
  FORUM_MEMBERS ON FORUM_MODERATOR.MEMBER_ID = FORUM_MEMBERS.MEMBER_ID INNER JOIN
  FORUM_REPLY ON FORUM_REPLY.R_AUTHOR = FORUM_MEMBERS.MEMBER_ID
WHERE (FORUM_GROUPS.GROUP_ID = 6) AND (FORUM_CATEGORY.CAT_ID <> 41) AND (FORUM_FORUM.F_STATUS = 1) AND
  (FORUM_MEMBERS.M_PHOTO_URL <> '')
GROUP BY FORUM_FORUM.F_SUBJECT, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME,
  FORUM_MEMBERS.M_PHOTO_URL, FORUM_FORUM.FORUM_ID, FORUM_MEMBERS.M_HOMEPAGE,
  findDomain(FORUM_MEMBERS.M_HOMEPAGE)
ORDER BY LASTREPLY DESC

Prøver :-)

Virker - takker mange mange mange gange :-D

Hav en go dag :-)