bbala Posted March 8, 2009 Share Posted March 8, 2009 i've a table as followsTranIdTrantimeTrantypeUserIdWhat i want is all transactions (records with all fields) of a particular UserId AND the next transaction for every transacition in the previous set..Next transaction of a particular transacition is the one which has the next TranId.how do i write a query for the above? Link to comment Share on other sites More sharing options...
bbala Posted March 12, 2009 Author Share Posted March 12, 2009 Am i asking a silly question?? wer do i learn more abt sql queries?? Link to comment Share on other sites More sharing options...
jesh Posted March 12, 2009 Share Posted March 12, 2009 You might get better results here if you post some sample data and what you'd expect the results of the query to be. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 12, 2009 Share Posted March 12, 2009 I didn't reply because I frankly have no clue how to make a query get a certain record and the next record. Not a single query, anyway. It's probably possible, but it takes a level of experience that I don't have. I would probably either use a stored procedure or a scripting language to do the processing for this. Link to comment Share on other sites More sharing options...
jeffman Posted March 12, 2009 Share Posted March 12, 2009 I'm glad you were stumped, JSG. Makes me feel less stoopid. I played with this for maybe 30 minutes. I figured maybe a subquery, and then (assuming the transaction IDs are sequential) you could grab the minimum transId that is greater than that of the record . . .I can probably make it work for the first record.But I'm not sure how to create an arbitrary number of subqueries based on the number of records that get returned.So a shell script is all I can think of, too. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 12, 2009 Share Posted March 12, 2009 I'm thinking this probably *is* possible with a single query, but the guy who writes that query is a professional DBA with some serious education and experience who works with databases and nothing else, all day. It would be pretty cool to have that type of knowledge (SQL is an extremely powerful language), but if I went to school for that stuff I'd probably come out of school no longer knowing how to write Javascript or something. Link to comment Share on other sites More sharing options...
stupor Posted March 12, 2009 Share Posted March 12, 2009 erm... going to stick my neck out and suggest thisselect b.* from testme aright join testme bon a.tranid = b.tranid - 1where a.userid = X or b.userid = X(where X is your user ID)which works on a table I knocked up in SQL Server 08, based on what i *think* you're asking, but I've suspect I've missed the point. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 12, 2009 Share Posted March 12, 2009 Actually that doesn't look half bad. Same-table joins always confuse me a little, it's hard for me to wrap my brain around that stuff without drawing a diagram or something. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.