Solved

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

Posted on 2015-02-21
6
72 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
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 77

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 77

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 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