Query Builder with latest record

Yii 1.1 General Discussion
1

Hi guys,

how to write the query builder to get the latest record for the duplicate field? i now want to get all the record for company_Id =2 with the latest product balance without duplicate product_Id.

product table

id created product_Id company_Id product_balance

1 2011-09-05 10:24:32 1 2 9

2 2011-09-06 10:24:32 2 2 10

3 2011-09-07 10:24:32 2 1 8

4 2011-09-08 10:24:32 1 2 7

The result that i want to get




product table

id      created              product_Id  company_Id   product_balance

2    2011-09-06 10:24:32       2           2             10

4    2011-09-08 10:24:32       1           2             7





SELECT * FROM machine_product_inout WHERE machine_id='2' GROUP BY product_id ORDER BY id/

// this sql query only can get record for id 1 and id 2. How to get the record for id 2 and id 4??Can anyone help me…

thx

2

Hi,

order by product_balance descending not by id ascending.

3

Hi drylko,


SELECT * FROM product WHERE machine_id='2' GROUP BY product_id ORDER BY qty_balance DESC

the result is still the same.

sorry.i want to get all the record for company_Id =2 with the latest product balance based on product_Id without duplicate the product_id.


product table

id created            product_Id company_Id product_balance

1 2011-09-05 10:24:32    1              2             9

2 2011-09-06 10:24:32    2              2             10

3 2011-09-07 10:24:32    2              1             8

4 2011-09-08 10:24:32    1              2             7

The result i want is…




id      created              product_Id  company_Id   product_balance

2    2011-09-06 10:24:32       2           2             10

4    2011-09-08 10:24:32       1           2             7

Pls help… =)

4

anyone got solution for this problem?? . Pls help. thank =)

5

SELECT * FROM product WHERE machine_id=‘2’ GROUP BY product_id ORDER BY created DESC

6

hi qijunz,


SELECT * FROM product WHERE machine_id='2' GROUP BY product_id ORDER BY created DESC

the result is


product table

id created            product_Id company_Id product_balance

2 2011-09-06 10:24:32    2              2             10

1 2011-09-05 10:24:32    1              2             9

when i use group by product_id, it will filter result with only id 1 and 2 from top searching. so the result will show as above when i use order by created DESC from group by product_id… Nid helpss…

Thanks.