question

kandimal-7427 avatar image
0 Votes"
kandimal-7427 asked MelissaMa-msft commented

sql query for reading records one at a time

I need to read a Microsoft sql db table record one at a time (first in first out). we have Field1 with time stamp of insert of record into table. we have Field2 with value 'N". I need to pick the oldest record of table first with Value "N" on the basis of Field1 and Field2.

Can you advise the SQL query for this requirement ? I have a business requirement where in I need to read only one record at a time, and I am not explaining that business requirement here. I have batch job scheduled and will run every 5mins.

Thanks in Advance

kandimal

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Do you also want to delete the row, so that the next time, after 5 min, you will read another row?


0 Votes 0 ·

Hi @kandimal-7427,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
SELECT TOP(1) col1, col2, ...
FROM   tbl
WHERE Field2 = 'N'
ORDER BY Field1

There should be index like this:

CREATE INDEX Field1_ix ON (Field1)  INCLUDE (Field2) WHERE Field2 = 'N'
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ErlandSommarskog commented

Hi @kandimal-7427,

Welcome to Microsoft Q&A!

You could also try with SET ROWCOUNT (N) which is similar with TOP (N).

Please refer below:

 set rowcount 1
    
 select * 
 from yourtable
 where Field2='N' 
 order by Field1

Only one note is that once you have set rowcount this will persist for the life of the connection so make sure you reset it to 0 once you are done with it.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You could also try with SET ROWCOUNT (N) which is similar with TOP (N).

But it is a lot more dangerous! If you forget to reset it, you can wreak havoc with things. I have some horror stories to share!

This a very old command which should not be used today. Please make me a favour and never suggest it in public again!

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

If you do not want to delete the row which was read, you need a flag column to indicate it. See the example below:

 DECLARE @tbl TABLE (
     UniqueColumn int IDENTITY,
     Field1 datetime,
     Field2 char(1),
     IsRead bit default(0)
 );
    
 INSERT INTO @tbl(Field1, Field2) VALUES
 ('2021-05-01', 'N'), ('2021-05-02', 'Y'), ('2021-05-03', 'N'),
 ('2021-05-04', 'N'), ('2021-05-05', 'Y'), ('2021-05-06', 'N');
    
 DECLARE @UniqueColumn int;
    
 SELECT TOP 1 @UniqueColumn = UniqueColumn 
 FROM @tbl
 WHERE Field2 = 'N' AND IsRead = 0
 ORDER BY Field1
    
 UPDATE @tbl
 SET IsRead = 1
 WHERE UniqueColumn = @UniqueColumn;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.