Click here to monitor SSC
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_dubey1975
vineet_dubey1975
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 Visits: 651
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16546 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16675 Visits: 17035
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)
vineet_dubey1975
vineet_dubey1975
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 Visits: 651
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16546 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16675 Visits: 17035
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)
hunchback
hunchback
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16546 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16675 Visits: 17035
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)
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