January 16, 2008 at 10:57 am
Hi:
I am moving the MS Access tables in MDB to MS SQL server, the original sql string in MS Access looks like this:
strSQL = "SELECT codTasks.taskTaskID, First(codSubTasks.subTaskID), First(codSubTasks.subTaskSequenceNum)
FROM codTasks LEFT JOIN codSubTasks ON codTasks.taskTaskID = codSubTasks.subtaskID
GROUP BY codTasks.taskTaskID, codTasks.subTaskID, codSubTasks.subTaskSequenceNum
ORDER BY codSubTasks.subTaskSequenceNum, codTasks.subTaskID
One Task might have many subTasks, above qurey will only get the first subtask which has the smallest value of subTaskSequenceNum.
Since the MSSql doesn't have First() function, how do I make a change? So far I can only get is ( after I remove the First()):
taskID subTaskID SequenceNum
100 123 10
100 228 20
100 3939 30
The really data I want to get is the first row.(100 123 10)
Thank you very much.
JT
January 16, 2008 at 11:07 am
Try using the ROW_NUMBER() options. This uses a CTE
WITH CT (taskID,subtaskID,sequencenumber, RN) as
( select ct.taskTaskID as TaskID,
subTaskID,
subTaskSequenceNum as SequenceNumber,
ROW_NUMBER() over (PARTITITION BY taskTaskID ORDER by taskTaskID,SubtaskID)
FROM codTasks CT
LEFT JOIN codSubTasks CST ON CT.taskTaskID = CST.taskTaskID
GROUP BY CT.taskTaskID, CST.subTaskID, CST.subTaskSequenceNum
) as Tasklist
SELECT taskID,subtaskID,SequenceNumber
FROM TaskList
where RN=1
ORDER BY TaskList.subtaskID, TaskList.SequenceNumber
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 6:19 pm
Perhaps a TOP 1 With an ORDER BY clause would also meet the need.
January 16, 2008 at 8:11 pm
Thanks for your email. I think the first answer looks like very complicated, does that need to be so hard?
I prefer to use top 1, order by, but I really don't know how, just want to repeat what I want to get.
tblTask.taskID , tblSubTask.subTaskID, tblSubTask.subTaskSeqNum
100 , 409 , 12
100 , 590 , 3
100 , 3939 , 9
200 ,339 , 8
200 , 775 , 99
I want to get(the data which has same taskID but has smallest subtaskSeqNum)
100 , 590 ,3
200 ,339 , 8
Any comments suggestion? Thanks a lot.
James
January 17, 2008 at 6:42 am
If I would try to write in "SQL 2000" style SQL, I think I'd do something like
Select codSubTasks.taskID, codSubTasks.SubTaskID, codSubTasks.subTaskSequenceNum
(
Select codTasks.taskID, Min(codSubTasks.SubTaskID) MinSubTaskID
From codTasks
INNER JOIN codSubTasks ON codTasks.taskTaskID = codSubTasks.subtaskID
Group By codTasks.taskID
) MinTasks
INNER JOIN codSubTasks ON MinTasks.taskID = codSubTasks.taskID and MinTasks.MinSubTaskID = codSubTasks.SubTaskID
I'm writing this without testing, so pardon any errors.
What you basically have to do is find the taskID, and the minimum subtaskID, then join back to the table armed with that knowledge to lookup the subTaskSequence number.
I think Matts solution might be more efficient, since he is basically doing the same thing by making SQL Server find the first instance of a unique taskID and subTaskID. Try his query without the where clause to see the raw data that comes back.
In the query above, I am joining to the same table twice, so I don't think it will perform as well.
Hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply