Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 31st, 2006, 08:37 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query to select only one requester

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

  #2 (permalink)  
Old May 31st, 2006, 09:12 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old May 31st, 2006, 09:24 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #4 (permalink)  
Old May 31st, 2006, 09:41 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #5 (permalink)  
Old May 31st, 2006, 09:48 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #6 (permalink)  
Old May 31st, 2006, 10:02 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #7 (permalink)  
Old May 31st, 2006, 10:05 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #8 (permalink)  
Old May 31st, 2006, 10:28 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #9 (permalink)  
Old May 31st, 2006, 10:47 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #10 (permalink)  
Old May 31st, 2006, 12:44 PM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Query gregalb SQL Server 2000 2 November 15th, 2007 10:56 PM
SELECT from SELECT query? seananderson Access 1 October 12th, 2007 12:40 AM
Need help on select query arul1984 SQL Server 2000 2 July 4th, 2007 01:49 AM
Select query help minhtri SQL Server 2000 4 March 28th, 2005 06:59 PM
select query collie SQL Server 2000 2 January 17th, 2005 03:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.