Problematic Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problematic Select Query

Author

Topic

Juason
Starting Member

17 Posts

Posted - 2006-05-10 : 14:04:23

Greetings,

I am currently trying to organize some data in a way that is straightforward to parse in my web application. Here is the query:

select SN, Date, ActionDefID, Actiondef.SeqNum, Actiondef.SubSeqNum
from dbo.ActionLog
join dbo.ActionDef on dbo.ActionLog.ActionDefID = ActionDef.ID_ActionDef
join dbo.serials on dbo.ActionLog.S_Number = dbo.serials.ID_Serials
WHERE LinkID = 1002
ORDER by SeqNum DESC, SubSeqNum DESC, SN DESC

This results in a table as follows:

SN | Date | ActionDefID | SeqNum | SubSeqNum

88671 2006-05-12 11:07:06.000 24 300 0
-88671 2006-05-11 11:17:06.000 23 200 0
88670 2006-05-11 11:17:06.000 23 200 0
88669 2006-05-11 11:17:06.000 23 200 0
88668 2006-05-11 11:17:06.000 23 200 0
88667 2006-05-11 11:17:06.000 23 200 0
-88671 2006-05-10 10:07:06.077 22 100 0
-88670 2006-05-10 10:07:06.077 22 100 0
-88669 2006-05-10 10:07:06.077 22 100 0
-88668 2006-05-10 10:07:06.077 22 100 0
-88667 2006-05-10 10:07:06.060 22 100 0
88666 2006-05-10 10:07:06.060 22 100 0
88665 2006-05-10 10:07:06.060 22 100 0
88664 2006-05-10 10:07:06.060 22 100 0
88663 2006-05-10 10:07:06.060 22 100 0
88662 2006-05-10 10:07:06.060 22 100 0
88661 2006-05-10 10:07:06.060 22 100 0
88660 2006-05-10 10:07:06.060 22 100 0
88659 2006-05-10 10:07:06.043 22 100 0
88658 2006-05-10 10:07:06.043 22 100 0
88657 2006-05-10 10:07:06.043 22 100 0

Now, what I would like is for all SNs to be UNIQUE based on the latest Date. This would be accomplished by removing all entires with a '-' in front of them. I have tried grouping by MAX(Date), but that has not worked for me either.

The purpose of this Query is to identify the most current step that each SN in the process is at. Hence wanting to remove duplicate SNs. Thank you in advance for any help you can provide me with!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-10 : 14:21:27

When u query, if u r getting the above results what do u want to do?
To delete those has a hyphen in front of SN ?
To Query those without hyphen in front of SN ?

or something else ?

Srinika

Juason
Starting Member

17 Posts

Posted - 2006-05-10 : 14:46:32

I would like to capture only those entires without a '-' in front of them, effectively filtering out duplicate serial numbers with an older date.

Juason
Starting Member

17 Posts

Posted - 2006-05-10 : 14:47:59

I should point out, I put the '-' in manually when copying the result set into this post. I did it to highlight the ones I wished to not have returned :P

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-10 : 16:31:41

Check this :


Create table #t (SN int, [Date] datetime, ActionDefID int, SeqNum int, SubSeqNum int)

Insert into #t values (88671, '2006-05-12 11:07:06.000', 24, 300, 0)
Insert into #t values (88671, '2006-05-11 11:17:06.000', 23, 200, 0)
Insert into #t values (88670, '2006-05-11 11:17:06.000', 23, 200, 0)
Insert into #t values (88669, '2006-05-11 11:17:06.000', 23, 200, 0)
Insert into #t values (88668, '2006-05-11 11:17:06.000', 23, 200, 0)
Insert into #t values (88667, '2006-05-11 11:17:06.000', 23, 200, 0)
Insert into #t values (88671, '2006-05-10 10:07:06.077', 22, 100, 0)
Insert into #t values (88670, '2006-05-10 10:07:06.077', 22, 100, 0)
Insert into #t values (88669, '2006-05-10 10:07:06.077', 22, 100, 0)

Select * from #t 
inner join (Select SN, max([Date]) as MB from #t group by SN ) Q 
on Q.SN = #t.SN and Q.MB = #t.[Date]

drop table #t

Srinika

Juason
Starting Member

17 Posts

Posted - 2006-05-11 : 09:15:02

Wow that whole thing worked :P Thank you for the help!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 10:34:32

I can see you got an answer, so this is just FYI

.

An alternative to Srinika's select query is:

select * from #t a where date = (select max(date) from #t where sn = a.sn)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources