get first and second row from sql table

Question

I have a table

id term_id price
 2       1   100
 4       1   200
 6       1   500
 1       2   100
 3       3   100
 5       3   500
 7       3   700

I want only two row of all term id.

id term_id price
 4       1   200
 6       1   500
 1       2   100
 5       3   500
 7       3   700

What is the SQL? We need that, or a anonymized version of it to help you. — Nicholas Summers, Jul 21 '17 at 16:39
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jul 21 '17 at 16:44
I think I understand your question, hope my answer can help you. — karran, Jul 21 '17 at 17:03
Which two rows do you want? Are you willing to trust the random order of the rows in the database, or do you want to **Return the two rows with the lowest id for each given term_id**? — John Rotenstein, Jul 22 '17 at 08:00
I need only two row of all same term id which has the highest id in the table. @John Rotenstein — , Jul 22 '17 at 12:33

score -1 · Answer 1 · answered Jul 21 '17 at 16:45

-1

Three solutions

Using TOP

SELECT TOP 2 * FROM table;

Using LIMIT

SELECT * FROM table
LIMIT 2;

And using ROWNUM

SELECT * FROM table
WHERE ROWNUM <= 2;

answered Jul 21 '17 at 16:45

O.Rares

1,031
1
16
19

score -1 · Accepted Answer · answered Jul 21 '17 at 17:02

SELECT *
FROM   test s
WHERE 
    (
        SELECT  COUNT(*) 
        FROM    test  f
        WHERE f.term_id = s.term_id and 
        f.price >= s.price
    ) <= 2;

example with your example table: http://www.sqlfiddle.com/#!9/79a222/10

found here: In SQL, how to select the top 2 rows for each group

get first and second row from sql table

2 Answers2