How to Get previous record thru sql query

How to Get previous record thru sql query

For the example

my table:

1 usera item1 1.00 01/02/07
2 usera item1 2.00 02/02/07
3 userc item2 3.00 03/02/07

--
how to use the query to make them join became like this (get/join with the next record)

1 usera item1 1.00 01/02/07 item1 2.00 02/02/07
3 userc item2 3.00 03/02/07 null null null

>.<
need help ... thanks alot

You can try something like :

select m1.*, m2.Item, m2.Qty, m2.Date from mytable m1 LEFT JOIN mytable m2 ON m1.user=m2.user WHERE m1.date != m2.Date

Hope this helps,

Vivek

|||

it will work only if dates are different change it to (assuming that your first column is Identity ID)

select m1.*, m2.Item, m2.Qty, m2.Date from mytable m1 LEFT JOIN mytable m2 ON m1.user=m2.user WHERE m1.ID != m2.ID

to get data in all cases, but this will return only two sets of data per customer what if you have more records per customer?

And it will also repeat entries for your test data you will have records for users reported two times in different order 1 with 2 and 2 with 1

so you have to modify this query a little

select m1.*, m2.Item, m2.Qty, m2.Date
from mytable m1
LEFT JOIN mytable m2
ON m1.user=m2.user
andm1.ID != m2.ID
WHEREm1.ID < m2.ID

but it will work only if you have no more than 2 records per user

try and let me know if it works fro you or not.

Thanks

|||

Thanks alot who has replied me ^^

Yes.. i hav try on the way thatJpazgier provided, but it's too bad i m having more than 2 records in same user and the order is not just only at the top and bottom for example it may

1 usera item1 1.00 01/02/07
2 usera item1 2.00 02/02/07
3 userc item2 3.00 03/02/07
4 usera item1 1.50 04/02/07

and after i have join it will become

1 usera item1 1.00 01/02/07 item1 2.00 02/02/07
2 usera item1 2.00 02/0207 item1 1.50 04/02/07
3 userc item2 3.00 03/02/07 null null null
4 usera item1 1.50 04/02/07 null null null

thank u very much for ans my question ^^

|||

Another query for getting two consecutive rows (the row which you want along with the previous row)

select * from TableName Where DateField='02/03/07' Or DateField in(Select Top 1 DateField from TableName where DateField < ''02/03/07' Order by DateField DESC)

Thanks

Sorry Webmaster: (I am feeling bad that The forum @. ASP.Net was not allowed me to enter my Indian Standard Time (GMT + 5:30))