CODEWITHSUNDEEP
mysql
swetha
swetha

Reputation: 13

how to get records form a table based on particular values in a column

I have a table like

id fulltank vehicle number
1   Yes        123
2   Yes        456
3   No         123
4   No         123

So I wanted to get rows with id 3 and 4. I mean I should get all records with "No" fulltank.

If : 

id fulltank vehicle number
1   Yes       123
2   Yes       456
3   No        123
4   No        123
5   Yes       123
6   No        123
7   No        123
8   No        123
9   Yes       456

Then I should get rows with id 6,7,8 (of a particular vehicle). That means records after "Yes" to last inserted row with "No".

User every time selects whether he is filling the tank completely or not if he is filling completely fulltank will be recorded as "Yes" or if he is filling it with 45 or 75 or something it will be recorded as "No". When the user is filling it completely (ie., fulltank is "Yes") need to get all the records of previously created/inserted rows with "No" values (ie., only records after last - fulltank "Yes" of a particular vehicle).

Upvotes: 1

Views: 59

Answers (2)

Nick
Nick

Reputation: 147266

You can use a NOT EXISTS query to find all rows with fulltank = 'No' for a given vehicle where there is no row with fulltank = 'Yes' with a higher id value:

SELECT *
FROM fuel f1
WHERE fulltank = 'No'
  AND NOT EXISTS (SELECT *
                  FROM fuel f2
                  WHERE f2.vehicle_number = f1.vehicle_number
                    AND f2.id > f1.id
                    AND f2.fulltank = 'Yes'
                  )

Output (for your second table sample)

id  fulltank    vehicle_number
6   No          123
7   No          123
8   No          123

Demo on SQLFiddle

Upvotes: 1

nice_dev
nice_dev

Reputation: 17835

You can divide this into 3 steps:

  • First is to get the max ID of the row which has fulltank = 'No'
  • Second is to get the max ID of the row which has fulltank = 'Yes' but it's ID is less than the first step's ID.
  • Third is to get all rows which have ID between second step's ID + 1 and first step's ID.

Query:

select *
from test_table
where id between 
            (select max(id) from test_table where fulltank = 'Yes' and id < (select 
                                max(id) from test_table where fulltank = 'No')) + 1                                                                                      
          and                                                                                        
             (select max(id) from test_table where fulltank = 'No')

Demo: https://www.db-fiddle.com/f/dJzMz3eGH3KLLQVkyjmhP/0

Upvotes: 0

Related Questions