Nearest data in table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nearest data in table

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-07 : 05:55:22

I have a following tables :

ID------Name -------Salary
1---------A----------100
2---------B----------5000
3---------C----------9000
4---------D----------4000
5---------E----------6000
.......
1000 ----X----------4000

I offer average salary about 5000$
How can i find person which have nearest salarys with average salary
Expected output 1:
4--------D-------4000$
1000----X-------4000$

Expected output 2:
5--------E---------6000$

Thank you very much.

Sachin.Nand

2937 Posts

Posted - 2010-03-07 : 06:55:53

Do u want salary to be +- 1000 of the average salary?

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-07 : 07:04:06

[code]SELECT TOP(1) WITH TIES
s.*
FROM Table1 AS s
CROSS JOIN (
SELECT AVG(Salary) AS Salary
FROM Table1
) AS w
ORDER BY ABS(w.Salary - w.Salary)[/code]

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-07 : 07:15:29

w.Salary - w.Salary
Is that correct?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 08:25:45

quote:
Originally posted by webfred

w.Salary - w.Salary
Is that correct?

No, you're never too old to Yak'n'Roll if you're too young to die.

Nope it should be

w.Salary - s.Salary

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-07 : 16:47:47

[code]declare @t table
(ID int IDENTITY(1,1),
Name char(1),
Salary int);

INSERT @t VALUES
('A',100),
('B',5000),
('C',9000),
('D',4000),
('E',6000),
('X',4000);

--Expected output 0
SELECT ID, Name, Salary
FROM (SELECT *,DENSE_RANK() OVER(ORDER BY Rank_ID) AS [Rank]
FROM (SELECT *,
Rank_ID = ABS(AVG(Salary) OVER() - Salary)
FROM @t) D) D1
WHERE [Rank] = 1;
/*
ID Name Salary
----------- ---- -----------
2 B 5000
*/

--Expected output 1
SELECT ID, Name, Salary
FROM (SELECT *,DENSE_RANK() OVER(ORDER BY Rank_ID) AS [Rank]
FROM (SELECT *,
Rank_ID = ABS(AVG(Salary) OVER() - Salary)
FROM @t) D) D1
WHERE [Rank] = 2;
/*
ID Name Salary
----------- ---- -----------
4 D 4000
6 X 4000
*/

--Expected output 2
SELECT ID, Name, Salary
FROM (SELECT *,DENSE_RANK() OVER(ORDER BY Rank_ID) AS [Rank]
FROM (SELECT *,
Rank_ID = ABS(AVG(Salary) OVER() - Salary)
FROM @t) D) D1
WHERE [Rank] = 3;
/*
ID Name Salary
----------- ---- -----------
5 E 6000
*/
[/code]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-07 : 22:53:03

quote:
Originally posted by pamyral_279

I have a following tables :

ID------Name -------Salary
1---------A----------100
2---------B----------5000
3---------C----------9000
4---------D----------4000
5---------E----------6000
.......
1000 ----X----------4000

I offer average salary about 5000$
How can i find person which have nearest salarys with average salary
Expected output 1:
4--------D-------4000$
1000----X-------4000$

Expected output 2:
5--------E---------6000$

Thank you very much.

I'm sorry for not explain clearly !
My idea is if i offer salary 5000$

I will get name who is D,X
4--------D-------4000$
1000----X-------4000$
5--------E---------6000$

if i offer salary 300$
i will get name who is A
1---------A----------100

I will select all records which near salary which i offer !
offer salary can be changed which i wish !

Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-07 : 23:27:42

[code]
declare @OfferSalary int

select @OfferSalary= 5000

SELECT ID, Name, Salary
FROM
(
SELECT *,
row_no = DENSE_RANK() OVER(ORDER BY abs(Salary - @OfferSalary))
FROM yourtable
where Salary <> @OfferSalary
) s
WHERE row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-08 : 02:14:15

quote:

Originally posted by khtan


declare @OfferSalary int

select	@OfferSalary= 5000

SELECT 	ID, Name, Salary
FROM 	
(
	SELECT 	*, 
		row_no	= DENSE_RANK() OVER(ORDER BY abs(Salary - @OfferSalary))
  	FROM 	yourtable
	where	Salary	<> @OfferSalary
) s
 WHERE row_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

Sorry may be my bad explain !
my input :
ID----Name---Salary
1------ A------ 3
2------ B------ 6
3------ C------ 9
4------ D------ 12
5------ E------ 15
6------ F------ 18
7------ G------ 21
8------ H------ 23
9------ K------ 9

If offersalary is 10 i need output :
ID-----Name---Salary
3------C--------9
9------K--------9
4------D--------12

If offersalary is 18 i need output :
ID-----Name---Salary
1------F--------18
Because offer salary match exactly data in table.

If offersalary is 2 i need output :
ID-----Name---Salary
1------A--------3
It's only one record because no lower bound ! one higher bound is 3.

Thank you very much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 03:23:04

You can make use of ms65g's query like this



declare @OfferSalary int

select	@OfferSalary= 10

IF exists(select * from @t where Salary=@OfferSalary)
	SELECT ID,Name,Salary FROM @t WHERE Salary=@OfferSalary
else
 
SELECT ID, Name, Salary
FROM (SELECT *,DENSE_RANK() OVER(ORDER BY abs(Salary - @OfferSalary)) AS [Rank]
          FROM (SELECT *, 
                       Rank_ID = ABS(AVG(Salary) OVER() - Salary)
                  FROM @t) D) D1
 WHERE [Rank] <= 2

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-08 : 03:52:12

[code]
declare @OfferSalary int

select @OfferSalary= 5000

SELECT ID, Name, Salary
FROM
(
SELECT *,
row_no = DENSE_RANK() OVER(ORDER BY abs(Salary - @OfferSalary))
FROM yourtable
~~WHERE Salary <> @OfferSalary~~
) s
WHERE row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 09:37:18

[code]SELECT TOP 1 WITH TIES ID, Name, Salary
FROM YourTable
ORDER BY ABS(Salary-@InputValue) ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 09:53:20

quote:
Originally posted by visakh16

SELECT TOP 1 WITH TIES ID, Name, Salary
FROM YourTable
ORDER BY ABS(Salary-@InputValue) ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If there is no direct match OP needs nearest minimum as well as maximum

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:41:25

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

SELECT TOP 1 WITH TIES ID, Name, Salary
FROM YourTable
ORDER BY ABS(Salary-@InputValue) ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If there is no direct match OP needs nearest minimum as well as maximum

Madhivanan

Failing to plan is Planning to fail

Oops missed that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-08 : 11:48:36

quote:

Originally posted by khtan


declare @OfferSalary int

select	@OfferSalary= 5000

SELECT 	ID, Name, Salary
FROM 	
(
	SELECT 	*, 
		row_no	= DENSE_RANK() OVER(ORDER BY abs(Salary - @OfferSalary))
  	FROM 	yourtable
	WHERE	Salary	<> @OfferSalary
) s
 WHERE row_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

The first thing i want to say to thank madhivanan,visakh16 and khtan very much.
Khtan's statement is perfect in my problem.I want to ask more,example i want to select top 5 nearest as your guide ? how can i do ?

I need to 2 statements runs individual :

Input :
1 A 3
3 C 7
4 D 7
5 E 12
6 F 17
7 G 18
8 H 18
9 K 9

First statement get lower bound - offer salary is 10
Expect output1 (top 5)
9 K 9
3 C 7
4 D 7
1 A 3

Second statement get higher bound - offer salary is 10
Expect output2 (top 5)
5 E 12
6 F 17
7 G 18
8 H 18

Can you help me ! thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:54:57

you tell top 5 but you're showing only 4 values, why is that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-08 : 11:58:32

quote:
Originally posted by visakh16

you tell top 5 but you're showing only 4 values, why is that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If my data is only 4 records which suitable ! i will show that records,if in my data is enough 5 records, i will show 5 records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 12:09:58

[code]SELECT ID, Name, Salary
FROM
(
SELECT ID, Name, Salary,CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 'E' ELSE 'U' END AS CAt,
ROW_NUMBER() OVER(PARTITION BY CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 'E' ELSE 'U' END ORDER BY abs(Salary - @OfferSalary)) AS Seq
)t
WHERE Seq < = @N
AND Cat=@Cat
[/code]

now pass @Cat as L or E or U for lower, equal or upper bound results respectively
and pass a value for @N to get desired number of results like 5,3,...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-08 : 21:14:51

quote:
Originally posted by visakh16

SELECT ID, Name, Salary
FROM
(
SELECT ID, Name, Salary,CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 'E' ELSE 'U' END AS CAt,
ROW_NUMBER() OVER(PARTITION BY CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 'E' ELSE 'U' END ORDER BY abs(Salary - @OfferSalary)) AS Seq
)t
WHERE Seq < = @N
AND Cat=@Cat

now pass @Cat as L or E or U for lower, equal or upper bound results respectively
and pass a value for @N to get desired number of results like 5,3,...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry your statement is wrong !
i get Incorrect syntax near 'E' when run your statement.
Can you edit ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-08 : 21:56:54

just a missing THEN


SELECT ID, Name, Salary
FROM
(
SELECT ID, Name, Salary,CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 THEN'E' ELSE 'U' END AS CAt,
ROW_NUMBER() OVER(PARTITION BY CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 THEN 'E' ELSE 'U' END ORDER BY abs(Salary - @OfferSalary)) AS Seq
)t
WHERE Seq < = @N
AND Cat=@Cat

KH
[spoiler]Time is always against us[/spoiler]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-08 : 23:36:57

quote:

Originally posted by khtan

just a missing THEN


SELECT ID, Name, Salary
FROM
(
SELECT ID, Name, Salary,CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 THEN'E' ELSE 'U' END AS CAt,
ROW_NUMBER() OVER(PARTITION BY CASE WHEN Salary - @OfferSalary< 0 THEN 'L' WHEN Salary - @OfferSalary= 0 THEN 'E' ELSE 'U' END ORDER BY abs(Salary - @OfferSalary)) AS Seq
)t
WHERE Seq < = @N
AND Cat=@Cat

KH
[spoiler]Time is always against us[/spoiler]

I don't find where to insert my table !!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources