0

please help me with the following problem. I have spent already one week trying to put all the logic into one SQL query​ but still got no elegant result. I hope the SQL experts could give me a hint,

I have a table which has 4 fields: date, expire_month, expire_year and value. The primary key is defined on 3 first fields. Thus for a concrete date few values are present with different expire_month, expire_year. I need to chose one value from them for every date, present in the table.

For example, when I execute a query:

SELECT date, expire_month, expire_year, value FROM futures 
WHERE date = ‘1989-12-01' ORDER BY expire_year, expire_month;

I get a list of values for the same date sorted by expirity (months are coded with letters):

1989-12-01  Z   1989    408.25
1989-12-01  H   1990    408.25
1989-12-01  K   1990    389
1989-12-01  N   1990    359.75
1989-12-01  U   1990    364.5
1989-12-01  Z   1990    375

The correct single value for that date is the k-th record from top. For example, of k is 2 then the «correct single» record would be:

1989-12-01  H   1990    408.25

How can I select these «correct single» values for every date in my table?

Sergej Kosov
  • 118
  • 7
  • 4
    Possible duplicate of [SQLite3 (or general SQL) retrieve nth row of a query result](https://stackoverflow.com/questions/3419626/sqlite3-or-general-sql-retrieve-nth-row-of-a-query-result) – nLee May 23 '19 at 22:25
  • @nLee that one won't work without a lot of changes since OP wants the the nth row of every date. – Shawn May 24 '19 at 00:53

3 Answers3

2

You can do it with rank():

select t.date, t.expire_month, t.expire_year, t.value from (
  select *,
    rank() over(partition by date order by expire_year, expire_month) rn
  from futures 
) t
where t.rn = 2

The column rn in the subquery, is actually the rank of the row grouped by date.
Change 2 to the rank you want.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thank you very much! This is a very elegant and simple method to solve my problem. It works with SQLite starting from version 3.25, however, does not work in the latest version of the SQLiteStudio... – Sergej Kosov May 25 '19 at 15:22
  • I can't believe that SQLiteStudio is still that far behind. This is why I use DB Browser for SQLite. – forpas May 25 '19 at 15:28
  • Thanks for the hint! I will try the DB Browser too! – Sergej Kosov May 25 '19 at 15:34
1

While forpas's answer is the better one (Though I think I'd use row_number() instead of rank() here), window functions are fairly recent additions to Sqlite (In 3.25). If you're stuck on an old version and can't upgrade, here's an alternative:

SELECT date, expire_month, expire_year, value
FROM futures AS f
WHERE (date, expire_month, expire_year) =
      (SELECT f2.date, f2.expire_month, f2.expire_year
       FROM futures AS f2
       WHERE f.date = f2.date
       ORDER BY f2.expire_year, f2.expire_month
       LIMIT 1 OFFSET 1)
ORDER BY date;

The OFFSET value is 1 less than the Kth row - so 1 for the second row, 2 for the third row, etc.

It executes a correlated subquery for every row in the table, though, which isn't ideal. Hopefully your composite primary key columns are in the order date, expire_year, expire_month, which will help a lot by eliminating the need for additional sorting in it.

Shawn
  • 47,241
  • 3
  • 26
  • 60
-2

You can try the following query.

select * from 
(
SELECT rownum seq, date1, expire_month, expire_year, value FROM testtable
WHERE date1 = to_date('1989-12-01','yyyy-mm-dd') 
ORDER BY expire_year, expire_month
)
where seq=2
forpas
  • 160,666
  • 10
  • 38
  • 76
GautamKhandelwal
  • 41
  • 1