Solved

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

Posted on 2015-02-21
6
74 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 47

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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

749 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