SQL Query Problem
Posted on 2007-03-30
I am trying to retrieve the last 100 distinct job positions that have attended an event. Right now the query returns the last 100 job positions but they are not distinct, because I have an order by event start date in my query clause. Is there anyway else I can retrieve this information.
I've posted the query below. I've tried a group by and that didn't work either because of the event start date.
SELECT DISTINCT TOP 100 WebUser.Wuser_Position, Event.Evnt_Start_Date
FROM Event INNER JOIN
Invoice ON Event.Evnt_ID = Invoice.Evnt_ID INNER JOIN
InvoiceItems ON Invoice.Inv_ID = InvoiceItems.Inv_ID INNER JOIN
WebUser ON InvoiceItems.Wuser_ID = WebUser.Wuser_ID
WHERE (WebUser.Wuser_Position IS NOT NULL) AND (WebUser.Wuser_Enabled = 1) AND
(NOT (UPPER(WebUser.Wuser_First_Name) LIKE '%TEST%')) AND
(NOT (UPPER(WebUser.Wuser_Last_Name) LIKE '%TEST%'))
ORDER BY Event.Evnt_Start_Date DESC