• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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
0
AD1080
Asked:
AD1080
  • 4
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
AD1080Author Commented:
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
Anthony PerkinsCommented:
You have a typo.  You have first V1 and then use VI.
0
 
Anthony PerkinsCommented:
Here is your corrected query:
SELECT  *
FROM    PO_VEND V
        INNER JOIN (SELECT  VEND_NO,
                            MAX(COMMNT_1) COMMENT_ONE
                    FROM    PO_VEND_ITEM
                    GROUP BY VEND_NO
                   ) VI ON V.VEND_NO = VI.VEND_NO
0
 
Anthony PerkinsCommented:
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
0
 
AD1080Author Commented:
Hi,

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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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