SQL statement question

[Hans]

Hello group,

I have a table with the next contents.
It lists data about : Who sent what kind of message at what time.
For the Level column: The highest level is Critical, the middle is Warning,
the lowest is Info.

Id Timestamp Level
1 12:00 Info
1 13:00 Warning
1 14:00 Critical
2 12:00 Info
2 13:00 Warning
2 14:00 Info
2 15:00 Warning
3 12:00 Critical
3 13:00 Warning
4 15:00 Info
5 15:00 Info
5 16:00 Warning

What I want to get out of his list is:
The first message with the highest level of each Id. So let the Level-column
prevail above the Timestamp column,so I want to get this result:

1 14:00 Critical // The only 'Critical'
2 13:00 Warning // the first 'Warning'
3 12:00 Critical // The only 'Critical'
4 15:00 Info // The only one, so simple
5 16:00 Warning // The highest level, the only
'Warning'
So: First find the highest level of message of each Id and then find the
first one.
The first part I tried with CASE .. THEN .. END construction.

Any hints appreciated.

Gr, Hans

[Siemel Naran]

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message
news:S%8Oc.151848

"Hans" <ni*********@zonnet.nl> wrote in message news:4108fb97$0$93324

"Leif" <id****@internet.dk> wrote in message

select id,min(time),level from table a where (id,level) in (select
id,max(level) from table b group by b.id) group by a.id

BTW-2 The query you propesed is running now for more than an hour, so

What is the exact query you ran? I think MySql supports function indexes,
so for example you could try to create index (id, case level when 'High'
then 3 ... end), or create index (id, if(level='High', 3, if(...))). Will
need to look it up, but have to run to work.
So I checked, and no, MySql 4.1 and even 5.0 I think, does not support
function indexes.
Also, what does the query plan say is the driving table and index?

The above is important, because it could mean the difference between a query
that returns right away, and another that takes hours.

In Leif's original query, I think you would need only one index. This
assumes driving by the inner query is the right thing for your situation
(which may or may not be the case), and that MySql will in this case indeed
drive by the inner query.

create index table_level on table (id, level);

But this only works when the level numbers are 1 for Critical, 2 for
Warning, 3 for Info. By contrast, sorting level as strings yields Critical,
Info, Warning -- which is not correct.
One solution is function indices, which some databases provide, but not
MySql. It would look like this:

create index table_level on table (id, case level when 'Critical' then 1 ...
end);
Most databases support triggers, but MySql only will in 5.1. It would look
something like this:

create trigger on table
for update
as update table set levelnum = case level when 'Critical' then 1 ... end;

create index table_level on table (id, levelnum);
Some databases also let you define calculated fields, so you'd say

create table (
level varchar(10) not null,
levelnum smallint(1) as case level when 'Critical' then 1 ... end,
index on table (id, levelnum);
);

But I don't think MySql does not let you do this either.
Of course, you could just add your levelnum column and when you insert or
update table.level, remember to update levelnum too. So you'd have to put
more work into the PHP or C++ scripts, or whatever, that take input from the
user and convert these into SQL statements.
Another design decision is to set level = "1 - Critical", "2 - Warning",
etc. It's nice for the end user too. We use it at work, and it works
nicely. Then Leif's original solution plus the index should work. You
could create an index the first char of table.level.
The problem with

select id,max(case level when ... end) from table b group by b.id

is that the system will still scan all rows and evaluate (case level ...
end) because it does not know that once it has seen a 'Critical' it can't
see anything higher. Plus, without a function or index or index on a
calculated column, it has no index to immediately locate the records of
importance, so it has to do a full table scan.
Still, maybe we could use collations. Something like

create table (
level varchar(10) not null character set collate <an appropriate name>
)

The <an appropriate name> is a collation where Critical comes first, then
Warning, then Info. But I don't know how to create new collations.

[Leif]

"Hans" <ni*********@zonnet.nl> skrev i en meddelelse
news:41***********************@news.xs4all.nl...

Hello group,

I have a table with the next contents.
It lists data about : Who sent what kind of message at what time.
For the Level column: The highest level is Critical, the middle is Warning, the lowest is Info.

Id Timestamp Level
1 12:00 Info
1 13:00 Warning
1 14:00 Critical
2 12:00 Info
2 13:00 Warning
2 14:00 Info
2 15:00 Warning
3 12:00 Critical
3 13:00 Warning
4 15:00 Info
5 15:00 Info
5 16:00 Warning

What I want to get out of his list is:
The first message with the highest level of each Id. So let the Level-column prevail above the Timestamp column,so I want to get this result:

1 14:00 Critical // The only 'Critical'
2 13:00 Warning // the first 'Warning'
3 12:00 Critical // The only 'Critical'
4 15:00 Info // The only one, so simple 5 16:00 Warning // The highest level, the only
'Warning'
So: First find the highest level of message of each Id and then find the
first one.

I hope the level is an enum, so you can sort on it.

Will this work?

select id,min(time),level from table a where (id,level) in (select
id,max(level) from table b group by b.id) group by a.id

Leif

[Hans]

"Leif" <id****@internet.dk> wrote in message
news:41**********************@nntp03.dk.telia.net. ..

"Hans" <ni*********@zonnet.nl> skrev i en meddelelse
news:41***********************@news.xs4all.nl...

Hello group,

I hope the level is an enum, so you can sort on it.
Nope.
Will this work?

select id,min(time),level from table a where (id,level) in (select
id,max(level) from table b group by b.id) group by a.id

That's a query that will cost a lot of time for multiple 1000s of records.

I already found a solution myself; I created a new table with the
'enum'-values of all levels ,MessageLevels:
Value Name
1 Info
2 Warning
3 Critical

And thenthis query:
SELECT
`SubQuery`.`Id_1`,
`MessageLevels`.`Name`
FROM (
SELECT
`MyTable`.`Id` AS `Id_1`,
`MessageLevels`.`Value` AS `Message_Value`
FROM
`MyTable`
INNER JOIN `MessageLevels` ON (`MyTable`.`Name` = `MessageLevels`.`Name`)
ORDER BY `Id`,
`Message_Value` DESC
) AS `SubQuery`
INNER JOIN
`MessageLevels` ON (`SubQuery`.`Message_Value` = `MessageLevels`.`Value`)
GROUP BY `SubQuery`.`Id_1`
Which in the subquery creates a table with all ID's with ALL their
messagelevels, sorted by ID, and then MessageLevel DESC,
And the from this subquery's result, id do GROUP BY Id, which causes only
the first record for each Id to be selected.

BTW This is not exactly what I asked in my first post, (No timestamp) but it
is indeed what I want ;-)

BTW-2 The query you propesed is running now for more than an hour, so that's
not really a good solution, but thanks anyway for thinking with me.

Gr, Hans

[Hans]

"Leif" <id****@internet.dk> wrote in message
news:41**********************@nntp03.dk.telia.net. ..

"Hans" <ni*********@zonnet.nl> skrev i en meddelelse
news:41***********************@news.xs4all.nl...

Hello group,

I hope the level is an enum, so you can sort on it.
Nope.
Will this work?

select id,min(time),level from table a where (id,level) in (select
id,max(level) from table b group by b.id) group by a.id

That's a query that will cost a lot of time for multiple 1000s of records.

I already found a solution myself; I created a new table with the
'enum'-values of all levels ,MessageLevels:
Value Name
1 Info
2 Warning
3 Critical

And thenthis query:
SELECT
`SubQuery`.`Id_1`,
`MessageLevels`.`Name`
FROM (
SELECT
`MyTable`.`Id` AS `Id_1`,
`MessageLevels`.`Value` AS `Message_Value`
FROM
`MyTable`
INNER JOIN `MessageLevels` ON (`MyTable`.`Name` = `MessageLevels`.`Name`)
ORDER BY `Id`,
`Message_Value` DESC
) AS `SubQuery`
INNER JOIN
`MessageLevels` ON (`SubQuery`.`Message_Value` = `MessageLevels`.`Value`)
GROUP BY `SubQuery`.`Id_1`
Which in the subquery creates a table with all ID's with ALL their
messagelevels, sorted by ID, and then MessageLevel DESC,
And the from this subquery's result, id do GROUP BY Id, which causes only
the first record for each Id to be selected.

BTW This is not exactly what I asked in my first post, (No timestamp) but it
is indeed what I want ;-)

BTW-2 The query you propesed is running now for almost 45 minutes, so that's
not really a good solution, but thanks anyway for thinking with me.

Gr, Hans

[Siemel Naran]

"Hans" <ni*********@zonnet.nl> wrote in message news:4108fb97$0$93324

"Leif" <id****@internet.dk> wrote in message

Will this work?

select id,min(time),level from table a where (id,level) in (select
id,max(level) from table b group by b.id) group by a.id

That's a query that will cost a lot of time for multiple 1000s of records.

BTW-2 The query you propesed is running now for more than an hour, so that's not really a good solution, but thanks anyway for thinking with me.

What is the exact query you ran? I think MySql supports function indexes,
so for example you could try to create index (id, case level when 'High'
then 3 ... end), or create index (id, if(level='High', 3, if(...))). Will
need to look it up, but have to run to work.

Also, what does the query plan say is the driving table and index?

[Siemel Naran]

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message
news:S%8Oc.151848

"Hans" <ni*********@zonnet.nl> wrote in message news:4108fb97$0$93324

"Leif" <id****@internet.dk> wrote in message

select id,min(time),level from table a where (id,level) in (select
id,max(level) from table b group by b.id) group by a.id

BTW-2 The query you propesed is running now for more than an hour, so

What is the exact query you ran? I think MySql supports function indexes,
so for example you could try to create index (id, case level when 'High'
then 3 ... end), or create index (id, if(level='High', 3, if(...))). Will
need to look it up, but have to run to work.
So I checked, and no, MySql 4.1 and even 5.0 I think, does not support
function indexes.
Also, what does the query plan say is the driving table and index?

The above is important, because it could mean the difference between a query
that returns right away, and another that takes hours.

In Leif's original query, I think you would need only one index. This
assumes driving by the inner query is the right thing for your situation
(which may or may not be the case), and that MySql will in this case indeed
drive by the inner query.

create index table_level on table (id, level);

But this only works when the level numbers are 1 for Critical, 2 for
Warning, 3 for Info. By contrast, sorting level as strings yields Critical,
Info, Warning -- which is not correct.
One solution is function indices, which some databases provide, but not
MySql. It would look like this:

create index table_level on table (id, case level when 'Critical' then 1 ...
end);
Most databases support triggers, but MySql only will in 5.1. It would look
something like this:

create trigger on table
for update
as update table set levelnum = case level when 'Critical' then 1 ... end;

create index table_level on table (id, levelnum);
Some databases also let you define calculated fields, so you'd say

create table (
level varchar(10) not null,
levelnum smallint(1) as case level when 'Critical' then 1 ... end,
index on table (id, levelnum);
);

But I don't think MySql does not let you do this either.
Of course, you could just add your levelnum column and when you insert or
update table.level, remember to update levelnum too. So you'd have to put
more work into the PHP or C++ scripts, or whatever, that take input from the
user and convert these into SQL statements.
Another design decision is to set level = "1 - Critical", "2 - Warning",
etc. It's nice for the end user too. We use it at work, and it works
nicely. Then Leif's original solution plus the index should work. You
could create an index the first char of table.level.
The problem with

select id,max(case level when ... end) from table b group by b.id

is that the system will still scan all rows and evaluate (case level ...
end) because it does not know that once it has seen a 'Critical' it can't
see anything higher. Plus, without a function or index or index on a
calculated column, it has no index to immediately locate the records of
importance, so it has to do a full table scan.
Still, maybe we could use collations. Something like

create table (
level varchar(10) not null character set collate <an appropriate name>
)

The <an appropriate name> is a collation where Critical comes first, then
Warning, then Info. But I don't know how to create new collations.