asked on

SQL Query

Hi,

I have the following tables.

Vendors - V
Items - I
Vendor Items (Which has a combination of Item and Vendor as its primary key) - VI

VI has a comment field with numeric values - COMMENT_1

I want to get all records from V along with the greatest COMMENT_1 in VI where V.VENDOR = VI.VENDOR

Thanks in advance. I hope my question is clear.

Ariel

Anthony Perkins

SELECT      *
FROM      Vendors V
INNER JOIN (
                        SELECT      VendorID,
MAX(COMMENT_1) MaxComment_1
                        FROM      [Vendor Items]
                        GROUP BY
                                    VendorID) VI ON V.VendorID = VI.VendorID

Raja Jegan R

This would do:

SELECT * 
FROM (
SELECT *, Row_number() over ( partition by v.vendor_id order by vi.COMMENT_1 desc) rnum
FROM Vendors V
INNER JOIN vendor_items vi on V.VendorID = vi.VendorID
INNER JOIN items i on vi.itemsid = i.itemsid ) temp
where rnum = 1

Open in new window

AD1080

ASKER

Thanks for the reply.

I am getting the following error with your solution as I have interpreted it below.
"The multi-part identifier "VI.VEND_NO" could not be bound." Can you see where I am off on this one?

SELECT * FROM

PO_VEND V

INNER JOIN(
      SELECT P.VEND_NO,
      MAX(P.COMMNT_1) COMMENT_ONE
      FROM PO_VEND_ITEM P
      GROUP BY P.VEND_NO)V1 ON V.VEND_NO = VI.VEND_NO

Raja Jegan R

Typo here:

SELECT * FROM

PO_VEND V

INNER JOIN(
SELECT P.VEND_NO,
MAX(P.COMMNT_1) COMMENT_ONE
FROM PO_VEND_ITEM P
GROUP BY P.VEND_NO)VI ON V.VEND_NO = VI.VEND_NO

And in the meanwhile have you tried out my query..

Anthony Perkins

You have a typo. You have first V1 and then use VI.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

A word of caution, before you choose to use ROW_NUMBER() over a simple derived table, I would urge you to read the following article:

Using row_number() vs. a correlated sub-select
http://sqlservernation.com/blogs/brandongalderisi/archive/2009/09/20/using-row-number-vs-a-correlated-sub-select.aspx

AD1080

ASKER

Hi,

Thanks again, sorry to waste time over a type O.