MAX in column and joining table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MAX in column and joining table

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-03-25 : 14:47:25

Having a bit of trouble with this sql.
It's bring back too many rows.
The problem is the reference table.
This table has information on each revision, so, it returns all revisions.


SELECT QuoteName, MAX(i.revision) revision, 
i.ItemName, 
AS 'XrefID',
ref.ID AS 'XrefID' --if this is removed, correct num of rows returned
FROM Items i 
LEFT OUTER JOIN PriceQty q ON q.itemid = i.rowID
INNER JOIN PriceRef ref ON ref.PriceQtyID = q.rowid
GROUP BY QuoteName, i.ItemName, ref.ID
ORDER BY QuoteName DESC

I know I'm not seeing something obvious here so an extra set of eyes would be helpful.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-25 : 14:58:06

Is revision in the PriceRef table? If so, can you add

and ref.revision = i.revision to that join?

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-03-25 : 15:36:12

No, the revision is only in the Items table.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-25 : 16:01:42

Then, how can you tell which PriceRef row goes with which Item row with respect to revision?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-25 : 18:14:54

Maybe this?:


SELECT QuoteName, i.revision,
    i.ItemName, 
    ref.ID AS 'XrefID'
FROM (
    SELECT 
        Items.*,
        ROW_NUMBER() OVER(PARTITION BY Items.ItemName ORDER BY ID DESC) AS row_num
    FROM Items
) AS i
LEFT OUTER JOIN PriceQty q ON q.itemid = i.rowID
INNER JOIN PriceRef ref ON ref.PriceQtyID = q.rowid
WHERE
    i.row_num = 1
ORDER BY QuoteName DESC

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-03-26 : 08:28:50

That's it. Thanks for the help Scott. I've been trying to get this to work for hours.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources