Get last record only if multiple exist

Question

Post reply

Get last record only if multiple exist

Viewing 7 posts - 16 through 21 (of 21 total)

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

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 1

I think sushila had it right.

select emp_id, company, max(CheckNumber), Amount

from phbasic

where pay_period_end = '11/25/2006'

group by emp_id, company, Amount

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Lol, I think I overcomplicated this one a little bit in the end .

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 3

LOLZ

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

But now you see how you can get a subset of data using a more complexe group by, then self join to get the rest of the columns for those specific rows.

Both solutions worked in this case but it's not always like that .

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Rereading this I realize you state can have the same date time for more than one record. Is there any other way to identify them in that scenario or do you just want either row without regard of any other difference?

DanielG-259128 SSC Veteran Points: 291 More actions · Answer 6

I would use a cursor to retrieve all employees that have duplicates, then load the first record found in a sorted list for this employees into a temp table variable.

declare @empl_no as varchar(6)

Declare @Tmp_Tbl table ( EMPL_Code varchar(6), [Date1] datetime,Empl_name varchar(20))

DECLARE EMPL_List CURSOR FOR

SELECT empl_CODE

FROM dbo.All_Empl WITH(NOLOCK)

Group by empl_CODE

Having count(1) > 1

OPEN EMPL_List

FETCH NEXT FROM EMPL_List

INTO @EMPL_NO

WHILE @@FETCH_STATUS = 0

BEGIN -- Empl_List eof

insert into @Tmp_Tbl(empl_code, date1, empl_name)

Select Top 1 empl_Code, [Date],empl_name from dbo.All_empl

where empl_CODE = @EMPL_NO

order by date desc

FETCH NEXT FROM EMPL_List

INTO @EMPL_NO

End

select * from @Tmp_Tbl

;

deallocate EMPL_List

Jambu Krishnamurthy SSCrazy Points: 2054 More actions · Answer 7

if exists (select * from sysobjects where name = 't1')

drop table t1

create table t1(f1 int, f2 datetime)

insert into t1 values(111,getdate()-10)

insert into t1 values(222,getdate()-11)

insert into t1 values(333,getdate()-12)

insert into t1 values(444,getdate()-13)

insert into t1 values(555,getdate()-14)

insert into t1 values(666,getdate()-10)

insert into t1 values(222,getdate()-12)

insert into t1 values(222,getdate()-13)

insert into t1 values(555,getdate()-15)

insert into t1 values(555,getdate()-16)

insert into t1 values(555,getdate()-17)

select * from t1 order by f1, f2

111 2006-11-26 09:48:22.850

222 2006-11-23 09:50:09.157

222 2006-11-24 09:50:09.157

222 2006-11-25 09:49:17.893

333 2006-11-24 09:49:17.893

444 2006-11-23 09:49:17.893

555 2006-11-19 09:50:31.723

555 2006-11-20 09:50:31.723

555 2006-11-21 09:50:31.723

555 2006-11-22 09:49:17.893

666 2006-11-26 09:49:17.893

select f1,max(f2)

from t1

group by f1