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 2012 Forums

Transact-SQL (2012)

query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-12-13 : 08:46:43

i have table like this


OrderNo  Group    Quantity
1         M       100
1         F       100
1         G       100
1         E       100
2         A       300
2         C       200 
3         E       400

i want to get this, that mean if i recognize the orderNo ,the first row show the Quantity and all rows of this orderNo will show 0.


OrderNo  Group    Quantity
1         M       100
1         F       0
1         G       0
1         E       0
2         A       300
2         C       0
3         E       400

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-13 : 10:05:17

for OrderNo 1, what makes you decide to show Quantity for Group M rather than the other ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-14 : 00:19:16

if i recognize the orderNo
how do you recognize it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jethrow
Starting Member

37 Posts

Posted - 2013-12-14 : 04:02:34

... assuming inbs is just using the first Group encountered for each OrderNo when Ordered By OrderNo ...

WITH	t AS (
	SELECT	*, Row_Number() OVER (ORDER BY OrderNo) rn
	FROM	Orders )
SELECT	t1.OrderNo,
	t1.[Group],
	CASE
		WHEN t1.OrderNo = t2.OrderNo THEN 0
		Else t1.Quantity
	END AS Quantity
FROM	t t1
LEFT	JOIN t t2
  ON	t1.rn = t2.rn+1

EDIT: ... yeah, partition by & where rn = 1 is a much better idea than left joining - see below

Microsoft SQL Server Noobie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-14 : 07:41:02

quote:
Originally posted by jethrow

... assuming inbs is just using the first Group encountered for each OrderNo when Ordered By OrderNo ...

WITH t AS (
SELECT *, Row_Number() OVER (ORDER BY OrderNo) rn
FROM Orders )
SELECT t1.OrderNo,
t1.[Group],
CASE
WHEN t1.OrderNo = t2.OrderNo THEN 0
Else t1.Quantity
END AS Quantity
FROM t t1
LEFT JOIN t t2
ON t1.rn = t2.rn+1

Microsoft SQL Server Noobie

For that this is enough


SELECT OrderNo,Group,CASE WHEN Rn = 1 THEN Quantity ELSE 0 END AS Quantity
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY OrderNo) AS Rn, *
FROM Table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources