• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

How to get related rows

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

Open in new window

0
brettr
Asked:
brettr
  • 6
  • 5
2 Solutions
 
BinuthCommented:

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)

Open in new window

0
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
brettrAuthor Commented:
Thanks.  Is one better than the other in regards to performance?
0
 
brettrAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
brettrAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
brettrAuthor Commented:
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
 
SharathData EngineerCommented:
So whats the conclusion? Do you think you want another solution for this kind of scenario?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now