July 21, 2016 at 5:18 am
Hi Friends ,
input code :
CREATE TABLE #First_nonzero
(
value int
)
GO
INSERT INTO #First_nonzero VALUES
(0),
(0),
(0),
(0),
(1),
(2),
(0),
(3),
(0)
Expected_output :
1
2
0
3
0
please some one suggest me how can i do this ..?
July 21, 2016 at 5:36 am
Anandkumar-SQL_Developer (7/21/2016)
Hi Friends ,input code :
CREATE TABLE #First_nonzero
(
value int
)
GO
INSERT INTO #First_nonzero VALUES
(0),
(0),
(0),
(0),
(1),
(2),
(0),
(3),
(0)
Expected_output :
1
2
0
3
0
please some one suggest me how can i do this ..?
Regardless of the order in which you have inserted the data, your table's values have no default order.
So unless you add a column by which to order the data (timestamp? Identity? Sequence?) you cannot do this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2016 at 5:44 am
Anandkumar-SQL_Developer (7/21/2016)
Hi Friends ,input code :
CREATE TABLE #First_nonzero
(
value int
)
GO
INSERT INTO #First_nonzero VALUES
(0),
(0),
(0),
(0),
(1),
(2),
(0),
(3),
(0)
Expected_output :
1
2
0
3
0
please some one suggest me how can i do this ..?
I think I understand what you're trying to do here but there is problem. At the moment there's no way of ordering the rows. The rows in a table are not stored in any order. The rows could technically be returned in any order so the output may differ each time the query is run. Is there a way that the rows can be ordered?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 21, 2016 at 5:46 am
Here is a modified version.
IF OBJECT_ID('tempdb..#First_nonzero', 'U') IS NOT NULL
DROP TABLE #First_nonzero;
CREATE TABLE #First_nonzero
(
Counter INT IDENTITY(1, 1)
PRIMARY KEY
,value INT
);
GO
INSERT INTO #First_nonzero
(value)
VALUES (0),
(0),
(0),
(0),
(1),
(2),
(0),
(3),
(0);
SELECT *
FROM #First_nonzero fn
WHERE fn.Counter >= (SELECT MIN(fn2.Counter)
FROM #First_nonzero fn2
WHERE fn2.value > 0
)
ORDER BY fn.Counter;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2016 at 6:28 am
I've assumed that there is a column indicating when row was inserted. I also taken you literally and assumed that non-zero could be a negative rather than just greater than zero. I've changed the 1 to -1 to demonstrate this.
You can use any ordering column in the WHERE clause so long as there is an ordering column.
CREATE TABLE #First_nonzero
(
InsertDateTime DATETIME
,value int
)
;
INSERT INTO #First_nonzero
SELECT DATEADD(minute,1,GETDATE()),0 UNION ALL
SELECT DATEADD(minute,2,GETDATE()),0 UNION ALL
SELECT DATEADD(minute,3,GETDATE()),0 UNION ALL
SELECT DATEADD(minute,4,GETDATE()),0 UNION ALL
SELECT DATEADD(minute,5,GETDATE()),-1 UNION ALL
SELECT DATEADD(minute,6,GETDATE()),2 UNION ALL
SELECT DATEADD(minute,7,GETDATE()),0 UNION ALL
SELECT DATEADD(minute,8,GETDATE()),3 UNION ALL
SELECT DATEADD(minute,9,GETDATE()),0
SELECT
fnz.Value
FROM #First_nonzero fnz
WHERE fnz.InsertDateTime >= (
SELECT MIN(fnz1.InsertDateTime)
FROM #First_nonzero fnz1
WHERE fnz1.value <> 0
)
DROP TABLE #First_nonzero
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 21, 2016 at 6:32 am
I also taken you literally and assumed that non-zero can be a negative rather than just greater than zero.
Good catch, thanks.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2016 at 7:01 am
Hi BWFC / Phil Parkin ,
Its working thank u very much for ur valuable time.
July 21, 2016 at 7:09 am
You're welcome. What did you use to order the rows?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 21, 2016 at 7:33 am
Actually This is an interview question.
July 21, 2016 at 7:37 am
Anandkumar-SQL_Developer (7/21/2016)
Actually This is an interview question.
Really? Then unless your answer is along the lines of
"This cannot be done, unless a column exists on which to order, because rows in tables have no inherent order."
you should not get the job 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 21, 2016 at 7:47 am
Phil Parkin (7/21/2016)
Anandkumar-SQL_Developer (7/21/2016)
Actually This is an interview question.Really? Then unless your answer is along the lines of
"This cannot be done, unless a column exists on which to order, because rows in tables have no inherent order."
you should not get the job 🙂
If he was asking this during the interview, he shouldn't get the job either, as it would be cheating.
If it was after the interview, it's good as it shows interest on learning.
July 25, 2016 at 4:24 am
Hi this code also work :
SELECT Value
FROM
#first_nonzero
WHERE %%PhysLoc%% >=
(
SELECT MIN(%%PhysLoc%%) FROM #first_nonzero WHERE VALUE > 0
)
July 25, 2016 at 6:19 am
Anandkumar-SQL_Developer (7/25/2016)
Hi this code also work :SELECT Value
FROM
#first_nonzero
WHERE %%PhysLoc%% >=
(
SELECT MIN(%%PhysLoc%%) FROM #first_nonzero WHERE VALUE > 0
)
Not necessarily, the physical location of a row might not have the desired order.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply