sql help

[foxtrotunicorncharliekilo]

Hello.

I need some SQL help please... pretty basic stuff

Dumb example below but it will get the concept though

table1: empId, name, surname, etc
table2: salaryid, empId, salaryAmount, SalaryDate.

What I need:

I need a query to find the 2nd and 3rd latest salary amount with the name and surname of the emp in the same row per employee.

 

[MarchantV]

I wrote this for PSQL. Not sure if it will work for you

SELECT table1.name, table1.surname, table2.salaryAmount, table2.salaryDate FROM table1, table2 WHERE table1.empid = table2.empid ORDER BY salarydate LIMIT 2 OFFSET 1;

Note: What field type is salarydate?

My apologies if this does not work.

 

[realbigdreamer]

There are various ways to do this. One easy way would be to do the following:

* Join the tables
* Order according to SalaryDate
* Select name, surname and amount
* Limit the results to the two rows that you require

I know in MySQL you can do the following for the last (limit) part:
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

Hope that helps.

 

[dadecoza]

he should do his own homework.

 

[Masataka]

Are you doing this in MSSQL, MySQL, etc? MSSQL has a few functions that will help get this done.

 

[Baxteen]

how is this

select EMPID, name,surname,
(select salaryamount
From table2
where table2.EMPID = table1.EMPID
AND DATEDIFF(M,SalaryDate,GETDATE()) = 2) as SEcondLastSalary,
(select salaryamount
From table2
where table2.EMPID = table1.EMPID
AND DATEDIFF(M,SalaryDate,GETDATE()) = 3) as ThirdlastSalary
From Table1


basic idea

write the select from table 1, and ignore the salary.
now write the query to get the second last salary for 1 employee
I used datediff here because we dont know what datetype your fields are.
but the idea can hold if there is some other sort of identifier, sure you can figure out how to do it,

now add the second part as a select statement in the select statement of the first query, linking table2's employee ID to table1's

repeat for the other field you want.

 

[MarchantV]

he should do his own homework.

We all have those moments where we just can't figure something out

 

[Batista]

Do your own homework, this is basic sql, what is going to happen when you have to do more advanced stuff?

 

[Batista]

Hello.

I need some SQL help please... pretty basic stuff

Dumb example below but it will get the concept though

table1: empId, name, surname, etc
table2: salaryid, empId, salaryAmount, SalaryDate.


What I need:

I need a query to find the 2nd and 3rd latest salary amount with the name and surname of the emp in the same row per employee.

select * from
(
select *
from t1
join t2 on empid=empid
order by salarydate desc limit 2))

 

[Baxteen]

select * from
(
select *
from t1
join t2 on empid=empid
order by salarydate desc limit 2))

problem with that way around is, if the salary dates are not in order.
if the last 3 are in order, then that works fine.
if they are not in order you might get a result from a year ago or something silly like that.

using the datediff function is more reliable, because even if the data is bad, the difference in date between the date the salary was issued, and the date the query was run will always be X months.

my query will have trouble if salaries are issued weekly and monthly in the same division though.

I think for that case your solution with a double order is better, order the data correctly, then get the top 3 oldest of the top 3 first, then strip out the ones you want.

mayeb the most robust solution is the comprimize between both of ours, doubled ordered, select in select?

 

[Nerfherder]

Select top 3 name, surname from table1 t1
join table2 t2 on t1.empId = t2.empId

order by salaryAmount

 

[Batista]

problem with that way around is, if the salary dates are not in order.
if the last 3 are in order, then that works fine.
if they are not in order you might get a result from a year ago or something silly like that.

using the datediff function is more reliable, because even if the data is bad, the difference in date between the date the salary was issued, and the date the query was run will always be X months.

my query will have trouble if salaries are issued weekly and monthly in the same division though.

I think for that case your solution with a double order is better, order the data correctly, then get the top 3 oldest of the top 3 first, then strip out the ones you want.

mayeb the most robust solution is the comprimize between both of ours, doubled ordered, select in select?

I have purposefully left out the salary amount calcs.That sql statement is 90% done. Now figure out the rest

EDIT : You are almost there, just keep on trying

 

[zippy]

this works for Oracle. Whatever your db, im sure there is some similar function to dense_rank. ms sql also supports dense_rank and rank functions.

basically the trick to merge 2 rows is make them 2 sets of data and then join them using the key(empid)

select second_latest.empid, second_latest.name, second_latest.surname, second_latest.salaryamount second_latest_salary, second_latest.salarydate second_latest_date,
       third_latest.salaryamount third_latest_salary, third_latest.salarydate third_latest_date
from (select a.empid, a.name, a.surname, a.salaryamount, a.salarydate
from (select t1.empid, t1.name, t1.surname, t2.salaryamount, t2.salarydate, 
           dense_rank() over (partition by t1.empid 
                         order by t2.salarydate desc) pldr
from table1 t1, table2 t2
where t2.empid = t1.empid ) a
where a.pldr=2) second_latest ,
(select a.empid, a.name, a.surname, a.salaryamount, a.salarydate
from (select t1.empid, t1.name, t1.surname, t2.salaryamount, t2.salarydate, 
           dense_rank() over (partition by t1.empid 
                         order by t2.salarydate desc) pldr
from table1 t1, table2 t2
where t2.empid = t1.empid ) a
where a.pldr=3) third_latest
where third_latest.empid = second_latest.empid
;

to explain this:

There are 2 sets
Each uses :

dense_rank() over (partition by t1.empid order by t2.salarydate desc) pldr

The "order by t2.salarydate desc" is crucial here.

Within each set, pldr will be the "row number". Therefore 2 is the second latest and 3 is third latest

then you limit each set by saying pldr =2 or pldr = 3

set 1 is aliased : second_latest
set 2 is aliased : third_latest

Because they are now separate sets. You can join them using the empid and they appear on the same row for each employee