mike99c
asked on
SQL Query returns duplicate results
I have the following query which is returning duplicate results despite the fact I use DISTINCT
SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username,
NewID()
FROM
PractitionerToPractitioner TypeMap
JOIN Core_Accounts ON PractitionerToPractitioner TypeMap.Ac countID = Core_Accounts.ID
JOIN PractitionerTypeToTabTypeM ap ON PractitionerTypeToTabTypeM ap.Practit ionerTypeI D = PractitionerToPractitioner TypeMap.Pr actitioner TypeID
WHERE
PractitionerTypeToTabTypeM ap.TabType ID = 3
ORDER BY
NewID()
Any help would be appreciated. If it is not obvious I can provide more details if needed.
SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username,
NewID()
FROM
PractitionerToPractitioner
JOIN Core_Accounts ON PractitionerToPractitioner
JOIN PractitionerTypeToTabTypeM
WHERE
PractitionerTypeToTabTypeM
ORDER BY
NewID()
Any help would be appreciated. If it is not obvious I can provide more details if needed.
newId() will be unique , that's why it is returning duplicates
SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username
FROM
PractitionerToPractitioner TypeMap
JOIN Core_Accounts ON PractitionerToPractitioner TypeMap.Ac countID = Core_Accounts.ID
JOIN PractitionerTypeToTabTypeM ap ON PractitionerTypeToTabTypeM ap.Practit ionerTypeI D = PractitionerToPractitioner TypeMap.Pr actitioner TypeID
WHERE
PractitionerTypeToTabTypeM ap.TabType ID = 3
ORDER BY
NewID()
SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username
FROM
PractitionerToPractitioner
JOIN Core_Accounts ON PractitionerToPractitioner
JOIN PractitionerTypeToTabTypeM
WHERE
PractitionerTypeToTabTypeM
ORDER BY
NewID()
ASKER
Ok but the problem is I get the following error now:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
What I want is to return up to 8 rows randomly ordered but for each to be different.
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
What I want is to return up to 8 rows randomly ordered but for each to be different.
SELECT ID, UserName from
FROM ( SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username
FROM
PractitionerToPractitioner TypeMap
JOIN Core_Accounts ON PractitionerToPractitioner TypeMap.Ac countID = Core_Accounts.ID
JOIN PractitionerTypeToTabTypeM ap ON PractitionerTypeToTabTypeM ap.Practit ionerTypeI D = PractitionerToPractitioner TypeMap.Pr actitioner TypeID
WHERE
PractitionerTypeToTabTypeM ap.TabType ID = 3
) A
Order by NewID()
FROM ( SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username
FROM
PractitionerToPractitioner
JOIN Core_Accounts ON PractitionerToPractitioner
JOIN PractitionerTypeToTabTypeM
WHERE
PractitionerTypeToTabTypeM
) A
Order by NewID()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks SQL SERVER DBA, your solution was the closest. I had to make the following sloght adjustment to add the other field in the group by and also add the order by to make the results random:
SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username,
NewID()
FROM
PractitionerToPractitioner TypeMap
JOIN Core_Accounts ON PractitionerToPractitioner TypeMap.Ac countID = Core_Accounts.ID
JOIN PractitionerTypeToTabTypeM ap ON PractitionerTypeToTabTypeM ap.Practit ionerTypeI D = PractitionerToPractitioner TypeMap.Pr actitioner TypeID
WHERE
PractitionerTypeToTabTypeM ap.TabType ID = 3
GROUP BY
Core_Accounts.ID,Core_Acco unts.Usern ame
ORDER BY
NewID()
SELECT DISTINCT TOP 8
Core_Accounts.ID,
Core_Accounts.Username,
NewID()
FROM
PractitionerToPractitioner
JOIN Core_Accounts ON PractitionerToPractitioner
JOIN PractitionerTypeToTabTypeM
WHERE
PractitionerTypeToTabTypeM
GROUP BY
Core_Accounts.ID,Core_Acco
ORDER BY
NewID()
Core_Accounts.ID,
Core_Accounts.Username,
NewID()
FROM
PractitionerToPractitioner
JOIN Core_Accounts ON PractitionerToPractitioner
JOIN PractitionerTypeToTabTypeM
WHERE
PractitionerTypeToTabTypeM
group by Core_Accounts.ID
ORDER BY
NewID()