We help IT Professionals succeed at work.

Select rows with Max (Column Value), Distinct by another column value

Medium Priority
662 Views
Last Modified: 2012-08-14
Hi

I am trying to create a view from 2 tables whereby I need the Max(Date) from T2, and the distinct(Itemno) from T1. I have joined the tables with a left outer Join on UNQ

I am trying to determine the last purchase price and date

The tables are as follows
T1:
UNQ         ITEMNO              ITEMDESC     UNITCOST
1               A                        A-Item          2.50
1               B                        B-Item           3.50
1               C                        C-Item           1.50
1               D                        D-Item           4.50

2               A                        A-Item          2.25
2               B                        B-Item           3.65
2               C                        C-Item           1.55
2               D                        D-Item           4.40

3               A                        A-Item          2.58
3               C                        C-Item           1.56
3               D                        D-Item           4.55

4               A                        A-Item          2.52
4               C                        C-Item           1.54

T2:
UNQ        DATE          
1              11 Apr 11
2              12 Apr 11
3              13 Apr 11
4              14 Apr 11

Result to be
ItemNo       ItemDesc  UnitCost   Date
A                A-Item          2.52     14 Apr 11
B                B-Item           3.65     12 Apr 11
C                C-Item           1.54     13 Apr 11
D                D-Item           4.55     14 Apr 11


If I select Max(Date) I keep getting errors relating to Group by clause



     
Comment
Watch Question

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
select ItemNo, ItemDesc, UnitCost, Date
from
(
select a.ItemNo, a.ItemDesc, a.UnitCost, b.Date,
 rn=row_number() over (partition by a.itemno order by b.date desc)
from t1 a
left join t2 b on a.unq = b.unq
) X
where rn=1
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
for a couple of alternatives, read this article:
https://www.experts-exchange.com/A_3203.html
Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
Hi,
Try this

Select
      ITEMNO, ITEMDESC, UNITCOST, Date
From T1
      Inner Join (Select UNQ, Max(DATE) Date from T2 Group by UNQ) tbl2 on T1.UNQ = tbl2.UNQ

Thanks

Author

Commented:
cyberkiwi

If I do your script, I get a unhandled exception error, and the Management Studio Closes down

Below is the full text of my script

SELECT     dbo.PORCPL.ITEMNO, dbo.PORCPL.ITEMDESC, dbo.PORCPL.UNITCOST, dbo.PORCPH1.DATE
                     
FROM (select dbo.PORCPL.ITEMNO, dbo.PORCPL.ITEMDESC, dbo.PORCPL.UNITCOST, dbo.PORCPH1.DATE,
             rn=row_number() over (partition by dbo.PORCPL.ITEMNO order by dbo.PORCPH1.DATE desc)
           from dbo.PORCPL  LEFT OUTER JOIN
                      dbo.PORCPH1  ON dbo.PORCPL.RCPHSEQ = dbo.PORCPH1.RCPHSEQ) X
where rn=1

Thanks

Author

Commented:
IJZ

If I run your script, I get a Incorrect Syntax Error

Below is the full text that I Ran

SELECT     dbo.PORCPL.ITEMNO, dbo.PORCPL.ITEMDESC, dbo.PORCPL.UNITCOST, dbo.PORCPH1.DATE
                     
FROM     dbo.PORCPL  INNER JOIN (Select dbo.PORCPH1.RCPHSEQ, Max(dbo.PORCPH1.DATE) dbo.PORCPH1.DATE from dbo.PORCPH1 group by dbo.PORCPH1.RCPHSEQ)
                dbo.PORCPH1  ON dbo.PORCPL.RCPHSEQ = dbo.PORCPH1.RCPHSEQ


Thanks
Top Expert 2011

Commented:
Please try the following:

SELECT OT1.ITEMNO, OT1.ITEMDESC, OT1.UNITCOST, OT2.[DATE] 
  FROM T1 OT1
       INNER JOIN T2 OT2 
             ON OT2.UNQ = OT1.UNQ
       INNER JOIN (SELECT T1.ITEMNO, MAX(T2.[DATE]) [DATE]
                     FROM T1 
                          INNER JOIN T2 ON T2.UNQ = T1.UNQ
                     GROUP BY T1.ITEMNO) MDATE 
             ON MDATE.[DATE] = OT2.[DATE] AND MDATE.ITEMNO = OT1.ITEMNO
  ORDER BY OT1.ITEMNO

Open in new window

Author

Commented:
WDOSANJOS

I replaced your script with the table names, I hope correctly as you intended, Please see script below, but am getting incorrect syntax errors.

Could you please advise


SELECT
     dbo.PORCPL.ITEMNO, dbo.PORCPL.ITEMDESC, dbo.PORCPL.UNITCOST, dbo.PORCPH1.[Date]
                     
FROM    T1  dbo.PORCPL
                    INNER JOIN T2 dbo.PORCPH1
                         ON dbo.PORCPH1.RCPHSEQ=  dbo.PORCPL.RCPHSEQ
                    INNER JOIN (Select T1.ITEMNO, Max(T2.[DATE]) [DATE]
                                              from FROM T1
                                                               INNER JOIN T2 ON T2.RCPHSEQ=T1.RCPHSEQ
                                              GROUP BY T1.ITEMNO) MDATE
                               ON MDATE.[DATE] = dbo.PORCPH1.[DATE] AND MDATE.ITEMNO =  dbo.PORCPL.ITEMNO
  ORDER BY  dbo.PORCPL.ITEMNO
Top Expert 2011

Commented:
I applied your table names to the script.  Please try the following:

SELECT OT1.ITEMNO, OT1.ITEMDESC, OT1.UNITCOST, OT2.[DATE] 
  FROM dbo.PORCPL  OT1
       INNER JOIN dbo.PORCPH1 OT2 
             ON OT2.UNQ = OT1.UNQ
       INNER JOIN (SELECT T1.ITEMNO, MAX(T2.[DATE]) [DATE]
                     FROM dbo.PORCPL T1 
                          INNER JOIN dbo.PORCPH1 T2 ON T2.UNQ = T1.UNQ
                     GROUP BY T1.ITEMNO) MDATE 
             ON MDATE.[DATE] = OT2.[DATE] AND MDATE.ITEMNO = OT1.ITEMNO
  ORDER BY OT1.ITEMNO

Open in new window

Author

Commented:
wdossanjos

Thanks, I think we are getting there, The table displays except if an item is receipted more than once in a day, I am getting it showing multiple times.

I need to only show one item if there are multiples , as you can see by the image
 
 Item Listing
Top Expert 2011

Commented:
OK. A DISTINCT should resolve this issue:

SELECT DISTINCT OT1.ITEMNO, OT1.ITEMDESC, OT1.UNITCOST, OT2.[DATE] 
  FROM dbo.PORCPL  OT1
       INNER JOIN dbo.PORCPH1 OT2 
             ON OT2.UNQ = OT1.UNQ
       INNER JOIN (SELECT T1.ITEMNO, MAX(T2.[DATE]) [DATE]
                     FROM dbo.PORCPL T1 
                          INNER JOIN dbo.PORCPH1 T2 ON T2.UNQ = T1.UNQ
                     GROUP BY T1.ITEMNO) MDATE 
             ON MDATE.[DATE] = OT2.[DATE] AND MDATE.ITEMNO = OT1.ITEMNO
  ORDER BY OT1.ITEMNO

Open in new window

Author

Commented:
wdosanjos

Thanks for taking the effort to assist me.

Is there any way to make only the itemno distinct

The distinct makes every item of the select distinct, the idea is to make the itemno distinct so that I can get the view to list the latest unitcost for the item. if for example the user has typed a different description in for the same item no, it will list the itemno multiple times as the description.

SELECT DISTINCT TOP (100) PERCENT OT1.ITEMNO, OT1.ITEMDESC, OT1.UNITCOST, OT2.DATE
FROM         dbo.PORCPL AS OT1 INNER JOIN
                      dbo.PORCPH1 AS OT2 ON OT2.RCPHSEQ = OT1.RCPHSEQ INNER JOIN
                          (SELECT     T1.ITEMNO, MAX(T2.DATE) AS DATE
                            FROM          dbo.PORCPL AS T1 INNER JOIN
                                                   dbo.PORCPH1 AS T2 ON T2.RCPHSEQ = T1.RCPHSEQ
                            GROUP BY T1.ITEMNO) AS MDATE ON MDATE.DATE = OT2.DATE AND MDATE.ITEMNO = OT1.ITEMNO
ORDER BY OT1.ITEMNO





 View
Top Expert 2011

Commented:
Which ITEMDESC should be displayed in that case?

Author

Commented:
The Item description is not important, it is the item no that is.

At the end of the day, I want to be able,  out of my purchases Database, to see what was last amout paid for an itemcode, and when this occured.

if it says that the last purchase of the Item 123 was on 20110120, I want to see the unit cost, and what ever desc ties up with that record.

As Item 123, I know it is a Cabtyre, the added description is just what the purchase clerk entered for whatever reason. What I need to know is last cost

Thanks
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
wdosanjos

Thanks for your assistance, I am still sitting with duplicate itemno's, but I suppose that there is nothing one can do, as they seem to purchase the same item multiple times a day, at different prices. If you have any advice so as to only have one record for the day, with either the highest or the lowest unitcost, I would appreciate it.

I would ultimately like to get last purchase date per item, and the cost of it.
 Duplicate Item

Author

Commented:
Thanks for your prompt assistance
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
JacquesSmith,

I would advise you start SSMS again and try my query in the first comment.  I would have answered this typical question 100 times in the last year, so I know it should work.  The fact that it crashes SSMS is mind-boggling, I have not yet been able to crash SSMS just by running a query!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.