Can I do this using a ''self-join''? Please Help.

Question

Post reply

Can I do this using a ''self-join''? Please Help.

John Costa

Grasshopper

Points: 17
More actions
August 14, 2006 at 6:56 pm

#173778

I have a table with the following values:
EMPLOYEE   DATE         REC_ID  CODE
1598       08/14/2006   24604   T2
12484      07/24/2006   24132   L3
18945      07/16/2006   24033   A4
18977      07/31/2006   24253   T2
20113      07/31/2006   24270   T2
20113      07/31/2006   24267   A1
Notice that employee 20113 has two records on the same date with different REC_IDs and CODEs. What I am trying to do is bring back one record for each employee based on the max REC_ID. In other words, I need my result set to look like this:
EMPLOYEE   DATE         REC_ID  CODE
1598       08/14/2006   24604   T2
12484      07/24/2006   24132   L3
18945      07/16/2006   24033   A4
18977      07/31/2006   24253   T2
20113      07/31/2006   24270   T2
The REC_ID column is NOT a primary key and can occur more than once in the table. I'm assuming this can be done through some sort of a self-join but I just can't figure out the syntax. What I am having to do now is create a temporary table containing each EMPLOYEE and MAX(REC_ID). I then run a second second query to update the temp table with the DATE and CODE values for the retrieved REC_ID values pulled in the first query. Not the most effcient way of doing it, but it works. However, I'd much rather do it in a single statement instead of two.
Can anyone help?
- John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

This should do it for you... includes a test setup so you can "play"...

--===== If the test table exists, drop it

IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

DROP TABLE #yourtable

--===== Create the test table (you really need a primary key if you don't have one)

CREATE TABLE #yourtable (Employee INT, Date DATETIME, Rec_ID INT, Code VARCHAR(2))

--===== Populate the test table with the data given on the post

INSERT INTO #yourtable (Employee,Date,Rec_ID,Code)

SELECT 1598,'08/14/2006',24604,'T2' UNION ALL

SELECT 12484,'07/24/2006',24132,'L3' UNION ALL

SELECT 18945,'07/16/2006',24033,'A4' UNION ALL

SELECT 18977,'07/31/2006',24253,'T2' UNION ALL

SELECT 20113,'07/31/2006',24270,'T2' UNION ALL

SELECT 20113,'07/31/2006',24267,'A1'

--===== Demonstrate the solution

SELECT yt.*

FROM #yourtable yt,

(--==== Derived table "d" finds max Rex_ID for each employee

SELECT Employee, MAX(Rec_ID) AS MaxRec_ID

FROM #yourtable

GROUP BY Employee

) d

WHERE yt.Employee = d.Employee

AND yt.Rec_ID = d.MaxRec_ID

ORDER BY yt.Employee, yt.Rec_ID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Costa Grasshopper Points: 17 More actions · Answer 2

Jeff,

That did it! I sincerely thank you for your help.

- John

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

You bet, John... thank you kindly for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert Hill-311095 Old Hand Points: 339 More actions · Answer 4

Here is another possibility using a subselect.

Uses sample tables from Jeffs response above.

SELECT yt.*

FROM #yourtable yt

WHERE

yt.rec_id = (select Max(rec_id) from #yourtable where yt.employee = employee)

Robert.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

I just can't bring myself to use a correlated subquery...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)