Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

SQL Query Problem

Hi there,

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.

Query:
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
0
quanmac
Asked:
quanmac
  • 2
1 Solution
 
LowfatspreadCommented:
you do want group by
you don't want distinct
you need to select max dates...
and for performance avoid using NOT...

SELECT  TOP 100      WebUser.Wuser_Position,max(Event.Evnt_Start_Date) as 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       (select *
                                     ,case when UPPER(Wuser_first_name) like '%TEST%' then 1 else 0 end as FN
         ,case when UPPER(Wuser_last_name) like '%TEST%' then 1 else 0 end as LN
                              from   WebUser
                             where Wuser_Position IS NOT NULL
                                 AND Wuser_Enabled = 1

 ON InvoiceItems.Wuser_ID = WebUser.Wuser_ID
WHERE     FN = 0 and LN=0
group by webuser.wuser_position        
ORDER BY   Event.Evnt_Start_Date DESC
 
0
 
Christopher KileCommented:
Is this SQL Server?

Don't use a single query.  Use a stored procedure.

CREATE PROCEDURE get100Positions
AS
CREATE TABLE #tempPos (
position <whatever type Wuser_position is> NOT NULL PRIMARY KEY
)

DECLARE p1 READ-ONLY CURSOR FOR
SELECT
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

OPEN p1

DECLARE @position <type of Wuser_Position>
DECLARE @startDate DATETIME

FETCH NEXT FROM p1 INTO @postion, @startDate
WHILE @@FETCH_STATUS = 0 AND (SELECT COUNT COUNT(*) FROM #tempPOS) < 100)
BEGIN
    IF (SELECT COUNT(*) FROM #tempPOS WHERE position = @position) = 0
    BEGIN
        INSERT INTO #tempPos (position) VALUES (@position)
    END
END

CLOSE p1
DEALLOCATE p1

RETURN
GO
0
 
Christopher KileCommented:
Whoops!

Add before RETURN:

SELECT * FROM #tempPos
0
 
quanmacAuthor Commented:
Thanks Lowfatspread, it worked like a charm
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now