Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

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

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
brgdotnet
Asked:
brgdotnet
2 Solutions
 
BullfrogSoftwareCommented:
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
 
arnoldCommented:
Select first,mi,last,min(code) from tablename group by (first + last) [,code], MIA

The [] optional if without it you get an error.
0
 
brgdotnetcontractorAuthor Commented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
arnoldCommented:
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
 
Dale FyeCommented:
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
 
PaulCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now