Solved

I need help with a query, probably involving the MIN function

Posted on 2015-02-21
6
75 Views
Last Modified: 2015-02-22
I need help figuring out a sql query. I have tried using the MIN statement to get what I need, but still can't figure this out.

Suppose I have many clients with the same First,  and Last Name,
and possibly the same middle initial. The only thing really distinguishing
these records, is that each Code value is different for each record.

So how would I return only one record for people with similar first, middle and last names?
Plus I want the record which has the shortest length Code within a group of similar sounding names.
So notice that one of the duplicate named people will have a code which is the shortest in length. That is
the one I want. (Also, I want the resulting query to return the resulting query data into a temp table)

So for example for this table data :

First       MI Last    Code
Drake    B  Hansen   12345
Drake         Hansen   12345A
Drake    T   Hansen   12345B
Berry     K   Mojo        98988
Mary     B   Jones        55687
Mary           Jones        55687A
Mary      H  Jones        55687B

I want my query to return the following  :

Drake  B Hansen   12345
Berry  K Mojo        98988
Mary   B   Jones     55687

Attached is a scripted table with data, if that helps
SampleTable.txt
0
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Expert Comment

by:BullfrogSoftware
ID: 40623782
Is the 'Code' always going to be a numeric, then the same numeric with alphabetic characters at the end?  If so, you could just check that the code 'IsNumeric' and return all of the rows that are.
0
 
LVL 78

Expert Comment

by:arnold
ID: 40623797
Select first,mi,last,min(code) from tablename group by (first + last) [,code], MIA

The [] optional if without it you get an error.
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40623835
Arnold, I can't get that to work :

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ',Code'.

Select FirstName,MI,LastName,min(CODE) from Clients group by (FirstName + LastName) [,Code]
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 78

Expert Comment

by:arnold
ID: 40623838
You need to remove the [] surrounding the code
And will likely need to add MI at the end

Select first,mi,last,min(code) from tablename group by (first + last)[,code, MIA

The [] optional if without it you get an error.
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 40624181
SELECT T1.First, T1.MI, T1.LAST, T1.Code
FROM yourTable T1
INNER JOIN
(
SELECT First, LAST, Min(LEN(Code)) as MinLen
FROM yourTable
GROUP BY First, Last
) as T2
ON T1.First = T2.First AND T1.Last = T2.Last AND Len(T1.Code) = T2.MinLen

The subquery identifies the combinations of First, and Last, and determines the shortest length of the Code field for that combination.  You then join that subquery to your table and join on the First, Last, and Len(Code) values to identify the particular row.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40625029
Using ROW_NUMBER() is efficient and convenient for this style of need.

| FIRSTNAME | MI | LASTNAME |       CODE |
|-----------|----|----------|------------|
|      Brad |  T |   Grassy | 45455      |
|     Drake |  B |   Hansen | 12345      |
|      Mary |  B |    Jones | 45455      |
|     Sarah |  P |   Gisley | 8889       |

Open in new window


produced by:
select
      [FirstName], [MI], [LastName], [Code]
      from (
        select *
        , row_number() over(partition by [FirstName], [LastName]
                            order by code ASC) as rn
        from [dbo].[Clients]
     ) c
where c.rn = 1
;

Open in new window

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question