?
Solved

SQL Query

Posted on 2010-01-09
8
Medium Priority
?
213 Views
Last Modified: 2012-05-08
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
Comment
Question by:AD1080
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275928
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26275951
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
 

Author Comment

by:AD1080
ID: 26275960
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26275967
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275968
You have a typo.  You have first V1 and then use VI.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 26275970
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275974
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
 

Author Closing Comment

by:AD1080
ID: 31675099
Hi,

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 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