Getting "Distinct" of first field only 2

[bburnell]

Hi all,

I am using SQL Server 2005 and have the following T-SQL query:

SELECT DISTINCT tiMNMmbrno, ttMNMnincr, tvMNMntype, tvMNMrelat, tvMMBclass, tsMMBmyear, tvMMBmpref, 
tcMNMfirst, tcMNMmidle, tcMNMlastn, tvMNMgnrtn, tcMADline1, tcMADline2, tcMADline3, tcMADcityn, 
tvMADstate, tcMADzipc5, tcMADzipc4, tsMCOmcnty, tcMCOcname, ttMCOfbreg, ttMCOfbdst, tcMTOtname,
ttMTOrtwnp, PhoneType, Faxnum, tcMEMemail

FROM vw_MMB_Membership

WHERE tsMMBmyear=2008 AND (tvMMBclass='01' OR tvMMBclass='21' OR
	tvMMBclass='31' OR tvMMBclass='51')   AND tvMNMrelat<>'30' AND 
	tsMCMcyear=2008 and 
	((tvMCMcomno ='0051' AND tvMCMposno='0002') OR 
	(tvMCMcomno='0051' AND tvMCMposno='0003') OR 
	(tvMCMcomno='0051' AND tvMCMposno='0005') OR 
	(tvMCMcomno='0051' AND tvMCMposno='0010') OR 
	(tvMCMcomno='0053' AND tvMCMposno='0005') OR 
	(tvMCMcomno='0054' AND tvMCMposno='0005') OR 
	(tvMCMcomno='0055' AND tvMCMposno='0005') OR 
	(tvMCMcomno='0060' AND tvMCMposno='0005') OR 
	(tvMCMcomno='0086' AND tvMCMposno='0005') OR 
	tvMCMcomno='0047' OR 
	tvMCMcomno='0048' OR 
	tvMCMcomno='0049' OR
	tvMCMcomno='0059')

tiMNMmbrno looks distinct until about 200 rows down, then dupes of that member # show up with 2 different <ttMNMnincr> and <tvMNMrelat>. So, the record itself is not distinct, but I only want distinct member #'s to show up. Any ideas?

Thanks!
Brett

 

[bborissov]

And what result you want in other fields?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bburnell]

If there is more than one record that matches the where clause, I want the first record/value of each field. Is that answer your question?
~Brett

 

[genomon]

This is really cumbersome, but might work if added to the where clause (as a subquery):

AND tiMNMmbrno IN(SELECT DISTINCT tiMNMmbrno FROM vw_MMB_Membership)

Seems like this is a great case for a self join, but I rarely use them. Try looking into Access help or Google. There has to be a more elegant and efficient way than mine...

All I ask is a chance to prove that money can't make me happy.

 

[bborissov]

How you define First?
Which fields define "First" record?
Keep in mind that in SQL Server there is no such thing like record number.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bburnell]

if there are 2 records (for example):

15143, 01, ...
15143, 02, ...

I want "15143, 01" (or the TOP 1 record)...

~Brett

 

[bborissov]

OK, I assume that 01 and 02 are values stored in ttMNMnincr field:

SELECT vw_MMB_Membership.tiMNMmbrno,
       vw_MMB_Membership.ttMNMnincr,
       tvMNMntype,
       tvMNMrelat,
       tvMMBclass,
       tsMMBmyear,
       tvMMBmpref,
       tcMNMfirst,
       tcMNMmidle,
       tcMNMlastn,
       tvMNMgnrtn,
       tcMADline1,
       tcMADline2,
       tcMADline3,
       tcMADcityn,
       tvMADstate,
       tcMADzipc5,
       tcMADzipc4,
       tsMCOmcnty,
       tcMCOcname,
       ttMCOfbreg,
       ttMCOfbdst,
       tcMTOtname,
       ttMTOrtwnp,
       PhoneType,
       Faxnum,
       tcMEMemail
FROM vw_MMB_Membership
INNER JOIN (SELECT tiMNMmbrno,
                   MIN(ttMNMnincr) AS ttMNMnincr
            FROM vw_MMB_Membership
            GROUP BY tiMNMmbrno) TblMin
      ON vw_MMB_Membership.tiMNMmbrno = TblMin.tiMNMmbrno
         vw_MMB_Membership.ttMNMnincr = TblMin.ttMNMnincr
WHERE tsMMBmyear=2008 AND
   (tvMMBclass='01' OR tvMMBclass='21' OR
    tvMMBclass='31' OR tvMMBclass='51')   AND tvMNMrelat<>'30' AND
    tsMCMcyear=2008 and
    ((tvMCMcomno ='0051' AND tvMCMposno='0002') OR
    (tvMCMcomno='0051' AND tvMCMposno='0003') OR
    (tvMCMcomno='0051' AND tvMCMposno='0005') OR
    (tvMCMcomno='0051' AND tvMCMposno='0010') OR
    (tvMCMcomno='0053' AND tvMCMposno='0005') OR
    (tvMCMcomno='0054' AND tvMCMposno='0005') OR
    (tvMCMcomno='0055' AND tvMCMposno='0005') OR
    (tvMCMcomno='0060' AND tvMCMposno='0005') OR
    (tvMCMcomno='0086' AND tvMCMposno='0005') OR
    tvMCMcomno='0047' OR
    tvMCMcomno='0048' OR
    tvMCMcomno='0049' OR
    tvMCMcomno='0059')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bburnell]

I assume that 01 and 02 are values stored in ttMNMnincr field

- Correct.

Now I get the following when I try to execute it:

Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'vw_MMB_Membership'.

which is this line:

vw_MMB_Membership.ttMNMnincr = TblMin.ttMNMnincr

Ideas?
~Brett

 

[ca8msm]

ON vw_MMB_Membership.tiMNMmbrno = TblMin.tiMNMmbrno
vw_MMB_Membership.ttMNMnincr = TblMin.ttMNMnincr

You are either missing an AND, or you should be only joining on one of those fields.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[bborissov]

I forgot an [AND] Change this lines:

    ON vw_MMB_Membership.tiMNMmbrno = TblMin.tiMNMmbrno
       vw_MMB_Membership.ttMNMnincr = TblMin.ttMNMnincr

to be:

    ON vw_MMB_Membership.tiMNMmbrno = TblMin.tiMNMmbrno AND
       vw_MMB_Membership.ttMNMnincr = TblMin.ttMNMnincr

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bburnell]

I figured out the "AND" just before you posted. Thanks. When I ran this, I now have 400+ extra records and the example record (15143) now has 3 of the same record...

15143, 01, ..., Charles
15143, 01, ..., Charles
15143, 01, ..., Charles

The dupes look like this:

15143, 01, ..., Betty
15143, 02, ..., Charles

Any ideas? I think you are on the right path, because I was trying something similar.

Thanks for your help!
~Brett

 

[bborissov]

You just have multiple records with
[tt]
tiMNMmbrno, ttMNMnincr
-----------------------
15143 01
[/tt]

I don't know your data, I'm just guessing here.
Could you post some example data and what you want from it?
Did you have any Primary key on that table?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bburnell]

15143,1,IND,01,01,2008,M,Charles,D,LastName,
BLNK,,,Address1,Somewhere,PA,19000,9333,44,COUNTY,1,
3,TOWN,3,NULL,NULL,email@email.com

15143,2,IND,02,01,2008,M,Lois,A,LastName,
BLNK,,,Address1,Somewhere,PA,19000,9333,44,COUNTY,1,
3,TOWN,3,NULL,NULL,email@email.com

It's a view. There's no primary key on a view, is there?

Currently, my SQL is getting possibly 2 records (above) that match my criteria. But, I only want the first record returned if there's more than 1.

Notice that the 2nd (1,2) and 4th (01,02) fields are different. Also, the first and middle names are different as well. Address, city, zip, email, etc. can all be different as well.

This isn't even my SQL, it's someone else's, but I've been told to fix it and I'm pulling my hair out!

~Brett

 

[r937]

change this --

SELECT DISTINCT tiMNMmbrno, 
ttMNMnincr, tvMNMntype, 
... [i]bunch of other fields[/i]
FROM vw_MMB_Membership
WHERE [i]bunch of conditions[/i]

to this --

SELECT tiMNMmbrno, 
MAX(ttMNMnincr), MAX(tvMNMntype), 
... [i]bunch of MAXes on all other fields[/i]
FROM vw_MMB_Membership
WHERE [i]bunch of conditions[/i]
GROUP BY tiMNMmbrno

this will not give the "first" of multiple rows for each member, but it will retrieve the most data, which is often advantageous if one address has a zip code and the other one doesn't, for example

r937.com | rudy.ca

 

[bborissov]

Brett,
There is NO way to get:

15143, 1
AND
15143, 2

when you self join (INNER) this view/table by MIN() of the second field.

DECLARE @Test TABLE (Fld1 int, Fld2 int)
INSERT INTO @Test VALUES( 15143, 1)
INSERT INTO @Test VALUES( 15143, 2)
INSERT INTO @Test VALUES( 15143, 3)
INSERT INTO @Test VALUES( 15143, 4)
INSERT INTO @Test VALUES( 15143, 5)
INSERT INTO @Test VALUES( 15143, 6)
INSERT INTO @Test VALUES( 15143, 7)

SELECT Test.* 
       from @Test Test
INNER JOIN (SELECT Fld1, MIN(Fld2) AS Fld2
                   FROM @Test
                   GROUP BY Fld1) Tst
ON Test.Fld1 = Tst.Fld1 AND Test.Fld2 = Tst.Fld2

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bburnell]

I think that was it. All I had to add to it was also putting the "WHERE" clause in the self/inner join as well, but it seems to be what I wanted. Thank you so much!!
~Brett

 

[bburnell]

Ok. Back again. I have the self/inner join and it works EXCEPT for one record. In that record, everything is exactly the same except it has 2 different email addresses.

I tried adding the email address to the inner join with no success. What I want is to return only one record with just the first email address if possible.

The records look something like this:

Record 1:

15143,1,IND,01,01,2008,M,Charles,D,LastName,
BLNK,,,Address1,Somewhere,PA,19000,9333,44,COUNTY,1,
3,TOWN,3,NULL,NULL,email@email.com

Record 2:

15143,1,IND,01,01,2008,M,Charles,D,LastName,
BLNK,,,Address1,Somewhere,PA,19000,9333,44,COUNTY,1,
3,TOWN,3,NULL,NULL,NewEmail@NewEmail.com

See how they are duplicates EXCEPT for the fact they have different email addresses? Also, the email address could be NULL as well.

Any help greatly appreciated!
~Brett

 

[jdaman]

You could use the following solution:

SELECT  tiMNMmbrno,
        ttMNMnincr,
        tvMNMntype,
        tvMNMrelat,
        tvMMBclass,
        tsMMBmyear,
        tvMMBmpref,
        tcMNMfirst,
        tcMNMmidle,
        tcMNMlastn,
        tvMNMgnrtn,
        tcMADline1,
        tcMADline2,
        tcMADline3,
        tcMADcityn,
        tvMADstate,
        tcMADzipc5,
        tcMADzipc4,
        tsMCOmcnty,
        tcMCOcname,
        ttMCOfbreg,
        ttMCOfbdst,
        tcMTOtname,
        ttMTOrtwnp,
        PhoneType,
        Faxnum,
        tcMEMemail
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY tiMNMmbrno ORDER BY ttMNMnincr, tvMNMntype, tvMNMrelat, tvMMBclass, tsMMBmyear, tvMMBmpref, tcMNMfirst, tcMNMmidle, tcMNMlastn, tvMNMgnrtn, tcMADline1, tcMADline2, tcMADline3, tcMADcityn, tvMADstate, tcMADzipc5, tcMADzipc4, tsMCOmcnty, tcMCOcname, ttMCOfbreg, ttMCOfbdst, tcMTOtname, ttMTOrtwnp, PhoneType, Faxnum, tcMEMemail ) AS RowNumber,
                    tiMNMmbrno,
                    ttMNMnincr,
                    tvMNMntype,
                    tvMNMrelat,
                    tvMMBclass,
                    tsMMBmyear,
                    tvMMBmpref,
                    tcMNMfirst,
                    tcMNMmidle,
                    tcMNMlastn,
                    tvMNMgnrtn,
                    tcMADline1,
                    tcMADline2,
                    tcMADline3,
                    tcMADcityn,
                    tvMADstate,
                    tcMADzipc5,
                    tcMADzipc4,
                    tsMCOmcnty,
                    tcMCOcname,
                    ttMCOfbreg,
                    ttMCOfbdst,
                    tcMTOtname,
                    ttMTOrtwnp,
                    PhoneType,
                    Faxnum,
                    tcMEMemail
          FROM      vw_MMB_Membership
        ) a
WHERE   RowNumber = 1

However without anything to tell which record came first in the table there is no guarantee that it will always return the first record for each account. In this case it will return the record with the email address that comes first alphabetically.

 

[bburnell]

For now that gets me the 822 records I want without the duplicate record. I'll be back if it's not what they want, but I think we are ok. Thanks again! A "*" for you!

~Brett