3 tables - group by and inner join with

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

SQL Server 2005 Forums

Transact-SQL (2005)

3 tables - group by and inner join with

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-21 : 16:25:24

I have the following 3 tables.
Im trying to write the following SP:

For each sale from the SALES table im trying to find the highest
price from the PRICES table and get addtional details about the user (of that price) from the Users table + Title of the sale from the SALES table.

		
Sales Table
------------------	
SaleID Title
1      sale1
2      sale2
3      sale3


Prices Table
------------------	
PriceID SaleID Price Username
1	1      95.5  User1
2	1      96.5  User2
3       3      96.5  User1
4       3      55.5  User1
5       2      44.4  User3
6	1      87.1  User3
7       1      80.0  User1
	
Users Table
------------------
Username Name
User1    Mike
User2    Joe
User3    Ross

Expected Output:
--------------------------
SaleID Title  Price Username Name
1      sale1  96.5  User2    Joe
2      sale2  44.4  User3    Ross
3      sale3  96.5  User1    Mike

i wrote the following query, and i managed to get the highest price for each sale with the sale title but i dont know how to get the user details.

select s.SaleID , s.Title , p.high
from Sales s
inner join
(
SELECT SaleID , MAX(Price) as high
FROM Prices AS p
group by SaleID
) p
on s.SaleID = p.SaleID

Thank you for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 16:33:23

We have showed you how to use ROW_NUMBER() function before.

SELECT		s.SaleID,
		s.Title,
		p.Price,
		u.UserName,
		u.Name
FROM		Sales AS S
INNER JOIN	(
			SELECT	SaleID,
				Price,
				UserName,
				ROW_NUMBER() OVER (PARTITION BY SaleID ORDER BY Price DESC) AS recID
			FROM	Prices
		) AS p ON p.SaleID = s.SaleID
			AND p.recID = 1
INNER JOIN	Users AS u ON u.UserName = p.UserName

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

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-22 : 09:53:00

Yes i know you showed me how to use ROW_NUMBER() , i guess i was fixed on using GROUP BY in this case.

Anyway ,Thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 10:52:58

Also


SELECT s.SaleID, 
s.Title,
t.Price,
t.Username,
t.Name
FROM Sales s
CROSS APPLY (SELECT TOP 1 p.Price, p.Username, u.Name
             FROM Prices p
             INNER JOIN Users u
             ON u.Username=p.Username
             WHERE p.SaleID=s.SaleID
             ORDER BY p.Price DESC)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources