CODEWITHSUNDEEP
sqlsqliteselect
Sergej Kosov
Sergej Kosov

Reputation: 128

How to select k-th record per field in a single SQL query

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?

Upvotes: 0

Views: 311

Answers (3)

Shawn
Shawn

Reputation: 52579

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.

Upvotes: 1

GautamKhandelwal
GautamKhandelwal

Reputation: 41

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

Upvotes: -2

forpas
forpas

Reputation: 164184

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.

Upvotes: 2

Related Questions