SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
( SELECT *
FROM tableX AS tt
WHERE tt.widget = t.widget
AND tt.state <> 'down'
AND tt.`timestamp` > MAX(t.`timestamp`)
) ;
I think that you'll need two indices, one on (widget, state, timestamp)
and one on (widget, timestamp, state)
for efficiency.
This will work, too, and will be needing only one index, on (widget, timestamp, state)
:
SELECT t.widget, t.`timestamp`
FROM
tableX AS t
JOIN
( SELECT widget, MAX(`timestamp`) AS ts
FROM tableX
GROUP BY widget
) AS tm
ON tm.widget = t.widget
AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;
Tested both at SQL-Fiddle: test
Looking at your table structures, I would suggest that you change the design to something that is normalized. For example:
create table tbl_subjects
(
sub_id int,
subject_name varchar(25)
);
create table tbl_cmarks
(
c_id int,
examid int,
rollno int,
sub_id int,
mark int
);
Using something similar to above will allow you to add new subjects without having to alter your table. Then you will just join the tables on the sub_id
to get the list of subjects for each class.
select ta.rollno,ta.StdNm,tc.C_Name,
tm.mark,
s.subject
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
on tm.sub_id = s.sub_id
where tm.Cid = 22
and tm.Examid=9;
See SQL Fiddle with Demo. The above will give you a result in rows, but you could easily apply an aggregate function with a CASE expression to pivot the data into columns. Similar to the following:
select ta.rollno,
ta.StdNm,
tc.C_Name,
max(case when s.subject = 'English' then tm.mark end) Emglish,
max(case when s.subject = 'Physics' then tm.mark end) Physics
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
on tm.sub_id = s.sub_id
where tm.Cid = 22
and tm.Examid=9
group by ta.rollno, ta.StdNm, tc.C_Name;
See Demo.
But if you don't change your current table structure and if you don't know the columns that you are going to return, then you will have to implement a prepared statement to generate dynamic SQL.
First, you will create the list of the classes:
set @sqlList = null;
set @query = null;
SELECT
GROUP_CONCAT(concat('tm.', subject))
INTO @sqlList
FROM tbl_subjects
where C_Id=22;
See Demo. This will give you the list of classes for each c_id
. Once you have the list of classes, then you can add this to the rest of the sql string so the full code will be:
set @sqlList = null;
set @query = null;
SELECT
GROUP_CONCAT(concat('tm.', subject))
INTO @sqlList
FROM tbl_subjects
where C_Id=22;
SET @query
= CONCAT('SELECT ta.rollno,ta.StdNm,tc.C_Name, ', @sqlList, '
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
ON ta.rollno = tm.rollno
where tm.Cid = 22
and tm.Examid=9');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo
Best Answer
Unless you are needing to return data that is in the user table you don't really need to involve it in your query. In that case you could implement a query such as
Were you to need a result set including results from both tables I would use a query similar to the one Marco suggests.