[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

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

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



     
0
Jacques Smith
Asked:
Jacques Smith
  • 8
  • 5
  • 2
  • +2
1 Solution
 
cyberkiwiCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for a couple of alternatives, read this article:
http://www.experts-exchange.com/A_3203.html
0
 
Imran Javed ZiaCommented:
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
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Jacques SmithAuthor 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
0
 
Jacques SmithAuthor 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
0
 
wdosanjosCommented:
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

0
 
Jacques SmithAuthor 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
0
 
wdosanjosCommented:
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

0
 
Jacques SmithAuthor 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
0
 
wdosanjosCommented:
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

0
 
Jacques SmithAuthor 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
0
 
wdosanjosCommented:
Which ITEMDESC should be displayed in that case?
0
 
Jacques SmithAuthor 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
0
 
wdosanjosCommented:
OK. The following script will pick one of the descriptions just for reference:

SELECT OT1.ITEMNO, MIN(OT1.ITEMDESC) 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
GROUP BY OT1.ITEMNO, OT1.UNITCOST, OT2.DATE
ORDER BY OT1.ITEMNO

Open in new window

0
 
Jacques SmithAuthor 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
0
 
Jacques SmithAuthor Commented:
Thanks for your prompt assistance
0
 
cyberkiwiCommented:
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!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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