To find Lowest Experience of any Employee by writing a query in GRID DB

Question

I am working on a EmployeeDetails table. I have 4 columns in that table(Empid,Empname, DateOfJoining, Salary). I want to write a query to fetch the Empid,Empname and salary of the employee who is having Lowest Experience. Can someone please assist me. The query should be written in GRID DB

I have tried this query using a DateADD() function.

Answer 1

I don't think that you need date functions here. As I understand your question, we can just select all employees, order by date of joining and limit:

select empid, empname, salary
from employeeDetails
order by dateOfJoining limit 1

Note that this does not handle ties (if any) in a predictable way. A simple solution is to add another sort criteria to make the results stable:

 order by dateOfJoining, empid limit 1

If you really want the query to return all ties, then we can use window functions instead:

select empid, empname, salary
from (
    select e.*, rank() over(order by dateOfJoining) rn
    from employeeDetails e
) e
where rn = 1

Answer 2

I am assuming you are using GridDB SQL. Then, you need to use the below 5 Clauses:

a. TIMESTAMP_DIFF function in GridDB - to compare the two timestamps/dates

TIMESTAMP_DIFF(time_unit, timestamp1, timestamp2 [, timezone])

where time_unit can be YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND

b. TIMESTAMP function - to convert string dates to timestamp in griddb

TIMESTAMP(timestamp_string [, timezone])

c. NOW() function - to get the current date timestamp in griddb

NOW()

d. LIMIT function - to limit the number of records

LIMIT __By-Number__

e. ORDER BY function - to order the number of rows in Ascending or descending order

ORDER BY ASC/DESC

---

I am getting the experience of the employees by DAY (by comparing it with the current date timestamp), but you can do it for YEAR or MONTH by replacing DAY in the below query. I assume the "DateOfJoining" column is of type VARCHAR in the table, meaning it needs to be converted to Timestamp. So, the final query would look like this:

SELECT Empid, Empname, Salary , TIMESTAMP_DIFF(DAY, NOW(),TIMESTAMP(DateOfJoining)) as exper_by_days from employee order by exper_by_days;

You can limit the number of records by displaying only the top 5 rows with lowest experience using the below query:

SELECT Empid, Empname, Salary , TIMESTAMP_DIFF(DAY, NOW(),TIMESTAMP(DateOfJoining)) as exper_by_days from employee order by exper_by_days LIMIT 5;