CODEWITHSUNDEEP
sqlsqlite
dropWizard
dropWizard

Reputation: 3538

sql - Selecting row that compares against values in other rows

My database looks something like this

Value | Date     | parent_id
 y    | 2019-01-01 | 1
 n    | 2019-01-02 | 1
 n    | 2019-01-03 | 1

I want to write a query that will look for a row that has parent_id = 1 and value = 'y', and also evaluate it against other rows that have parent_id=1' but will check to make sure the date is greater than its row's date.

So in this case it won't return anything.

If the data was like:

Value | Date     | parent_id
 y    | 2019-01-04 | 1
 n    | 2019-01-02 | 1
 n    | 2019-01-03 | 1

Then it would return the first row.

If it means anything, I am using SQLite.

Upvotes: 1

Views: 42

Answers (3)

GMB
GMB

Reputation: 222432

With SQLite >= 3.25, simply use window function ROW_NUMBER() :

SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.parent_id ORDER BY t.date DESC) rn
    FROM mytable t
) x WHERE rn = 1 AND parent_id = 1 AND value = 'y'

Window function are usually more efficient than correlated subqueries.

Demo on DB Fiddle.

The query works as follows.

First, it traverses the table and uses ROW_NUMBER() to assign a a rank to each record within the group of records having the same parent_id, and with the record with the highest date numbered 1. You can run the subquery to see what it actually returns (it gets more interesting when there is more than one parent_id) :

SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.parent_id ORDER BY t.date DESC) rn
FROM mytable t

Then, all that is left to do to is filter on the target parent_id, pick the record with row number 1 (ie the record that has the highest date for this parent_id), and make sure that it has value = 'y'.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Another method just looks for the last row to see if it meets your conditions:

select t.*
from (select t.*
      from t
      where t.parent_id = 1
      order by t.date desc
      limit 1
     ) t
where value = 'y';

This checks that the last row with parent_id = 1 has the value of 'y'.

Upvotes: 0

forpas
forpas

Reputation: 164069

You can use NOT EXISTS:

select * from tablename t
where 
  parent_id = 1 and value = 'y'
  and not exists (
    select 1 from tablename 
    where parent_id = t.parent_id
    and date > t.date
  )

See the demo.
As a suggestion: since you are using SQLite,you should consider to change the format of the Date column to YYYY-MM-DD, so that it is comparable.

Upvotes: 1

Related Questions