Need help with query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with query

Author

Topic

JMAIL
Starting Member

2 Posts

Posted - 2014-01-21 : 12:20:34

I have following situation:

table_1
pid discount
1 8
2 7
3 9

table_2
pid name price
1 aa 11
1 aa 12
1 aa 13
1 aa 10
2 bb 22
2 bb 33
3 cc 66

I need a query which will generate this result:

pid name price discount
1 aa 11 8
1 aa 12 0
1 aa 13 0
1 aa 10 0
2 bb 22 7
2 bb 33 0
3 cc 66 9

any help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-21 : 13:46:58

Could you explain words the result set?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-21 : 14:05:46

I know I shouldn't guess, but I'm gunna anyway.. :)

DECLARE @table_1 TABLE (pid int, discount int)

INSERT @table_1 VALUES
(1, 8),
(2, 7),
(3, 9)

DECLARE @table_2 TABLE (pid int, name varchar(20), price int)

INSERT @table_2 VALUES
(1, 'aa', 11), 
(1, 'aa', 12), 
(1, 'aa', 13), 
(1, 'aa', 10), 
(2, 'bb', 22), 
(2, 'bb', 33), 
(3, 'cc', 66)


SELECT
	pid,
	name,
	Price,
	CASE WHEN RowNum = 1 THEN discount ELSE 0 END AS discount
FROM
	(
		SELECT 
			B.pid,
			B.name,
			B.Price,
			A.Discount,
			ROW_NUMBER() OVER (PARTITION BY B.pid ORDER BY price DESC) AS RowNum
		FROM
			@table_1 AS A
		INNER JOIN
			@table_2 AS B
			ON A.pid = B.pid
	) AS T

JMAIL
Starting Member

2 Posts

Posted - 2014-01-21 : 18:26:37

Thanks Lamprey, you just save my day.

quote:

Originally posted by Lamprey

I know I shouldn't guess, but I'm gunna anyway.. :)

DECLARE @table_1 TABLE (pid int, discount int)

INSERT @table_1 VALUES
(1, 8),
(2, 7),
(3, 9)

DECLARE @table_2 TABLE (pid int, name varchar(20), price int)

INSERT @table_2 VALUES
(1, 'aa', 11), 
(1, 'aa', 12), 
(1, 'aa', 13), 
(1, 'aa', 10), 
(2, 'bb', 22), 
(2, 'bb', 33), 
(3, 'cc', 66)


SELECT
	pid,
	name,
	Price,
	CASE WHEN RowNum = 1 THEN discount ELSE 0 END AS discount
FROM
	(
		SELECT 
			B.pid,
			B.name,
			B.Price,
			A.Discount,
			ROW_NUMBER() OVER (PARTITION BY B.pid ORDER BY price DESC) AS RowNum
		FROM
			@table_1 AS A
		INNER JOIN
			@table_2 AS B
			ON A.pid = B.pid
	) AS T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources