Query to select only one requester

extendedram · May 31st, 2006, 08:37 AM

I have table of requests, and as part of the request columns there are:
RequesterFirstName
RequesterLastName
RequesterUserName

I want to extract these but I cannot come up with the query that returns only one row for a RequesterUserName.

Thanks Extended

jrogers · May 31st, 2006, 09:12 AM

can you give some more detail for example what values you are trying to return

extendedram · May 31st, 2006, 09:24 AM

Values like this are in the source table:
RequesterFirstName RequesterLastName RequesterUserID
Roxanne Alexander hx1i
Roxanne Alexander hx1i

I would like the query to return one row:
Roxanne Alexander hx1i

jrogers · May 31st, 2006, 09:41 AM

you could use a select Distinct, but is there anything unique about each of the records.

extendedram · May 31st, 2006, 09:48 AM

Here is the query I have tried:

Code:

SELECT DISTINCT 
                      UPPER(REQUESTER_F_NAME) AS Expr3, UPPER(REQUESTER_L_NAME) AS Expr4, UPPER(REQUESTER_SFID) AS Expr2, REQUESTER_PHONE, 
                      SUBSTRING(REQUEST_ID, 1, 2) AS office, REQUESTER_DEPT
FROM         dbo.RH902
GROUP BY UPPER(REQUESTER_SFID), SUBSTRING(REQUEST_ID, 1, 2), UPPER(REQUESTER_F_NAME), UPPER(REQUESTER_L_NAME), REQUESTER_PHONE, 
                      REQUESTER_DEPT
ORDER BY UPPER(REQUESTER_SFID), UPPER(REQUESTER_F_NAME), UPPER(REQUESTER_L_NAME), REQUESTER_PHONE, SUBSTRING(REQUEST_ID, 1, 2), 
                      REQUESTER_DEPT

And I get these results:

FirstName LastName UserID Phone office department
SANDI TAYLOR A01C 708-555-2313 01 CLM Autom & Proc
SANDI TAYLOR A01C 815-555-3335 01 CLAIMS
SANDI TAYLOR A01C 815-555-3335 01 CLM Autom & Proc

Thanks

Extended

jrogers · May 31st, 2006, 10:02 AM

which of the three results is the correct on to be displayed, distinct will return unique rows..
and as either the telephone number or department is different is the reason for three lines being
displayed.

extendedram · May 31st, 2006, 10:05 AM

The last ones are the most current information. But I would take any row as long as I just get one.

jrogers · May 31st, 2006, 10:28 AM

If all you care abut is the first three fields, then you can just have those fields in the select ie

Code:

SELECT DISTINCT 
        UPPER(REQUESTER_F_NAME) AS Expr3, 
        UPPER(REQUESTER_L_NAME) AS Expr4, 
        UPPER(REQUESTER_SFID) AS Expr2
FROM         
        dbo.RH902
GROUP BY 
        UPPER(REQUESTER_SFID), 
        UPPER(REQUESTER_F_NAME), 
        UPPER(REQUESTER_L_NAME)
ORDER BY 
        UPPER(REQUESTER_SFID), 
        UPPER(REQUESTER_F_NAME), 
        UPPER(REQUESTER_L_NAME)

am I missing something

extendedram · May 31st, 2006, 10:47 AM

Thanks for all your help so far on this.

Maybe if I explain my end goal it will make more sense. The rh902 table needs to be normalized. I want to remove requester information out of it and in to a new table where the requester_sfid only occurs once.

Doing DISTINCT doesnt work because none of the fields in this table can be trusted to be consistent.

The thing that really sucks, is I did this before but cannot remember how i did it.

It was something like:

select *

from (select top 1 ...)

thanks for your patience.

jrogers · May 31st, 2006, 12:44 PM

am I correct in saying that in the new table you also want the fields phone, office & department