How to get data from database with if clause (SQL query)

Question

Suppose I have a table like the following:

Table name: trial

ID | Date        | Name | Status     | Type
1  | 2017-06-01  | ABC  | Not Active | Food
2  | 2017-06-02  | DEF  | Not Active | Food
3  | 2017-06-03  | GHI  | Active     | Food

To retrieve the last data with type = FOOD from the table above, I use this query

SELECT * FROM `trial` WHERE type = 'FOOD' order by id DESC limit 1

if I want to retrieve the latest data from type = FOOD, but if there the data with status = active, this data is taken.

Assuming the table conditions like this.

ID | Date        | Name | Status     | Type
1  | 2017-06-01  | ABC  | Active     | Food
2  | 2017-06-02  | DEF  | Not Active | Food
3  | 2017-06-03  | GHI  | Not Active | Food

I Use query like this

SELECT * FROM `trial` WHERE type = 'FOOD' AND status = 'ACTIVE' order by id DESC limit 1

But what if the table conditions change with this assumption.

ID | Date        | Name | Status     | Type
1  | 2017-06-01  | ABC  | Not Active | Food
2  | 2017-06-02  | DEF  | Not Active | Food
3  | 2017-06-03  | GHI  | Not Active | Food

How to retrieve the latest data from type = FOOD, but if there the data type = food with status is active, then use data with status = active, but if there is no active status then the last data use

What sql query should I use ?

Then go for a group by select * from your_table group by status — Saad Suri, Jun 13 '17 at 09:31
To select latest data from table where status is active you should use `select * from your_table where status='Active' order by ID desc` — Praveen Kumar, Jun 13 '17 at 09:33
Possible duplicate of [Getting all results using where clause](https://stackoverflow.com/questions/11682455/getting-all-results-using-where-clause) — LuFFy, Jun 13 '17 at 09:34
*"Take the latest data from the status of the food type but if there is an active status of the type of food then the data taken is a line with active status. ... I want to take not only the active status only"* - I don't understand what *exactly* you're trying to do. Can product `ABC` (for instance) appear in this table multiple times with different statuses by date? What do you want to retrieve when the status is `Not Active` ? — CD001, Jun 13 '17 at 09:36
@PraveenKumar If the data in my table is all active status, no data is displayed because you are using query where status = 'Active'. Whereas I want to retrieve the latest status data from the food type. — Ade K Setiadi, Jun 13 '17 at 09:39
Is `ID` an auto-incremented PRIMARY key for this product status table or is it the product id (with the table using a compound PRIMARY key)? — CD001, Jun 13 '17 at 09:43
@CD001 No, i only take 1 value only (limit (1)). With parameters as I described above. Ya, ID is primary key & auto-incremen — Ade K Setiadi, Jun 13 '17 at 09:43
@AdeKSetiadi please explain proper issue as per your question praveen's query work fine — Ahmed Ginani, Jun 13 '17 at 09:51
@CD001 I have updated the question, hopefully can be understood my intent — Ade K Setiadi, Jun 13 '17 at 14:15

score 0 · Answer 1 · answered Jun 13 '17 at 09:31

0

first you study this manual manual.. this manual will help you to retrieve data from database.

you can fetch data from database using select command and DESC for latest record

select * from table_name where Status='Active' ORDER BY id DESC;;

answered Jun 13 '17 at 09:31

I updated my question, sorry my previous question did not match what I mean – Ade K Setiadi Jun 13 '17 at 14:20

score 0 · Answer 2 · answered Jun 13 '17 at 09:31

0

select * from TabelName where status="Active"

answered Jun 13 '17 at 09:31

Naveen kumar Rallabandi

1

I updated my question, sorry my previous question did not match what I mean – Ade K Setiadi Jun 13 '17 at 14:20

score 0 · Answer 3 · answered Jun 13 '17 at 09:43

0

Use where clause with ORDER BY :

select * from table_name where Status='Active' ORDER BY id DESC;

answered Jun 13 '17 at 09:43

Ahmed Ginani

6,522
2
15
33

I updated my question, sorry my previous question did not match what I mean – Ade K Setiadi Jun 13 '17 at 14:21

score 0 · Answer 4 · answered Jun 13 '17 at 10:18

0

Please use below mentioned query.

SELECT * from table_name WHERE Status='Active' AND Type='Food' ORDER BY id DESC;

answered Jun 13 '17 at 10:18

Alex Mac

2,970
1
22
39

I updated my question, sorry my previous question did not match what I mean – Ade K Setiadi Jun 13 '17 at 14:20

score 0 · Accepted Answer · answered Jun 13 '17 at 10:59

0

if i understand you correctly, you want to take the latest data if there are all Not active, but if there is an active state you want those ?

Try this

Select * from your_table
WHERE Type = 'Food' 
ORDER BY Status != "Active" ASC, Date DESC
LIMIT 1

or in Codeigniters Querybuilder

$this->db
    ->select("*")
    ->from("your_table")
    ->where("Type","Food")
    ->order_by("Status != 'Active'", "ASC")
    ->order_by("Date","DESC")
    ->limit(1);

answered Jun 13 '17 at 10:59

Atural

5,389
5
18
35

I updated my question, sorry my previous question did not match what I mean – Ade K Setiadi Jun 13 '17 at 14:20
thats exactly what i showed you here - did you test it ? Because my query goes with that approach - if you have an active state - it would take it - otherwise it takes the latest entry – Atural Jun 13 '17 at 14:38

score 0 · Answer 6 · answered Jun 13 '17 at 11:47

0

 $where = array('status'=>'Active' ,'type' =>'Food');
 $this->db->where($where);
 $this->db->order_by('Date','ASC');
 $this->db->get('_table_name');

answered Jun 13 '17 at 11:47

bahaa alaa

1

I updated my question, sorry my previous question did not match what I mean – Ade K Setiadi Jun 13 '17 at 14:20

score -1 · Answer 7 · edited Jun 13 '17 at 09:53

-1

Try the following query

select * from table_name where Status='Active';

edited Jun 13 '17 at 09:53

Sagar V

12,158
7
41
68

answered Jun 13 '17 at 09:28

Praveen P K

198
12

How to get data from database with if clause (SQL query)

7 Answers7