problems with where

Question

problems with where

Knallofall

SSC Journeyman

Points: 92
More actions
March 12, 2012 at 8:50 am

#265752

Hello,
I have problems figuring this out. What I want is a conditional where. If a column has a value greater than zero I want to select all the rows belonging to the same number. If the column has no value greater than zero I dont want these rows selected.
I have a table like this:
ID NUMBER VALUE
------------------------
1 1 0
2 1 1
3 1 0
4 2 0
5 2 0
In the example above the three rows with number of 1 would be selected because one of the rows has a value that is greater than zero.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Is it homework?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Knallofall SSC Journeyman Points: 92 More actions · Answer 2

Knallofall

SSC Journeyman

Points: 92

March 12, 2012 at 8:59 am

#1457916

haha no

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

declare @t table (ID int, VALUE int)

insert @t select 1, 0

insert @t select 1, 1

insert @t select 2, 0

select t1.*

from @t t1

join @t t2 on t2.ID = t1.ID

where t2.VALUE > 0

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Ant-Green SSC Guru Points: 113537 More actions · Answer 4

something like this

create table #temp (id int, number int, value int)

insert into #temp (id, number, value) values (1,1,0),(2,1,1),(3,1,0),(4,2,0),(5,2,0)

with cte1 as

(

select

number

from

#temp

where

value > 0

group by

number

)

select

#temp.*

from

#temp

join

cte1

on

#temp.number = cte1.number

Mark Cowne One Orange Chip Points: 26979 More actions · Answer 5

Another way

DECLARE @t TABLE (ID INT, NUMBER INT, VALUE INT)

INSERT INTO @t(ID,NUMBER,VALUE)

SELECT 1,1,0 UNION ALL

SELECT 2,1,1 UNION ALL

SELECT 3,1,0 UNION ALL

SELECT 4,2,0 UNION ALL

SELECT 5,2,0;

WITH CTE AS (

SELECT ID,NUMBER,VALUE,

MAX(VALUE) OVER(PARTITION BY NUMBER) AS maxVal

FROM @t)

SELECT ID,NUMBER,VALUE

FROM CTE

WHERE maxVal>0;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Knallofall SSC Journeyman Points: 92 More actions · Answer 6

Hello,

Thanks for this!

Problem is that I have a inner join on number on another table. Can this solution still be applied?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

Yes, it can.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Knallofall SSC Journeyman Points: 92 More actions · Answer 8

Ok great, it worked with the inner join. Here is the full query.

Thanks again!

USE [navtables]

GO

/****** Object: StoredProcedure [dbo].[RS_OrderStock] Script Date: 03/12/2012 12:36:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[RS_OrderStock]

AS

SELECT

SH.[No_] AS 'Ordernr.',

SIL.[Document_Type] as 'saleslinetyp',

SH.[Sell-to_Customer_No_] AS 'Förs.kundnr.',

SH.[Bill-to_Name] AS 'Kundnamn',

SH.[Order_Date] AS 'Orderdat.',

SH.[Shipment_Date] AS 'Lastdatum',

SH.[Requested_Delivery_Date] AS 'Beg. Lev.dat.',

SIL.[Promised_Delivery_Date] AS 'Lov. lev dat.',

SIL.[No_] AS 'Art.nr.',

SH.[Document_Type] AS 'DokTyp',

SIL.[Description] AS 'Beskr.',

SIL.[Location_Code] AS 'Lagerst.',

SIL.[Quantity] AS 'Antal',

SIL.[Unit_of_Measure] AS 'Enhet',

SIL.[Amount] AS 'Belopp',

SIL.[Unit_Price] AS 'A-pris',

SIL.[Line_Amount] AS 'Radbelopp',

SIL.[Qty__to_Ship] AS 'Ant. att utlev.',

SIL.[Quantity_Shipped] AS 'Utlev. ant.',

SIL.[Qty__to_Invoice] AS 'Ant. att fakt.',

SIL.[Shortcut_Dimension_1_Code] AS 'VG',

SH.[Salesperson_Code] AS 'SKod',

SIL.[Line_Discount_Amount] AS 'Rabatt SEK',

(CASE WHEN SIL.Qty__to_Invoice > 0 THEN (SIL.[Qty__to_Invoice] * SIL.[Unit_Price]) - ((SIL.[Line_Discount_Amount] / SIL.[Quantity])) * SIL.[Qty__to_Invoice] ELSE NULL END) as 'spec'

FROM Sales_Header SH

INNER JOIN Sales_Line SIL

ON SH.No_ = SIL.Document_No_

join Sales_Line SIL2 on SIL.Document_No_ = SIL2.Document_No_

WHERE SH.[Document_Type] = 'Order' AND SIL.[Type] = 'Item' AND SIL.[Document_Type] = 'Order' AND SIL2.Qty__to_Invoice > 0 --AND SH.[No_] IN ('28146', '28187')

GROUP BY SH.[No_] ,

SIL.[Document_Type] ,

SH.[Sell-to_Customer_No_],

SH.[Bill-to_Name],

SH.[Order_Date],

SH.[Shipment_Date],

SH.[Requested_Delivery_Date],

SIL.[Promised_Delivery_Date] ,

SIL.[No_],

SH.[Document_Type],

SIL.[Description],

SIL.[Location_Code],

SIL.[Quantity],

SIL.[Unit_of_Measure],

SIL.[Amount],

SIL.[Unit_Price],

SIL.[Line_Amount],

SIL.[Qty__to_Ship],

SIL.[Quantity_Shipped],

SIL.[Qty__to_Invoice],

SIL.[Shortcut_Dimension_1_Code],

SH.[Salesperson_Code],

SIL.[Line_Discount_Amount],

SIL.[Line_No_]

ORDER BY SH.[No_], SIL.[Line_No_], SH.[Order_Date]

problems with where

Cookies on SQLServerCentral