Get Top Few Rows which satifies the Condition

Question

I need to get First Two Rows which are having C = '1', I'm ordering the query by TimeStamp Descending, I dont want the Last row to be Fetched by query

    A      B  C   TimeStamp
 ------------------------------------
  foo    one  1   20180405153936
  foo    two  1   20180405153936
  foo    two  2   20180405115417
  foo    one  2   20180405115053
  foo  three  1   20180405113923

I don't Understand why people are down voting, You can Comment what is wrong the question. You should not discourage the new comers. Please — ViKu, Apr 10 '18 at 06:40
I didn't downvote you, but it probably happened because you showed zero effort. — Tim Biegeleisen, Apr 10 '18 at 06:44
As I'm Not so good in SQL, I'm unable to figure it out. I'll Learn definitely in future — ViKu, Apr 10 '18 at 06:46

Peter Abolins · Accepted Answer · 2018-04-10T06:39:00.307

1

You can use the ROWNUM construct, but keep in mind that unless you wrap the ordering select, ROWNUM will bring back the rows in whatever order they were added to the table.

SELECT * FROM
    (SELECT * FROM 
         yourTable
     WHERE C = 1 
     ORDER BY "TimeStamp" DESC)
WHERE ROWNUM <= 2

EDIT

Given the added information, the following will probably work better than using a ROWNUM.

SELECT * FROM 
    yourTable
WHERE 
    C = 1 AND 
    "TimeStamp" > (
        SELECT MIN("TimeStamp") FROM yourTable WHERE C = 1
    ) 
ORDER BY "TimeStamp" DESC

edited Apr 10 '18 at 06:39

answered Apr 10 '18 at 06:21

Peter Abolins

1,520
1
11
18

The thing is i'll be not knowing how many rows at the top which are satisfying the condition. – ViKu Apr 10 '18 at 06:25

Jay Shankar Gupta · Answer 2 · 2018-04-10T06:23:24.820

0

You can use ROWNUM

SELECT * FROM
(SELECT * FROM 
     Table1 
 WHERE C=1
 ORDER BY "TimeStamp" DESC)
WHERE ROWNUM <= (SELECT COUNT(*) FROM Table1 WHERE C=1)-1

Demo

http://sqlfiddle.com/#!4/08ed98/11

edited Apr 10 '18 at 06:23

answered Apr 10 '18 at 06:16

Jay Shankar Gupta

5,918
1
10
27

Actually Its not fixed to two rows, it may also be more at the Top – ViKu Apr 10 '18 at 06:17
so you in place of 2 you can write any number – Jay Shankar Gupta Apr 10 '18 at 06:20
That is not possible, I'll be not knowing how many rows at the Top, Its dynamically Generating – ViKu Apr 10 '18 at 06:21
This won't work. Try inserting the third row first, and see what result you get. – Peter Abolins Apr 10 '18 at 06:22
Check the answer now i edited my answer @ViKu – Jay Shankar Gupta Apr 10 '18 at 06:30
It's Working Fine Thanks @Jai Shankar – ViKu Apr 10 '18 at 06:34
Welcome!! @ViKu – Jay Shankar Gupta Apr 10 '18 at 06:37

Get Top Few Rows which satifies the Condition

2 Answers2