January 31, 2009 at 5:33 pm
Heh. You know Jeff, I think that this might change that article that you're writing, just a little bit. I bet you don't mind too much though. 😛
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 31, 2009 at 6:58 pm
RBarryYoung (1/31/2009)
Heh. You know Jeff, I think that this might change that article that you're writing, just a little bit. I bet you don't mind too much though. 😛
Heh... you state the obvious on all counts. 😉 I've still got some tests to run, though... there was recently a disturbance in the Force.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2009 at 8:06 pm
Hi Jeff
I am glad you had fun with the article. I am surely not at this level!
I cannot give you a lot more information on the purpose of my request. I know it's for a study about academic tendency over time. I don't have specific details.
I will look further on the code you provide.
thank you
Martin
January 31, 2009 at 8:20 pm
dubem1 (1/31/2009)
Hi JeffI am glad you had fun with the article. I am surely not at this level!
I cannot give you a lot more information on the purpose of my request. I know it's for a study about academic tendency over time. I don't have specific details.
I will look further on the code you provide.
thank you
Martin
Yeah... I had a ball with it. I love this kind of stuff.
Say, Martin... since we were able to answer your question in the form of two sets of working code, would you mind asking the folks you're doing this for, for some details? I'd really like to know what the pieces of this little puzzle are going to be used for.
Thanks a bunch.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2009 at 8:36 pm
Hello Jeff,
I try your code and found it not really do what I want because if you have this (the following show the 10 last exam result code of a student. there is also a examDate column which contain the date of the exam)
ID ResultCode
10 A
9 A
8 A
7 B
6 A
5 C
4 A
3 A
2 A
1 A
Those are the 10 last exam result of a particular student.
I would need the latest result code and the latest sequence so
A 3
You query returns
A 4 (this student got a sequence of 4 A earlier, but it's not the most recent sequence)
I ended up with this query to find the last sequence of a student (based on your solution):
SELECT ResultCode,
examDate,
CAST(0 AS INT) AS RunningCount
INTO #MyHead
FROM MyTable WITH(INDEX(0)) WHERE StudentId = 421 ORDER BY examDate
--===== Declare some obviously named variables
DECLARE @RunningCount INT,
@PrevResultCode CHAR(1)
--===== Do a little magic update to "rank" the rows in a way that
-- ROW_NUMBER() and RANK() can't do.
UPDATE #MyHead
SET @RunningCount = RunningCount = CASE WHEN @PrevResultCode = ResultCode THEN @RunningCount+1 ELSE 1 END,
@PrevResultCode = ResultCode
FROM #MyHead
--===== Display the results after the update
Select ResultCode, runningcount
From #MyHead
Where examDate = (Select max(examDate) from #MyHead)
January 31, 2009 at 8:47 pm
dubem1 (1/31/2009)
Hello Jeff,I try your code and found it not really do what I want because if you have this (the following show the 10 last exam result code of a student. there is also a examDate column which contain the date of the exam)
ID ResultCode
10 A
9 A
8 A
7 B
6 A
5 C
4 A
3 A
2 A
1 A
Those are the 10 last exam result of a particular student.
I would need the latest result code and the latest sequence so
A 3
You query returns
A 4 (this student got a sequence of 4 A earlier, but it's not the most recent sequence)
I ended up with this query to find the last sequence of a student (based on your solution):
SELECT ResultCode,
examDate,
CAST(0 AS INT) AS RunningCount
INTO #MyHead
FROM MyTable WITH(INDEX(0)) WHERE StudentId = 421 ORDER BY examDate
--===== Declare some obviously named variables
DECLARE @RunningCount INT,
@PrevResultCode CHAR(1)
--===== Do a little magic update to "rank" the rows in a way that
-- ROW_NUMBER() and RANK() can't do.
UPDATE #MyHead
SET @RunningCount = RunningCount = CASE WHEN @PrevResultCode = ResultCode THEN @RunningCount+1 ELSE 1 END,
@PrevResultCode = ResultCode
FROM #MyHead
--===== Display the results after the update
Select ResultCode, runningcount
From #MyHead
Where examDate = (Select max(examDate) from #MyHead)
Post the data in a readily consumable format like I did in my code and I'll take a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2009 at 8:52 pm
I don't know how to do it!!
January 31, 2009 at 8:54 pm
Actually, I didn't wait...
Did you drop an recreate the table with the new data? Both solutions I posted return A 3 for the new data.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2009 at 9:02 pm
You are right,sorry, my mistake
Both yout solution give the exact wanted result
January 31, 2009 at 9:10 pm
Jeff,
with your second solution, the last select return 3 rows, one for each result. I can this select return only a single row, with the most recent result code?
Thank you
Martn
January 31, 2009 at 9:14 pm
dubem1 (1/31/2009)
I can this select return only a single row, with the most recent result code?
Sure... just like you can in the first. Just add a condition to the final select for the ResultCode you desire (if that's what you meant by "return" only a single row"). In fact, if you turn the second solution into a View and select from that with the correct criteria for ResultCode, that would probably be even better.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2009 at 10:29 pm
RBarryYoung (1/31/2009)
Jeff Moden (1/31/2009)
Nah... I take it back... not clever... freakin' brilliant. The key to this whole thing is the grouping. You've gotta be able to group each "cluster" of rows that are in a sequence by date and slot. I knew it couldn't be done with a single ROW_NUMBER, but the winner used the difference between two ROW_NUMBERs with different partitions to generate the GRP for each student... absolutely brilliant.Heh, I agree 100% Jeff. I am stupefied at the cleverness of this mathematical trick, (and PO'd at myself for not having figured it out for myself before now). I am still trying to figure out in my head exactly why it never repeats a group number within the larger partitions.
Cool stuff. I sure hope that I remember to use this trick in the future...
If you're a student of sql you may remember characteristic functions. In 1992 when the first paper about them was published (David Rozenshtein) the sql community haled them as the greatest thing since sliced bread. Sql programmers thought it was super clever to cobble together various math functions to eventually evaluate to 0,1 and thus simulate if-then logic. But most developers thought it was unintuitive and way too complex. Fortunately sql vendors agreed with them and by the late 1990's characteristic functions were obsolete being completely replaced by the simple CASE construct. This 'clever' ranking trick is just the latest incarnation of characteristic functions. Yes it's a clever trick but it contains zero abstraction about the problem it's trying to solve. Do sql programmers really think this trick will jump out at developers like CASE?:) This remains a 'dense' rank problem. But in this case the column(s) that are the object of the rank are not the sort order of it. The target and sort order are
independent and that's why even the current dense_rank() function is unless in this situation. The sql vendors, ie. the sql standards committee, was just plain lazy or stupid by not recognizing target and order as possibly independent. Even with the full implementation of analytic window functions, which will greatly simplify the solution without any clever 'tricks', there is no abstraction like dense_rank() to help developers out with a simple and clear solution. You would think experts would lead the charge for higher levels of abstractions to solve problems. While you see a lot of celebration for 'tricks' there's way too much silence to move the language to better and more intuitive solutions. Sql experts too often lose sight of that big guiding light - KISS, keep it simple and stupid.
I note in passing of the very high level of abstraction to the Dataphor solution to this type of ranking problem. All that is necessary is for the developer to recognize what 'type' of rank is the solution (ie. dense rank). Download the operator and simply rename the column(s) in his table to match the signature of the operator. KISS:)
'Creating a Super Function'
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html
P.S. I think credit to this approach should go to MVP Steve Kass. He proposed something like this years ago to create an ordinal value identifying groups of rows. This approach is quite common in the microsoft.public.sqlserver.programming group. But it's still as bewildering to most as ever:)
February 2, 2009 at 11:39 pm
steve (2/2/2009)
If you're a student of sql you may remember characteristic functions. In 1992 when the first paper about them was published (David Rozenshtein) the sql community haled them as the greatest thing since sliced bread.
Uh, yeah. Not to intrude on your fantasy lecture here, Steve, but let's throw some actual facts in:
1) Rozenshtein's 1992 paper was not even remotely "the first paper about (characteristic functions)", as you stated.
2) I, and many other were using them in SQL well before 1992.
3) I, for one, have been using them in relational databases since the early 80's. And yes we all called them "characteristic functions" (though to be technical they were usually expressions, not functions, though that makes no difference here).
4) In fact, I and many others, had been using them on CODASYL databases before that. And I am sure that those "others" had been using them on databases as long as database have existed.
5) And in fact, I am sure that they had been using them in programs and on computers, again just about as long as they have existed (probably back to the Bletchley Park, in the 40's).
6) I first thought that I had "invented" them in 1972. Then I got to college in 1975 and learned that they were called "characteristic functions" and that, ...
7) Gottfried Leibniz is credited with first discovering them in the 1700's. You will note that this preceeds 1992 by a little bit.
8) The function that Jeff & I were so impressed with (expression actually), is not in fact a Characteristic function. So your discussion lacks a certain amount of relevance.
9) And finally, your inability to appreciate what Jeff & I find impressive does not constitute a reason us or others to adopt your condescension. Most of us have the ability to appreciate other's accomplishments without seeing them as a threat to our own.
edit: removed stray line at the end.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 3, 2009 at 5:54 am
steve (2/2/2009)
This 'clever' ranking trick is just the latest incarnation of characteristic functions.
BWAA-HAAA! So what? Heh... Consider this... If the people who wrote T-SQL and other languages got it all right the first time, people wouldn't have the need for what you're trying to sell. 😛
I agree with Barry... enjoy the cleverness of the code and the way the author of that code overcame the shortcomings of the language. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2009 at 9:06 pm
RBarryYoung (2/2/2009)
Uh, yeah. Not to intrude on your fantasy lecture here, Steve, but let's throw some actual facts in:...
1-7
I obviously was too ambiguous with my use of the term characteristic function. Rozenshtein was generally credited with showing how these functions could be used to do reports and various statistical computations, ie. crosstabs and medians. By the time his book was published:
Optimizing Transact-SQL : Advanced Programming Techniques
by David Rozenshtein, Anatoly Abramovich, Eugene Birger
October 1997
Sql-92 had replaced the functions with a simple CASE expression which
simplied reports for developers. And for what's it's worth the last time I talked to Gottfried he seemed to recall you using these functions too:)
8 - I was trying to make an analogy. At least I gave you a rantage point:)
9 - Far out. Far, far out 🙂
C'mon lite'n up on the pop offense. If you don't see merit doesn't mean there's a boogeyman:)
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply