Spark Sql Hive sql query with more than one limit clause

0
votes

I am new to Hive and spark sql technologies.I had tried limit clause in spark sql. but it support only for particular limit starting from zero to that particular limit.But I want to retrieve rows from a specific start point to specific end point.Can you please anyone to suggest a method to achieve this.

Query1 :: SELECT * FROM `Emp` LIMIT 10;  - this query supports in both sql and spark sql

but

Query2 :: SELECT * FROM `Emp` LIMIT 10,20;  - to retrive rows from 10 to 20 supports in sql, but not in spark sql.
2
sqlhivelimitapache-spark-sql

2 Answers

0
votes

Try a modified LEFT JOIN:

SELECT a.*
FROM
(SELECT * FROM `Emp` LIMIT 20) a
LEFT JOIN
(SELECT * FROM `Emp` LIMIT 10) b
ON a.primary_key=b.primary_key
WHERE b.primary_key IS NULL
0
votes

You can use ROW_NUMBER in HQL

SELECT *,ROW_NUMBER over (Order by id)  as rowid FROM `Emp`
where rowid > 10 and rowid <=20;