Get DISTINCT with MAX Value - SQL Server Forums

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)

Get DISTINCT with MAX Value

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-07-09 : 14:26:37

After many attempts, I know it's close but just can't seem to find the correct way.

Table structure:


RowID    ItemName    QuoteName    Revision    ShipToID
1         ABC          QABC         0          1996
2         ABC          QABC         1          1996
3         ABC          QABC         2          1996
4         XYZ123       QXYZ123      0          1996
5         XYZ123       QXYZ123      1          1996
6         KKJTT        QKKJTT       0          2056

The sql should bring back two (2) rows but it's bringing back five (5).


SELECT i.*
FROM tblItems i
INNER JOIN
(SELECT rowid, MAX(revision) AS MaxRevision, 
	CASE WHEN LEN(ItemName) > 3 THEN ItemName ELSE QuoteName END AS ItemName
FROM tblItems
GROUP BY rowid, ItemName, QuoteName) jn 
ON i.rowid = jn.rowid AND i.revision = jn.MaxRevision
WHERE i.ShipToID = 1996
ORDER BY ItemName

Just can't seem to get only the MAX(Revision).
Any suggestions are welcome.

Thanks!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-09 : 14:38:25

What do you want for a result?

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-07-09 : 14:40:24

quote:
Originally posted by Lamprey

What do you want for a result?

It should be:


RowID    ItemName    QuoteName    Revision    ShipToID
3         ABC          QABC         2          1996
5         XYZ123       QXYZ123      1          1996

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-09 : 14:41:06

[code]SELECT
*
FROM
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY ItemName, [QuoteName] ORDER BY Revision DESC) AS RowNum
FROM
tblItems
WHERE
ShipToID = 1996
) AS T
WHERE
RowNum = 1[/code]

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-07-09 : 14:47:49

That helped. I was looking at ROW_NUMBER() OVER but it's been a while since I used it. Need to brush up on that one I guess.

Thanks again!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources