How to Fatch last maximum non-zero values

Question

Post reply

How to Fatch last maximum non-zero values

vineet_dubey1975

SSCrazy

Points: 2306
More actions
August 29, 2013 at 12:34 pm

#277450

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
540D10012013-08-29 23:37:41.530
650D10012013-08-29 23:37:41.530
710D10012013-08-29 23:38:07.097
80D10012013-08-29 23:38:07.097
90D10012013-08-29 23:38:07.097
100D10012013-08-29 23:38:07.097
1180D10012013-08-29 23:38:07.097
120D10012013-08-29 23:38:07.097
1310D10012013-08-29 23:41:31.993
140D10022013-08-29 23:41:31.993
150D10022013-08-29 23:41:31.993
160D10022013-08-29 23:41:31.993
1730D10022013-08-29 23:41:31.993
1840D10022013-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

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 3

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

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;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Hunchback SSCrazy Points: 2649 More actions · Answer 6

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;

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

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).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/