10401

SQL Server - Solve this with set-based solution instead of row iteration

Question:

I'm trying to move some of my business logic out of my programs and into stored procedures. I'm really a VB.NET programmer, and not a SQL expert, but I'm learning more SQL and finding that in a lot of cases, it's faster to let SQL do my processing and return small amounts of data rather than giving me a ton of stuff and having my programs chomp through it.

So, my current problem is this:

I'm creating a timeline of something that has occurred from several different sources in a database. The pertinent information I am pulling is:

<ol><li>A user name</li> <li>A time associated with an action</li> </ol>

I want to use this timeline to figure out, ultimately, who was responsible for a given thing at a given time. Thus, if 1 user logs 400 actions in a row before a new user logs something, I really don't care; I just want to see when user 1 started logging and when user 2 took over logging.

More graphical example:

<h2>User | Time</h2>

User1 | 12:00<br /> User1 | 12:01<br /> User1 | 12:02<br /> User1 | 12:03<br /> User1 | 12:04<br /> User1 | 12:05<br /> User1 | 12:06<br /> User2 | 12:07<br /> User2 | 12:08<br /> User2 | 12:09<br /> User2 | 12:10<br /> User2 | 12:11<br /> User1 | 12:12<br /> User1 | 12:13

What I'd like:

<h2>User Time</h2>

User1 | 12:00<br /> User2 | 12:07<br /> User1 | 12:12

Now, in code, I'd get that result set into a DataTable and iterate each row in the table. Then, I'd check the current row's [User Name] value against the previous row's [User Name] value and only add the current row's values if the [User Name] was different. There seems to be a general aversion among true SQL experts to using a cursor, but I'm not sure I yet think in that manner, so can anyone help me out here?

So far, I have successfully gotten the raw, unfiltered data into a table variable in my query. So, I just need to know how to "collapse" the data and only return a small subset.

Thanks!

Answer1:

<s> <strong>EDIT</strong> Needs 1 more level of indirection for filtering by rank to work:</s>

select User,Time from ( select * from ( Select User,Time, rank() over (partition by u.User order by u.Time) as User_Rank from your_table u ) UserRanks ) x where User_Rank = 1 order by Time

Similar to araqnid and Royi's answers, but using WHERE NOT EXISTS rather than JOIN.

with CTE as ( select user, time, row_number() over (order by time) rn from MyTable ) select CTE.user, CTE.time from CTE CTE1 where not exists (select user, time from CTE CTE2 where CTE1.rn = CTE2.rn - 1 and CTE1.user = CTE2.user)

Answer2:

This is one of those exceptions where a cursor is likely your best bet. Just try to limit the subset of data that you are going to iterate as much as you can.

Answer3:

<h2>Finally :</h2> ;with CTE as ( select user, time, row_number() over (order by time) rn from MyTable ) select CTE.user, CTE.time from CTE left join CTE other on other.rn = CTE .rn - 1 where other.user is null or CTE .user <> other.user

Answer4:

A row-based iteration is probably your best solution in SQL Server. Other database flavours allow you to example values from the previous/next row (lag and lead window functions), but SQL Server doesn't support those.

You could bodge something together like this:

with x as ( select user, time, row_number() over (order by time) rn from source ) select x.user, x.time from x left join x prev on prev.rn = x.rn - 1 where prev.user is null or x.user <> prev.user

However, I suspect this is inconvenient and performs abominably.

Recommend

  • JavaScript sorting issue with looping
  • Check a checkbox on a dropdown selection
  • iOS - Concurrent access to memory resources
  • GNUPLOT: Show a x value given a y value
  • How to display indirectly given unicode character in Swift?
  • How to develop Eclipse GUI plugins
  • In LinqPad, is there a way to serialize type XML column as string?
  • firebase unauth with google doesn't allow change of user
  • System call time out?
  • How to use the resource module to measure the running time of a function?
  • Pythons argparse default value doesn't work
  • Fully customized Python Help Usage
  • SSL client cert authentication for only some URLs?
  • sweetalert2 inputoptions from file in select example
  • Most efficient way to move table rows from one table to another
  • Detecting null parameter in preprocessor macro
  • Servlet stops working on Tomcat server after some hits or time
  • How can I replace the server in Web Component Tester
  • Azure webjobs output logs indexing taking very long
  • Switch to popup in python using selenium
  • Clear activity stack before launching another activity
  • ThreadStatic in asynchronous ASP.NET Web API
  • Jenkins: FATAL: Could not initialize class hudson.util.ProcessTree$UnixReflection
  • Record samples being played with OpenAL
  • What is Eclipse's Declaration View used for?
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Perl system calls when running as another user using sudo
  • Redux, normalised entities and lodash merge
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Authorize attributes not working in MVC 4
  • How can I remove ASP.NET Designer.cs files?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Can't mass-assign protected attributes when import data from csv file
  • Busy indicator not showing up in wpf window [duplicate]
  • Unable to use reactive element in my shiny app
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF