Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Fatch last maximum non-zero values Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 12:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:34 AM
Points: 373, Visits: 387
Hi All ,

I have a device table consists device reading data on the daily basis.

The schema of the table is

CREATE TABLE [dbo].[DeviceReading](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ReadNo] [int] NULL,
[DeviceNo] [nvarchar](50) NULL,
[DateCreated] [datetime] NULL
) ON [PRIMARY]
GO


Sample data is
ID ReadNo DeviceNo DateCreated
5 40 D1001 2013-08-29 23:37:41.530
6 50 D1001 2013-08-29 23:37:41.530
7 10 D1001 2013-08-29 23:38:07.097
8 0 D1001 2013-08-29 23:38:07.097
9 0 D1001 2013-08-29 23:38:07.097
10 0 D1001 2013-08-29 23:38:07.097
11 80 D1001 2013-08-29 23:38:07.097
12 0 D1001 2013-08-29 23:38:07.097
13 10 D1001 2013-08-29 23:41:31.993
14 0 D1002 2013-08-29 23:41:31.993
15 0 D1002 2013-08-29 23:41:31.993
16 0 D1002 2013-08-29 23:41:31.993
17 30 D1002 2013-08-29 23:41:31.993
18 40 D1002 2013-08-29 23:41:31.993

How do I get the Output - Last maximum non-zero value inserted in the table group by deviceNo

D1001 = 80
D1002 = 40

Please help !!!!
Thanks

Vineet Dubey
Post #1489839
Posted Thursday, August 29, 2013 12:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 AM
Points: 12,208, Visits: 9,174
Last maximum value is a bit contradictory.
You either want the last non-zero value, or you want the maximum value if it is non-zero.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489848
Posted Thursday, August 29, 2013 1:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:24 PM
Points: 11,990, Visits: 11,007
What you posted is not really sample data. It is text that sort of is like sample data.

It looks like you pretty much wrote the query in your explanation...group by deviceNo

select Max(ReadNo), deviceNo
from DeviceReading
group by deviceNo
having MAX(ReadNo) > 0



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489859
Posted Thursday, August 29, 2013 1:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:34 AM
Points: 373, Visits: 387
Hi Thanks for the response.

I want the last non-zero value for D1001 and D1002.. In the leg time device returns 0.

Vineet Dubey

Post #1489870
Posted Thursday, August 29, 2013 1:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 AM
Points: 12,208, Visits: 9,174
WITH CTE_NoZero AS
(SELECT ID, ReadNo, deviceNo FROM DeviceReading WHERE ReadNo <> 0)

SELECT ReadNo, deviceNo
FROM
(SELECT ReadNo, deviceNo, RID = ROW_NUMBER() OVER (PARTITION BY deviceNo ORDER BY ID DESC)
FROM CTE_NoZero) tmp
WHERE RID = 1;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489872
Posted Thursday, August 29, 2013 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:24 PM
Points: 11,990, Visits: 11,007
Koen Verbeeck (8/29/2013)
WITH CTE_NoZero AS
(SELECT ID, ReadNo, deviceNo FROM DeviceReading WHERE ReadNo <> 0)

SELECT ReadNo, deviceNo
FROM
(SELECT ReadNo, deviceNo, RID = ROW_NUMBER() OVER (PARTITION BY deviceNo ORDER BY ID DESC)
FROM CTE_NoZero) tmp
WHERE RID = 1;



Isn't that going to return the same thing as the version I posted?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489884
Posted Thursday, August 29, 2013 2:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:03 PM
Points: 99, Visits: 497
What version of SQL Server are you using?

I am not sure is you are looking for the last non-zero value or the maximum non-zero value. Anyway, filter the rows to get just rows with [ReadNo] greater than zero and then enumerate them in descendent order by DateCreated.

with R as (
select *, row_number() over(partition by DeviceNo order by DateCreated DESC) as rn
from T
where ReadNo > 0
)
select DeviceNo, ReadNo
from R
where rn = 1;



Post #1489891
Posted Thursday, August 29, 2013 2:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 AM
Points: 12,208, Visits: 9,174
Sean Lange (8/29/2013)
Koen Verbeeck (8/29/2013)
WITH CTE_NoZero AS
(SELECT ID, ReadNo, deviceNo FROM DeviceReading WHERE ReadNo <> 0)

SELECT ReadNo, deviceNo
FROM
(SELECT ReadNo, deviceNo, RID = ROW_NUMBER() OVER (PARTITION BY deviceNo ORDER BY ID DESC)
FROM CTE_NoZero) tmp
WHERE RID = 1;



Isn't that going to return the same thing as the version I posted?


For this sample data, yes. But what if ID 11 has 30 instead of 80. Your query would return ID 6 with 50 (maximum value), while mine would still return ID 11 with value 30 (last non-zero value).




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489892
Posted Thursday, August 29, 2013 2:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:24 PM
Points: 11,990, Visits: 11,007
Koen Verbeeck (8/29/2013)
Sean Lange (8/29/2013)
Koen Verbeeck (8/29/2013)
WITH CTE_NoZero AS
(SELECT ID, ReadNo, deviceNo FROM DeviceReading WHERE ReadNo <> 0)

SELECT ReadNo, deviceNo
FROM
(SELECT ReadNo, deviceNo, RID = ROW_NUMBER() OVER (PARTITION BY deviceNo ORDER BY ID DESC)
FROM CTE_NoZero) tmp
WHERE RID = 1;



Isn't that going to return the same thing as the version I posted?


For this sample data, yes. But what if ID 11 has 30 instead of 80. Your query would return ID 6 with 50 (maximum value), while mine would still return ID 11 with value 30 (last non-zero value).


Ahh I misinterpreted the last max non-zero value. I was thinking of the greatest value not the most recent one. Poorly worded questions get poorly written responses.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse