CODEWITHSUNDEEP
mysql
SDF
SDF

Reputation: 1331

Mysql Group by and order by query

Table : gpreport

id  stid    stname  gpdate  
1   1234    store1  2014-11-16
2   1234    store1  2014-11-15
3   4321    store2  2014-11-17
4   4321    store2  2014-11-17
5   4321    store2  0000-00-00
6   1234    store1  2014-11-15

I have to get last id for stid and that stid have gpdate this record should show. For example, stid 4321 for last id is 5 and it should not show the record and stid 1234 last id is 6 and it has gpdate, so that record sholuld show.

My query is:

$sql="SELECT * FROM (SELECT stid, MAX(id) AS id FROM gpreport 
WHERE gpdate >= CURDATE() GROUP BY stid) AS sid JOIN gpreport USING (stid, id)";

It showing id 4 for 4321 also.

Upvotes: 0

Views: 39

Answers (1)

Kickstart
Kickstart

Reputation: 21513

Use a sub query to get the latest and join that against the table to get the other details.

SELECT a.*
FROM gpreport a
INNER JOIN 
(
    SELECT stid, MAX(id) AS max_id
    FROM gpreport
    GROUP BY stid
) sub0
ON a.stid = sub0.stid
AND a.id = sub0.max_id
WHERE sub0.gpdate> '0000-00-00'

Which is pretty much what you have, except you check for the date within the sub query.

Upvotes: 2

Related Questions