MySQL Query for getting last row on the basis of category

Question

I have the following table structure:

Category
-------------------
id
slug
values
category
sort


Incidents
-------------------
id
scid
title
impact
date
servicestatus
incidentsstatus
details
createdon
editedon

In in incidents table we have multiple entries for a single category. scid is the category id which is given in table category (id)

I want to show all the category names with the value of servicestatus field from incidents table.

Kind of

Service      Status
-------    ----------
Internet     1 
Email        0
Server1      1

Please check and advise what we can do with it, I tried it with join between both table on the basic of category.id and insidents.scid but that is showing duplicate results?

Thanks!

Check these questions/answers: http://stackoverflow.com/questions/7233757/order-within-group-by — ypercubeᵀᴹ, Sep 20 '11 at 11:56
http://stackoverflow.com/questions/4668902/mysql-group-by-to-display-latest-result — ypercubeᵀᴹ, Sep 20 '11 at 11:56
http://stackoverflow.com/questions/755918/simple-query-to-grab-max-value-for-each-id — ypercubeᵀᴹ, Sep 20 '11 at 11:57

score 1 · Answer 1 · edited Apr 03 '15 at 08:32

If I understand it correctly, the column "servicestatus" will contain 0 or 1? or?

If it is to see if there any "open" incidents you could make your SQL as this

SELECT 
    c.category, max(i.servicestatus) 
FROM 
    Category as c 
INNER JOIN 
    Incidents as i ON c.id = i.scid 
GROUP BY 
    c.category

The MAX() sql command will return the highest value from the incidents table rows within each category

\T

score 1 · Accepted Answer · edited Sep 20 '11 at 12:34

1

Use this query :

select cat.value, ins.servicestatus from Incidents ins, category cat where ins.scid = cat.id group by cat.id;

edited Sep 20 '11 at 12:34

Vishwanath Dalvi

35,388
41
123
155

answered Sep 20 '11 at 11:56

Play cool

158
4
12

score 0 · Answer 3 · answered Sep 20 '11 at 12:13

If you want to see current status from the incident table, you should use something like this:

select c.Category, i.servicestatus 
from category as c inner join incidents as i on c.id = i.scid 
where i.date = (select max(date) from incidents as i2 where i.scid = c.id)

MySQL Query for getting last row on the basis of category

3 Answers3