Solved

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

Posted on 2015-02-21
6
73 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

840 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