How can I get 10 first records of join tables in sqlite?

Question

How can I get just 10 first records of Nemayeh.Nemayeh fields in Sqlite?

SELECT
  Keyword,
  Nemayeh.Nemayeh

FROM
  (SELECT
     Keyword.Id,
     Keyword.Keyword
   FROM Keyword
   ORDER BY Keyword.Keyword ASC
   LIMIT 10 OFFSET 0
  ) AS tmp

INNER JOIN KeyWord_Nemayeh
        ON KeyWord_Nemayeh.Id_Keyword = tmp.Id

INNER JOIN Nemayeh
        ON Nemayeh.Id = KeyWord_Nemayeh.Id_Nemayeh

ORDER BY 1,2

For Example :

Key1 Nem1_1
Key1 Nem1_2
Key1 Nem1_3
.
.
.
Key1 Nem1_10
Key2 Nem2_1
Key2 Nem2_2
.
.
.
Key2 Nem2_10

So you want a result table which has at least 10, but not more than 10 **distinct** `Nemayeh.Nemayeh` value? (Appending `LIMIT 10` to your query would be too simple :) — biziclop, Aug 12 '12 at 12:35

score 2 · Accepted Answer · answered Aug 12 '12 at 12:47

2

This is pretty much what biziclop said but should work.

SELECT
  Keyword,
  Nemayeh.Nemayeh
FROM
  (SELECT Keyword.Id,Keyword.Keyword 
   FROM Keyword
   ORDER BY Keyword.Keyword ASC
   LIMIT 10 OFFSET 0) AS tmp

INNER JOIN KeyWord_Nemayeh ON KeyWord_Nemayeh.Id_Keyword = tmp.Id

INNER JOIN Nemayeh ON Nemayeh.Id = KeyWord_Nemayeh.Id_Nemayeh

ORDER BY 1,2 LIMIT 0, 10

answered Aug 12 '12 at 12:47

Pablo Jomer

9,870
11
54
102

I get 10 'Keyword', then I want get 10 'Nemayeh' for every 'Keyword'. Please see my edit post. – Ali Ahmadi Aug 13 '12 at 06:51
Okay I understand... I have tried to look up if you can create functions in SQLite but I didn't find any information on this. Otherwise I think what you want to achieve is hard in sqlite. – Pablo Jomer Aug 13 '12 at 11:19

score 1 · Answer 2 · answered Aug 12 '12 at 12:05

1

Try:

SELECT TOP 10 Keyword,... FROM...

answered Aug 12 '12 at 12:05

Auris

61
7

OK, sorry :) http://stackoverflow.com/questions/2728999/how-to-get-top-5-records-in-sqlite – Auris Aug 12 '12 at 15:58

How can I get 10 first records of join tables in sqlite?

2 Answers2