CODEWITHSUNDEEP
sqlmysql
Shift
Shift

Reputation: 642

SQL for reading records in between

I have the following data on databse.

ID  |SOURCE|DATETIME          | URL                          | TRANSACTION_ID 
1001|254|2011-01-01 12:00:00|http://www.domain.com/page1.html|NULL
1001|254|2011-01-01 12:00:00|http://www.domain.com/page3.html|NULL
1305|333|2011-01-01 12:00:00|http://www.domainx.com/page5.html|NULL
1432|777|2011-01-01 12:00:00|http://www.domainx.com/page5.html|NULL
1001|254|2011-01-01 12:00:00|http://www.domain.com/page5.html|NULL
1001|254|2011-01-01 12:00:00|http://www.domain.com/page2.html|NULL
1001|254|2011-01-01 12:00:00|http://www.domain.com/page1.html|457899447
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page1.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page3.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page5.html|NULL
1005|124|2011-01-01 12:00:00|http://www.domainx.com/page5.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page2.html|NULL
1005|214|2011-01-01 12:00:00|http://www.domainx.com/page5.html|NULL
1005|219|2011-01-01 12:00:00|http://www.domainx.com/page5.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page1.html|1235642321

What I want to do is based on the transaction id and SOURCE get the rows in between. So If want records after the last transaction (457899447) and before the new transaction(1235642321) with example query like

SELECT * FROM table WHERE SOURCE=254 && TRANSACTION_ID=1235642321

I should get the result below

1003|254|2011-01-01 12:00:00|http://www.domainb.com/page1.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page3.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page5.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page2.html|NULL
1003|254|2011-01-01 12:00:00|http://www.domainb.com/page1.html|1235642321

How can I acomplish this?

Upvotes: 0

Views: 87

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40309

Based on some assumptions based soley on what the small sample of data looks like:

  • The "Source" to filter on is the same as that found for the Transaction_Id passed in
  • Column "Id" is an ascending integer for all entries in the table for that Source

I come up with: (updated based on new data)

   WITH cteNew (Id, Source)
     as (--  Get Id and Source of transaction @Transaction_Id
         select Id, Source
          from MyTable
          where Transaction_Id = @TransactionId)
    SELECT *  
     from Mytable
      where Source = cteNew.Source  --  All entries for this source
       and Id = cteNew.Id

I made it a cte in the hopes that the SQL optimizer will opitimize the redundant queries (table scans), but you could break it into two queries with the first assigning Id and Source to local variables.

(Couldn't test it, some syntax debugging may be required.)

Edited, based on comments with @Chris Lively:

If it is the simpler situation, then a simple subquery like so should work--and MySQL supports these, doesn't it?

SELECT * 
 from Mytable 
 where Id = (select Id
              from MyTable
              where Transaction_Id = @TransactionId)

This assumes (again) that Transaction_ID is unique within the table.

Upvotes: 2

Vlagged
Vlagged

Reputation: 513

Either you use

WHERE ... AND (TRANSACTION_ID=1235642321 OR TRANSACTION_ID IS NULL)

or:

WHERE ... AND COALESCE(TRANSACTION_ID,12345642321)=1235642321

Upvotes: 0

Related Questions