Solved

# How to get related rows

Posted on 2008-11-07
157 Views
Using the query below, In addition to nearest price, I'd like to get all rows that have the same date.  The data might look like:

ID  tDate         price
1  11/1/2008   5.00
2  11/1/2008   7.50
3  11/3/2008   15.00
4  11/3/2008   14.00

If I run the query and substitute 6.00 for 100.00, I want to get row IDs 1 & 2.   1 because it matches the nearest price and 2 because it has the same date.  There could be several other rows with the same date.  What query will produce those types of results?
``````select top 1 price from

(

select MAX(price) as price,(100.00 - max(price)) AS Diff from TableName where price <= 100.00

UNION ALL

select MIN(price) as price,(MIN(price)-100.00) AS Diff from TableName where price >= 100.00

) test

order by Diff asc
``````
0
Question by:brettr

LVL 14

Accepted Solution

``````select B.* from TableName A

inner join TableName B ON A.tDate = B.tDate

where

A.price =(

select top 1 price from

(

select MAX(price) as price,(13 - max(price)) AS Diff from TableName where price <= 13

UNION ALL

select MIN(price) as price,(MIN(price)-13) AS Diff from TableName where price >= 13

) test

order by Diff asc)
``````
0

LVL 40

Expert Comment

Check this:

select a.id,a.tDate,a.price from tablename a
join
(select top 1 price from
(
select MAX(price) as price,(6.00 - max(price)) AS Diff from TableName where price <= 100.00
UNION ALL
select MIN(price) as price,(MIN(price)-6.00) AS Diff from TableName where price >= 100.00
) test
order by Diff asc) b
on a.price = b.price
join tablename c
on a.tDate = c.tDate
0

LVL 40

Assisted Solution

Updated everywhere 6.00

select a.id,a.tDate,a.price from tablename a
join
(select top 1 price from
(
select MAX(price) as price,(6.00 - max(price)) AS Diff from TableName where price <= 6.00
UNION ALL
select MIN(price) as price,(MIN(price)-6.00) AS Diff from TableName where price >= 6.00
) test
order by Diff asc) b
on a.price = b.price
join tablename c
on a.tDate = c.tDate
0

Author Closing Comment

Thanks.  Is one better than the other in regards to performance?
0

Author Comment

Actually, I was to quick on assigning solutions since Binuth's only gets prices equal to or less than and Sharath_123 doesn't return anything.
0

LVL 40

Expert Comment

Assume that you have data like below.
ID  tDate         price
1  11/1/2008   5.00
2  11/2/2008   7.00
3  11/1/2008   15.00
4  11/2/2008   14.00

Check whether Binuth's solution work for you. As per your requirement it should display the records with nearest prices. In this case records 1 and 2 both having nearest price (price 5 and 7). Then the records with same Dates (records having dates 11/1/2008 and 11/2/2208). In this case records 3 & 4.
The result set should be records 1,2,3 & 4.
Are you able to get this result set with Binuth's or anyone's solution here?
0

LVL 40

Expert Comment

brettr - dont jump on hitting accept button. Test any solution before doing that. because people generally check in the accepted solutions first if they encounter any problem. If they come to know that the solution provided is not working, thats not good for EE.
It may be bad impression that EE won't provide good solutions.
0

Author Comment

One or the other price will be chosen.  Say SQL Server chooses 5.  Then rows 1 & 3 should return.  If 7, then 2 & 4.

I'm able to get Binuth's solution going. The problem was more my modifications of trying to do a join with another table.  Thanks.
0

LVL 40

Expert Comment

My understanding is its not either 5 or 7. It should be both 5 and 7. The query should be written in such a way that it should return both 5 and 7 as both are nearest prices.
0

Author Comment

There is a problem with order by asc.  Since the UNION ALL returns tWo results, one may be NULL.  In this case the answer is NULL with a SELECT 1.  That's incorrect.  It should return the numeric from the other result.   You can get this by going with orderby desc.  However, now when you get numerics for both results, you send the wrong one on SELECT 1.
0

LVL 40

Expert Comment

So whats the conclusion? Do you think you want another solution for this kind of scenario?
0

Author Comment

0

## Featured Post

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durinâ€¦
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
This video discusses moving either the default database or any database to a new volume.