get the first row of a join

Question

Hi guys!

I've a query like this:

select t1.id, t1.sample, t2.id from table t1 join table t2  
on t1.sample = t2.sample and t2.id > t1.id

I need to get the first row that satisfy the second condition.

Any idea?

Answer 1

Well.

You could try this: if you're working with SQL Server, add a top 1

if you're working with MySQL, add a limit 1

it will only return the first row.

To ensure, you can add an order clause too.

Answer 2

SELECT t1.id, t1.sample, t2.id FROM table t1 JOIN TABLE t2
ON t1.sample = t2.sample AND t2.id > t1.id WHERE ROWNUM = 1

Answer 3

All rows returned will satisfy both conditions, so you don't have to do anything special to make sure that the second condition is satisfied.

If you want to limit the returned results size to 1, append WHERE ROWNUM = 1 to the query if it will be run on Oracle.

Answer 4

Oracle uses something called "ROWNUM". Limiting the number of results is annoyingly inconsistent across DBMSs.

SELECT t1.id, t1.sample, t2.id
FROM table t1 join table t2  
ON t1.sample = t2.sample and t2.id > t1.id
WHERE ROWNUM <= 1

Answer 5

If you want the least t2.id that satisfies the second condition then

select * from (select t1.id, t1.sample, t2.id from table t1 join table t2
on t1.sample = t2.sample and t2.id > t1.id order by t2.id ) where rownum =1

If you want the greatest t2.id that satisfies the second condition then

select * from (select t1.id, t1.sample, t2.id from table t1 join table t2
on t1.sample = t2.sample and t2.id > t1.id order by t2.id desc) where rownum =1