Help needed on confusing Select Query please.

[Juason]

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!

[cweiss]

If you removing anything with a '-' in front of it would give you the records you're looking for, why not just say something like:

SN Not Like '-%'

[Juason]

If you removing anything with a '-' in front of it would give you the records you're looking for, why not just say something like:

SN Not Like '-%'

I put the '-' there manually when I copied it into this post, just to illustrate which ones I didn't want returned :P

[cweiss]

So...that's not your results table :D

Hmm, you can't group on just date, because the other fields have different values. If the pattern of your data is consistent with what you have in the example, then you can group using max of all fields except SN. I don't know that you have to use a subquery, but you get the general idea:

SELECT 
  SN, MAX(DATE), MAX(ACTIONDEFID), MAX(SEQNUM), MAX(SUBSEQNUM)
  FROM (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)
GROUP BY SN

I'm not 100% this will work, or if it's the best way to go about it though.

[Juason]

That gives me the following error :

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.


The ActionLog table contains every single thing that happens in our process. What I'd like to do, is select a single LinkId, and find all the SNs associated with it in their most current states. Most current meaning, newest date-wise. Unfortunately my initial query has the SN duplicated, because it shows both new and old actions. I've just had no luck using the grouping. Thank you again for any help you can give me.

So...that's not your results table :D

Hmm, you can't group on just date, because the other fields have different values. If the pattern of your data is consistent with what you have in the example, then you can group using max of all fields except SN. I don't know that you have to use a subquery, but you get the general idea:

SELECT 
  SN, MAX(DATE), MAX(ACTIONDEFID), MAX(SEQNUM), MAX(SUBSEQNUM)
  FROM (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)
GROUP BY SN

I'm not 100% this will work, or if it's the best way to go about it though.

[cweiss]

Try taking out the ORDER BY clause in the subquery and see if that works. Oh what dbms are you using? Oracle? MySQL? Access?

SELECT 
  SN, MAX(DATE), MAX(ACTIONDEFID), MAX(SEQNUM), MAX(SUBSEQNUM)
  FROM (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)
GROUP BY SN

[Juason]

I am using Microsoft SQL Server 2005. I get the following error without the ORDER:

Incorrect syntax near the keyword 'GROUP'.

[cweiss]

Well, this is one way you could do in oracle, but I don't know if it'll translate over into SQL Server:

SELECT A.SN, A.Date, A.ActionDefID, B.SeqNum, B.SubSeqNum
FROM 
  dbo.ActionLog A, 
  ActionDef B, 
  dbo.serials C
  (SELECT SN, MAX(DATE) MAX_DATE FROM DBO.ACTIONLOG GROUP BY SN) D
WHERE
A.ActionDefID = B.ID_ActionDef AND
A.S_Number = C.ID_Serials AND
A.SN = D.SN AND
A.DATE = D.MAX_DATE
WHERE LinkID = 1002
ORDER by SeqNum DESC, SubSeqNum DESC, SN DESC

If that doesn't work, then your only other option is to use Oracle :D

(or hopefully someone else here can help you, either way)