March 7, 2011 at 2:57 pm
we have a query that creates a recordset like the one shown below. I'm looking for a different way to only show the earliest record with matching ids. the original query has "where id like = 'usercvb90_02%' " inside of it so if that could be included, great. the records in step with the values first, not_fir, always_rst, and help_irst show show. However, i cant filter by this column.
id,file_num,flow,step,times
usercvb90_02_01_08_24_25_670,60923717,timing,First,8:24:26
usercvb90_02_01_08_24_25_670,60923717,timing,second,8:24:30
usercvb90_02_01_08_24_25_670,60923717,timing,third,8:32:18
usercvb90_02_01_08_24_47_233,10500252579,timing,not_Fir,8:24:47
usercvb90_02_01_08_24_47_233,10500252579,timing,second,8:24:58
usercvb90_02_01_08_24_47_233,10500252579,timing,third,8:32:31
usercvb90_02_01_08_25_49_076,6000296567,timing,always_rst,8:25:49
usercvb90_02_01_08_25_49_076,6000296567,timing,second,8:25:55
usercvb90_02_01_08_25_49_076,6000294367,timing,third,8:32:43
usercvb90_02_01_08_26_54_185,105002330,timing,help_irst,8:26:54
March 7, 2011 at 3:32 pm
Can you please post the query you're looking to modify and maybe an example of the resultset you would like to see after you modify the query?
Also, what are the flow & step columns?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2011 at 3:45 pm
So you're looking for something like this?
SELECT
t1.*
FROM
UnnamedTable AS t1
JOIN
(SELECT
[id],
MIN( times) AS mintime
FROM
UnnamedTable AS t2
GROUP BY
[id]
) AS drv
ONt1.id = t2.id
and t1.times = t2.mintime
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 6:14 pm
Craig Farrell (3/7/2011)
So you're looking for something like this?
SELECT
t1.*
FROM
UnnamedTable AS t1
JOIN
(SELECT
[id],
MIN( times) AS mintime
FROM
UnnamedTable AS t2
GROUP BY
[id]
) AS drv
ONt1.id = t2.id
and t1.times = t2.mintime
Think you have to change your code to:
SELECT
t1.*
FROM
#UnnamedTable AS t1
JOIN
( SELECT
[id],
MIN( times) AS mintime
FROM
#UnnamedTable AS t2
GROUP BY
[id]
) AS drv
ON t1.id = drv.id
and t1.times = drv.mintime
March 7, 2011 at 7:42 pm
*facepalm* You're right, of course... what I get for untested code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 7:53 pm
March 7, 2011 at 8:33 pm
yup, I was able to work into their query. and i understand why your code works.
this is what they have, but occassionally call me when the query runs for 30+ mins. I dont understand why this works. I can't run the sub query by itself.
select distinct (h.id),h.file_no, fl.description AS FLOW, st.description AS STEP
from mytable h
inner join stepdef st on st.step=h.step and st.flow=h.flow
inner join flowdef fl on fl.flow=h.flow
AND h.times = (SELECT MIN(times) FROM mytable WHERE id = h.id)
the table contains data for workflow auditing...tracking steps through a workflow.
after modifying thier query, the execution plan does change. However, it still has a sort step with 30%. I'm thinking this would be causing the long query times because it carries the most percent. Also, the table has over 18 million records.
March 7, 2011 at 8:47 pm
the table contains data for workflow auditing...tracking steps through a workflow.
after modifying thier query, the execution plan does change. However, it still has a sort step with 30%. I'm thinking this would be causing the long query times because it carries the most percent. Also, the table has over 18 million records.
Can you post the table definition, and in particular the indexes defined for the table and the query execution plan. To post the plan click on the 2nd link in my signature block for easy to follow instructions Then someone will be better informed and may be able to assist you.
March 8, 2011 at 6:16 am
i dont own the rights to do so. the easiest work to do on this db is custom queries. i still dont understand how their subquery can work without being able to run it by itself.
March 8, 2011 at 6:40 am
foscsamuels (3/8/2011)
i dont own the rights to do so. the easiest work to do on this db is custom queries. i still dont understand how their subquery can work without being able to run it by itself.
There were enough ambiguities in your original post to compel me to ask for DDL and DML. Without that it is mostly an exercise in speculation. If you cannot post the actual DDL maybe you can contrive an example with columns names and data obfuscated so we can at least work on the technique with you without you divulging anything that would get you in trouble.
foscsamuels (3/8/2011)
i still dont understand how their subquery can work without being able to run it by itself.
Lookup "correlated sub-queries".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 10:47 am
As far as posting table def and such, I'm just not that comfortable with it. That and the problem hasnt reached a critical point yet. I believe I have enough data here to continue research on my own.
Currently, I'm happy with the responses that were made. As a matter of a fact, I'm quite impressed with the knowledge and responsiveness of the replies (especially given the vagueness of the original post).
Many Thanks.
😀
March 8, 2011 at 2:49 pm
Without knowing what indexes exist it is hard to optimize the T-SQL as I am sure you understand. Now just to give you an additional approach you might want to test this code. Note: With proper indexes the sort step in either this method or others might be as much as eliminated. Either way good luck, if you have any further questions, start a new forum and ask away.
;with numbered as(SELECT rowno=row_number() over
(partition by ID, file_num order by ID),ID,file_num,flow,step,times from UnnamedTable)
select * from numbered WHERE rowno = 1
Results:
rownoID file_numflowstep times
1usercvb90_02_01_08_24_25_67060923717timingFirst 1900-01-01 08:24:26.000
1usercvb90_02_01_08_24_47_23310500252579timingnot_Fir 1900-01-01 08:24:47.000
1usercvb90_02_01_08_25_49_0766000294367timingthird 1900-01-01 08:32:43.000
1usercvb90_02_01_08_25_49_0766000296567timingalways_rst1900-01-01 08:25:49.000
1usercvb90_02_01_08_26_54_185105002330timinghelp_irst1900-01-01 08:26:54.000
March 8, 2011 at 9:46 pm
Craig Farrell (3/7/2011)
*facepalm* You're right, of course... what I get for untested code.
Not your fault for not testing. It's what happens when the OP doesn't post readily consumable test data. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2011 at 9:51 pm
foscsamuels (3/8/2011)
i dont own the rights to do so. the easiest work to do on this db is custom queries. i still dont understand how their subquery can work without being able to run it by itself.
It's called a "correlated subquery" and you can actually find a good set of explanations and some examples if you lookup "correlated subqueries" in Books Online (the "help" system that comes with SQL Server). It's a classic structure that I generally try to steer away from because it's difficult to troubleshoot the subquery without being able to execute the outer query.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply