CODEWITHSUNDEEP
mysqlsql
user1699121
user1699121

Reputation: 29

fetching previous row in sql

I need help on creating the sql for this requirement - I have the data in a table : 

Empid      Date         Deptid  Location
-----------------------------------------
001    1st Jan 2012    101      BC
002    4th Jan 2012    101      AB
003    6th Jan 2012    103      PQ
004    8th Jan 2012    104      AB
005    10th jan 2012   105      XY

Now I want to fetch the previous row (depending upon date field) where location is AB and dept value is different. in the above example the following row should be fetched

003    6th Jan 2012    103      PQ

any suggestions?

Upvotes: 2

Views: 251

Answers (5)

Rohit Subedi
Rohit Subedi

Reputation: 550

SELECT * FROM `YOUR_TABLE`
WHERE `id` < (SELECT `id` FROM `YOUR_TABLE`
              WHERE `location` =  'AB'
              AND `deptid` =  '104') 
ORDER BY id DESC LIMIT 1

May be this will help you :D

Upvotes: 1

G&#225;bor Plesz
G&#225;bor Plesz

Reputation: 1223

create table tblTest (
  Empid char(3)
  ,Date date
  ,Deptid char(3)
  ,Location char(2)
);

insert into tblTest (Empid,Date,Deptid,Location) values ("001","2012/01/01","101","BC");
insert into tblTest (Empid,Date,Deptid,Location) values ("002","2012/01/04","101","AB");
insert into tblTest (Empid,Date,Deptid,Location) values ("003","2012/01/06","103","PQ");
insert into tblTest (Empid,Date,Deptid,Location) values ("004","2012/01/08","104","AB");
insert into tblTest (Empid,Date,Deptid,Location) values ("005","2012/01/10","105","XY");

SELECT
  t2.*
FROM
  tblTest t1
  LEFT JOIN tblTest t2 ON t1.Date<t2.date
                          AND
                          t1.Deptid <> t2.Deptid
WHERE
  t1.Empid = "002"
ORDER BY
  t2.Date
LIMIT 1;

You can test on SQL Fiddle

Upvotes: 1

user1675644
user1675644

Reputation: 21

select * from table_name where Date < (select Date from table_name where Location="AB") and Deptid NOT IN( select Deptid from table_name where Location="AB") order by Date desc limit 1 ;

Returns the previous row based on Date field given that location is "AB" and also checks that the employee is not from the same department

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

Use this pattern. Actually I think I messed up the condition, which should be LastAB.DeptID!=Emp.DeptID but you get the idea. The subquery gives you the singular record that you want to start with. The outer query then finds the records of a prior date at a different Location (or DeptID or whatever condition you set) and uses ORDER BY + LIMIT 1 to pinpoint a single record.

select Emp.*
from Emp
join (
  select *
  from Emp
  where Location = 'AB'
  order by Date desc
  limit 1) LastAB on LastAB.Date>Emp.Date
                 and LastAB.Location!=Emp.Location
order by Emp.Date desc
limit 1;

Working SQL Fiddle replicated below

create table Emp (
  Empid char(3),
  Date datetime,
  Deptid int,
  Location char(2)
  );
insert Emp values
('001', '2012-01-01', 101, 'BC'),
('002', '2012-01-04', 101, 'AB'),
('003', '2012-01-06', 103, 'PQ'),
('004', '2012-01-08', 104, 'AB'),
('005', '2012-01-10', 105, 'XY');

--- result

EMPID   DATE    DEPTID  LOCATION
003     January, 06 2012   103  PQ

Upvotes: 0

Niladri Biswas
Niladri Biswas

Reputation: 4171

Assuming SQL Server 2005+

SELECT * FROM(
SELECT *

FROM(SELECT *,Row_Number() OVER(Order By [Date]) From <Your table>)X

WHERE X.[Date] = '8th Jan 2012' AND Location = 'AB')X WHERE Rn = Rn-1

Upvotes: 1

Related Questions