The above result give me minimum salary but total number of JOB holders, You can see only one SALEMAN getting 1000 but count show total number of SALESMAN. Similarly 3 MANAGERS are getting minimum and same salary but count show total number of MANAGERS.
My question is how can i get number of person on min salary?
doc_id 100 was excluded because it has a higher version which was shown instead.
So how would i express this is oracle sql.
edit: Just thought i should say that I have been tring to crack this for 2 days now :P I have been messing around with MAX() but I cant make it do what i want.
I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score John ==== 200* Zohaib ==== 299 Ali ==== 0* John ==== 200 Maria ==== 150* Ali ==== 0 Maria ==== absent John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score Zohaib ==== 299 John ==== 200* Maria ==== 150* Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example 10,20,25 have a highest common factor of 5.
SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL); SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);
both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.
A.Create object to get the salary hike of an employee on the basis of completion of years in an organization.
Conditions:- Create two tables with name as "Employee_yourname" and "Employee_Hike_Yourname". "Employee_yourname" can have columns like EMPID, NAME, SALARY, DATE_OF_JOIN. "Employee_Hike_Yourname" will have 3 more columns for new salary, Current salary and completed period in organization. Consider the employees who have completed minimum one year in organization. Salary hike for employee with tenure >= 1yr and < 2yrs should be 10% of current salary. If tenure is greater than or equal to two years and less than 3 years then salary hike should be 20% of current salary. For employees having tenure more than 3 years hike should be 30% % of current salary
I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY ACCOUNTING 8750 RESEARCH 10875 SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
I have a created data block using employees table so am trying to validate item salary using max_salary and min_salary from table jobs how to do this kind of validation.
[How can i validate input number into a Field of type char in oracle form?]
I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary) from employee e join department d on e.deptno=d.deptno where d.deptname=upper('finance') group by d.deptno, d.deptname; [code]...
I need to obtain the top 2 departments in terms of total salary.
I have the following tables (Dno corresponds to DeptNo). I've found tutorials for obtaining the salaries of the top three employees, and summing the salaries for each dept. is easy, but I can't seem to combine the two.
DNo Fname Salary 2 Tom 10000 3 Mike 20000 2 Harry 30000
DeptNo DeptName 1 Administration 2 Special 3 Finance
How do i check in Table B whether it is converted correctly into words taking input or reference from table A
Consider below example:-
Table A Table B $125 Dollar One Hundred twenty Five only $45,542 Dollar Forty Five Thousand Five Forty Two Only $145.56 Dollar One Forty Five and fifty six cents Only $145,253 $35,256.65 $560,250.67
/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */ CREATE PROCEDURE p_get_name ( p_empno IN OUT NUMBER, p_name OUT VARCHAR2, p_err OUT NUMBER [code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
I have a table with 50 columns, lets call it Table_A . There is a identical table called Table_B. The data in Table_B gets flushed every night and data from Table_A is moved to Table_B. A new set of data is inserted into Table_A. Now I need to find out which field value is changed and what is changed . A minus command can give the changed records, but how to get the what was prior value for each changed field . Here is what I am looking for
WITH TABLE_A AS ( SELECT 1 ID, 'JOHN' NAME, 'SALES' DIV FROM DUAL UNION SELECT 2 ID, 'MARRY' NAME, 'ACCOUNTS' DIV FROM DUAL UNION SELECT 3 ID, 'KIM' NAME, 'SERVICE' DIV FROM DUAL),
[code]...
what i want is something that will spit out this (only one record, because there is only one diff record found in the above query.)
ID, OLD_ID, NAME, OLD_NAME , DIV , OLD_DIV -- ------ ---- -------- --- ------- 3 3 KIM KIM SERVICE SALESThe above one is for sample purpose, The actual table has 50 fields, and except the ID field, all other field values might change.
i need more important to know about this query,below query in where clause i have one condition like 'pk1_value =New_Deliveries.DELIVERY_ID" but in my New_Deliveries table i don't have pk1_value column,how to find that column.
i m create a table emp . all user of database have privileged to read and write the emp table. now How can identify that which user have insert row in emp file ?