Solved

# Find the Last Record in SQL based on  groups

Posted on 2011-05-03
Medium Priority
306 Views
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
Question by:Mikeyman_01

LVL 15

Expert Comment

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
``````
0

LVL 41

Expert Comment

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
``````
0

LVL 11

Accepted Solution

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
``````
0

LVL 21

Expert Comment

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

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

## Featured Post

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…
###### Suggested Courses
Course of the Month16 days, 8 hours left to enroll