?
Solved

Find the Last Record in SQL based on  groups

Posted on 2011-05-03
5
Medium Priority
?
306 Views
Last Modified: 2012-08-13
I have a table

Job        WorkCentre QtyManuf Journal     EntryNumber Runtime
C00001    ABC               25              358        20                 2.5
C00001    ABC               25              358        19                 2.4
C00001    ABC               25              357        99                 2.5
C00002    ABC               25              361        25                 3.1
C00002    ABC               25              362        25                 3.2

How can I only show the Last Records based on the Last Journal and Last EntryNumber per JOB...In this example I would only show the following 2 Records

C00001    ABC               25              358        20                 2.5
C00002    ABC               25              362        25                 3.2

Thank you
       
0
Comment
Question by:Mikeyman_01
5 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 35517285

;WITH CTE AS {
SELECT
   Job
   ,WorkCentre
   ,QtyManuf
   ,Journal
   ,EntryNumber
   ,Runtime
   ,ROW_NUMBER() OVER (Partition By Job ORDER BY Journal DESC) RN
FROM
   Table

}


SELECT
	*
FROM	
	CTE
WHERE
	RN = 1

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35517427
You need to include EntryNumber column also in the ORDER BY clause of ROW_NUMBER.
;WITH CTE 
     AS (SELECT Job, 
                WorkCentre, 
                QtyManuf, 
                Journal, 
                EntryNumber, 
                Runtime, 
                ROW_NUMBER() 
                  OVER(PARTITION BY Job ORDER BY Journal DESC, EntryNumber DESC) RN 
           FROM Your_Table) 
SELECT * 
  FROM CTE 
 WHERE RN = 1 

Open in new window

0
 
LVL 11

Accepted Solution

by:
brutaldev earned 2000 total points
ID: 35517461
The above SQL works but contains syntax errors as well as missing a required order by column to ensure the latest journal AND entry. Try this instead:
 
;WITH CTE AS (
SELECT
   Job
   ,WorkCentre
   ,QtyManuf
   ,Journal
   ,EntryNumber
   ,Runtime
   ,ROW_NUMBER() OVER (Partition By Job ORDER BY Journal DESC, EntryNumber DESC) RN
FROM YOUR_TABLE_OF_DATA

)

SELECT *
FROM CTE
WHERE RN = 1

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35688929
Select * from (Select ROW_NUMBER()over(PARTITION BY Job  order by Job desc) As RN, * From (
Select 'C00001' As Job, 'ABC' As WorkCentre,   '25' as QtyManuf,  '358' AS Journal, '20' AS EntryNumber, 2.5 AS Runtime
union
Select 'C00001' As Job, 'ABC' As WorkCentre,   '25' as QtyManuf,  '358' AS Journal, '19' AS EntryNumber, 2.4 AS Runtime
union
Select 'C00001' As Job, 'ABC' As WorkCentre,   '25' as QtyManuf,  '357' AS Journal, '99' AS EntryNumber, 2.5 AS Runtime
union
Select 'C00002' As Job, 'ABC' As WorkCentre,   '25' as QtyManuf,  '361' AS Journal, '25' AS EntryNumber, 3.1 AS Runtime
union
Select 'C00002' As Job, 'ABC' As WorkCentre,   '25' as QtyManuf,  '362' AS Journal, '25' AS EntryNumber, 3.2 AS Runtime) A) B
where RN = 1
0
 

Author Closing Comment

by:Mikeyman_01
ID: 35689408
T his was perfect...thank you very much
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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