Multiple records for one ID, show only first one 1

[tsp1lrk72]

Hi- I'm writing a query to show data from a table; the data has multiple records for the same ID:

Vendor  Comment
1       Inovis as being a common practice that we both use.    
1	Tom keeps getting VM. He will try again. CH
1	Tom spoke to contact and will help him get in touch

Here is my query:

Select 
VI.Rank,
VI.DivisionName,
VI.VendorName,
VI.SKU,
VI.EDI,
C.Comment
FROM tblVendorInfo As VI
JOIN ENAPTEST.dbo.tblVendorComments As C ON VI.VendorID = C.VendorID
Where VI.VendorID = '1' AND DivisionName = 'BP'

I only want to show the first comment-- how can I do that? This obviously shows all three comments for VendorID 1- I only want to show the first one...

Thanks.

 

[SQLSister]

What identifies it as the first comment? Data is not stored in a particular order in a table, the first one you see ina selct isn't necessarily the first one inserted.

"NOTHING is more important in a database than integrity." ESquared

 

[r937]

add

AND LEN(C.Comment) =
(SELECT MIN(LEN(Comment)) FROM ENAPTEST.dbo.tblVendorComments
WHERE VendorID = VI.VendorID )

r937.com | rudy.ca

 

[tsp1lrk72]

Ahhh, okay-I am using a CommentTimestamp- can incorporate that to show me the latest comment based on the timestamp?

 

[r937]

sure, just use it the way i've used the comment itself (except of course you wouldn't take the LEN of it)

and p.s., you should mention that kinda stuff in your first post

r937.com | rudy.ca

 

[bborissov]

Could you post the real structure of the table and explain each field?


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

 

[tsp1lrk72]

Thanks r937-- I just realized I had the CommentTimeStamp in there- sorry... I will try it.

 

[tsp1lrk72]

okay I have this, but it's trying to return all 3 again-

Select
VI.Rank,
VI.DivisionName,
VI.VendorName,
VI.SKU,
VI.EDI,
C.Comment,
C.CommentTimestamp
FROM tblVendorInfo As VI
JOIN ENAPTEST.dbo.tblVendorComments As C ON VI.VendorID = C.VendorID
Where VI.VendorID = '1' AND DivisionName = 'BP'
AND C.Comment =
(SELECT C.Comment FROM ENAPTEST.dbo.tblVendorComments
WHERE VendorID = VI.VendorID 
 ) 
order by CommentTimestamp desc

Where am I wrong?

Thanks...I appreciate the help

 

[r937]

  FROM tblVendorInfo As VI
INNER
  JOIN ENAPTEST.dbo.tblVendorComments As C 
    ON C.VendorID = VI.VendorID
   AND C.CommentTimestamp =
       ( SELECT MAX(CommentTimestamp)
           FROM ENAPTEST.dbo.tblVendorComments
          WHERE VendorID = VI.VendorID ) 
 WHERE VI.VendorID = '1' 
   AND VI.DivisionName = 'BP'

r937.com | rudy.ca

 

[tsp1lrk72]

Thanks 937.com -- it works, I appreciate your help- A star for you!

 

[bborissov]

tsp1lrk72 wants to return First, isn't it?
So I would wrote this that way:

FROM tblVendorInfo As VI
INNER JOIN (SELECT VendorID, tblVendorComments
                   FROM ENAPTEST.dbo.tblVendorComments Tbl1
            INNER JOIN (SELECT VendorID,
                               MIN(CommentTimestamp) AS Tmp
                        FROM ENAPTEST.dbo.tblVendorComments
                        GROUP BY VendorID) Tbl2
            ON Tbl1.VendorID         = Tbl2.VendorID AND
               Tbl1.CommentTimestamp = Tbl2.Tmp) Tbl3
ON C.VendorID = VI.VendorID AND
   C.CommentTimestamp = Tbl3.VendorID
 WHERE VI.VendorID = '1' AND
       VI.DivisionName = 'BP'

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

 

[r937]

MAX, MIN, what's da difference





r937.com | rudy.ca

 

[bborissov]

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

 

[AlexCuse]

Just use MAX when figuring my paycheck and I'll be happy

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[r937]

when people say "i want each X but only the first Y related to that X," they usually mean "oh, just give me the first one you find, they don't care which

so i always give them the latest, or the shortest, or anything except the first, and often, it was the right choice



r937.com | rudy.ca

 

[bborissov]

Then you could use:

...
MAX(C.Comment) AS Comment,
...
GROUP BY....

and you get some

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