SQL SEREVR QUERY
-
I HAVE A TABLE called TBLGENDER LIKE BELOW
IN THIS TABLE
2 people getting 8000,2 people getting 5500,2 people getting 3500 AND 4 people getting 5000
ID NAME GENDER SALARY CITY
1 SHIVA MALE 8000 LONDON
2 MAMATHA FEMALE 6000 SYDNEY
3 NIHAL MALE 5000 DELHI
4 ROHAN MALE 3500 DELHI
5 PANDA FEMALE 3500 LONDON
6 SIMON MALE 5500 LONDON
7 MARY FEMALE 5500 SYDNEY
8 JOHN MALE 4000 DELHI
9 MARIA FEMALE 5000 LONDON
10 ROSY FEMALE 5000 LONDON
11 MIKE MALE 5000 SYDNEY
12 JESSY FEMALE 8000 LONDON
13 SHAN MALE 3700 SYDNEY
14 JASON MALE 4800 LONDONWHAT IAM TRYING IS I WANT TO DISPLAY NAME,SALARY AND THEIR [SALARY RANK]
THE ABOVE 10 PEOPLE WHO IS GETTING SAME SALARIES
HERE IS MY ATTEMPT
WITH SALTEST ( SALARY,No_Of_People,D_RANK)
AS
(
select SALARY,COUNT(SALARY) AS No_Of_People,
DENSE_RANK() OVER(ORDER BY SALARY DESC) AS D_RANK
from TblGender GROUP BY SALARY)
SELECT * from SALTEST where No_Of_People>1I TRIED MANY WAYS TO ADD NAME BUT I DIDNOT GET IT
I WANT TO DISPLAY NAME,SALARY AND THEIR [SALARY RANK]
PLEASE HELP
- This topic was modified 5 years, 4 months ago by Steve Jones - SSC Editor. Reason: Formatted code
-
First of all, there is no need to shout ... please consider using some lowercase letter in future.
Secondly, please provide your data in a consumable format (one which can be pasted and run inside of SSMS in the form of a temp table with suitable INSERT statements)
Finally, please provide your expected results based on the sample data provided. Not just some textual commentary.
If you take the time to do this, you will probably have a working solution within minutes.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Secondly, do you realise that your table thoroughly violates relational database principles?
A Gender table should contain, erm, genders and only genders.
A City table would contain your cities.
A Person/Customer table would contain names.
You get the idea.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Also, please show the results you expect, or some of them. Format this as plan code, using the code sample button in the toolbar.
-
Hi Phil and Steve I am very sorry about my font size.
my expected result like this
Name Salary Salary_rank
Shiva 8000 1
Jessy 8000 1
Sion 5500 2
mary 5500 2
Nihal 5000 3
so on......
-
>> I have a table called tblgender like below <<
We need to fix a few things before you get anywhere. You're supposed post DDL so we don't have to guess about keys, datatypes, references, check constraints or other things that make it possible to answer your question. You expect us to simply design a schema for you! The name of this non-table is also wrong; the prefix "TBL_" is a design flaw called a tibble. You have mixed data and metadata in a data element name and that's a big no-no. In the second part of the name, "gender", is not the name of the set but the name of an attribute. You don't understand the difference between tables, attributes and their values. You also don't understand their ISO standards for the sex code that you completely failed to use in your narrative. You might want to look it up and learn some of the standards of your industry. There's also no such thing as a generic "id", a generic universal "name" and so forth. You really need to get a book on basic data base design. Here is my guess at a possible schema for you
CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL PRIMARY KEY, --- not an option
emp_name VARCHAR(25) NOT NULL,
emp_sex_code CHAR(1) DEFAULT '0' NOT NULL
CHECK(emp_sex_code IN ('0', '1', '2', '9');
salary_amt DECIMAL (10,2) NOT NULL
CHECK (salary_amt >= 0.00),
emp_city_name VARCHAR(20) NOT NULL); --- why is a city name a permanent attribute of an employee?
Please post DDL and follow ANSI/ISO standards when asking for help.
-
have you looked at windowing functions? RANK()? DENSE_RANK()?
-
Please consider all the advice given here by others.
That being said, you were very close.
with SalTest as
( select
-- Putting * here includes all fields including Name
*,
-- If you want the number of people with the
-- same salary, you need to add Partition by
COUNT(*) over (partition by Salary )as No_of_People,
-- You got the Rank part right
DENSE_RANK() over ( order by Salary desc ) as D_RANK
from Employee )
select * from SalTest where No_of_People > 1; - shiva996 wrote:
Hi Phil and Steve I am very sorry about my font size.
my expected result like this
Name Salary Salary_rank
Shiva 8000 1
Jessy 8000 1
Sion 5500 2
mary 5500 2
Nihal 5000 3
so on......
with TblGender as
(
SELECT *
FROM (VALUES (1, 'SHIVA', 'MALE', '8000', 'LONDON'),
(2, 'MAMATHA', 'FEMALE', '6000', 'SYDNEY'),
(3, 'NIHAL', 'MALE', '5000', 'DELHI'),
(4, 'ROHAN', 'MALE', '3500', 'DELHI'),
(5, 'PANDA', 'FEMALE', '3500', 'LONDON'),
(6, 'SIMON', 'MALE', '5500', 'LONDON'),
(7, 'MARY', 'FEMALE', '5500', 'SYDNEY'),
(8, 'JOHN', 'MALE', '4000', 'DELHI'),
(9, 'MARIA', 'FEMALE', '5000', 'LONDON'),
(10, 'ROSY', 'FEMALE', '5000', 'LONDON'),
(11, 'MIKE', 'MALE', '5000', 'SYDNEY'),
(12, 'JESSY', 'FEMALE', '8000', 'LONDON'),
(13, 'SHAN', 'MALE', '3700', 'SYDNEY'),
(14, 'JASON', 'MALE', '4800', 'LONDON')
)T(ID, NAME, GENDER, SALARY, CITY)
)
SELECT Name,
Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) Salary_rank
FROM TblGender
-
Hi Roberts
Thank you very much for your explanation now I am very Clear
Thank you every one who given to me great valuable Ideas
Thank you
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply