SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Fatch last maximum non-zero values


How to Fatch last maximum non-zero values

Author
Message
Vineet Dubey
Vineet Dubey
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1411 Visits: 869
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146414 Visits: 13350
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Lange
Sean Lange
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: General Forum Members
Points: 149305 Visits: 18575
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.

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)
Vineet Dubey
Vineet Dubey
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1411 Visits: 869
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
Koen Verbeeck
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146414 Visits: 13350
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Lange
Sean Lange
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: General Forum Members
Points: 149305 Visits: 18575
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.

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)
hunchback
hunchback
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1457 Visits: 639
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
Koen Verbeeck
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146414 Visits: 13350
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Lange
Sean Lange
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: General Forum Members
Points: 149305 Visits: 18575
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.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search