getting the top 2 records
I'm using ansi SQL (not MS-SQL)...
I'm trying to return the top two records from a list:
eg.
SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
How do I do the above but only select the top two newest alarms? I can only
fire one Select command as it is being passed as a URL...
Any help would be appreciated...
LIMIT is not available, FIRST is but I think it is the function FIRST()...
Cheers!
Mike B
[494 byte] By [
Mike B] at [2007-11-9 21:09:28]
# 1 Re: getting the top 2 records
"Mike B" <mbarham@enbridgeibt.com> wrote in message <news:3d528101$1@10.1.10.29>...
> I'm using ansi SQL (not MS-SQL)...
> I'm trying to return the top two records from a list:
>
> eg.
> SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
>
> How do I do the above but only select the top two newest alarms? I can only
> fire one Select command as it is being passed as a URL...
>
> Any help would be appreciated...
> LIMIT is not available, FIRST is but I think it is the function FIRST()...
Here's one way:
select max(alarms.alarmtext) as alarmtext, max(alarms.alarmtype) as alarmtype,
alarms.alarmtime
from alarms, alarms as a
where alarms.alarmtime <= a.alarmtime
group by alarms.alarmtime
having count(a.alarmtime) <= 2
It would be nice to know whether alarms.alarmtime is a candidate key,
though. (ie, can you create a unique index on only alarms.alarmtime?)
--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
# 2 Re: getting the top 2 records
"Mike B" <mbarham@enbridgeibt.com> wrote:
>
>I'm using ansi SQL (not MS-SQL)...
>I'm trying to return the top two records from a list:
>
>eg.
>SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
>
>How do I do the above but only select the top two newest alarms? I can
only
>fire one Select command as it is being passed as a URL...
>
>Any help would be appreciated...
>LIMIT is not available, FIRST is but I think it is the function FIRST()...
>
>Cheers!
>
>Mike B
>
# 3 Re: getting the top 2 records
How about <i>SELECT <b>TOP 2</b> AlarmType,AlarmText,AlarmTime FROM ALARMS
ORDER BY AlarmTime DESC</i> ?
Is that an MS SQL only convention?
"Mike B" <mbarham@enbridgeibt.com> wrote:
>
>I'm using ansi SQL (not MS-SQL)...
>I'm trying to return the top two records from a list:
>
>eg.
>SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
>
>How do I do the above but only select the top two newest alarms? I can
only
>fire one Select command as it is being passed as a URL...
>
>Any help would be appreciated...
>LIMIT is not available, FIRST is but I think it is the function FIRST()...
>
>Cheers!
>
>Mike B
>
# 4 Re: getting the top 2 records
Yes, it has TOP in it. Unfortunately, I don't have TOP to work with in this
cut down version of SQL I am using.
mb
"RamblinWreck" <GT@GaTech.edu> wrote:
>
>How about <i>SELECT <b>TOP 2</b> AlarmType,AlarmText,AlarmTime FROM ALARMS
>ORDER BY AlarmTime DESC</i> ?
>
>Is that an MS SQL only convention?
>
>
>"Mike B" <mbarham@enbridgeibt.com> wrote:
>>
>>I'm using ansi SQL (not MS-SQL)...
>>I'm trying to return the top two records from a list:
>>
>>eg.
>>SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
>>
>>How do I do the above but only select the top two newest alarms? I can
>only
>>fire one Select command as it is being passed as a URL...
>>
>>Any help would be appreciated...
>>LIMIT is not available, FIRST is but I think it is the function FIRST()...
>>
>>Cheers!
>>
>>Mike B
>>
>
Mike at 2007-11-11 23:56:30 >
# 5 Re: getting the top 2 records
Thanks for the response.
Hmmm... this didn't work either. I receive a web formatted table as a response
from the server that i send the URL request to... the request is in the following
format:
http://ip/appdb/query?sql=select%20TSTAMP,MESSAGETEXT,EVENTTYPE,EVENTSWID%20from%20EVENT.UNACKEDALARMS%20o rder%20by%20TSTAMP%20desc
%20 is the hex for a space... I just want the first n records from this.
Tricky.
Their interpreter limits things for me.
mb
"Joe \"Nuke Me Xemu\" Foster" <joe@bftsi0.UUCP> wrote:
>"Mike B" <mbarham@enbridgeibt.com> wrote in message <news:3d528101$1@10.1.10.29>...
>
>> I'm using ansi SQL (not MS-SQL)...
>> I'm trying to return the top two records from a list:
>>
>> eg.
>> SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
>>
>> How do I do the above but only select the top two newest alarms? I can
only
>> fire one Select command as it is being passed as a URL...
>>
>> Any help would be appreciated...
>> LIMIT is not available, FIRST is but I think it is the function FIRST()...
>
>Here's one way:
>
>select max(alarms.alarmtext) as alarmtext, max(alarms.alarmtype) as alarmtype,
> alarms.alarmtime
>from alarms, alarms as a
>where alarms.alarmtime <= a.alarmtime
>group by alarms.alarmtime
>having count(a.alarmtime) <= 2
>
>It would be nice to know whether alarms.alarmtime is a candidate key,
>though. (ie, can you create a unique index on only alarms.alarmtime?)
>
>--
>Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
>WARNING: I cannot be held responsible for the above They're coming
to
>because my cats have apparently learned to type. take me away,
ha ha!
>
>
Mike at 2007-11-11 23:57:34 >
# 6 Re: getting the top 2 records
"Mike" <mbarham@enbridgeibt.com> wrote in message <news:3d52a535$1@10.1.10.29>...
> Thanks for the response.
>
> Hmmm... this didn't work either.
Not all SQL interpreters like the "as" keyword when creating
aliases, so try taking it out, at least until you can get
*something* to work. You can always add the spit-n-polish
back in later...
select max(alarms.alarmtext), max(alarms.alarmtype), alarms.alarmtime
from alarms, alarms a
where alarms.alarmtime <= a.alarmtime
group by alarms.alarmtime
having count(a.alarmtime) <= 2
> I receive a web formatted table as a response
> from the server that i send the URL request to... the request is in the following
> format:
>
>
http://ip/appdb/query?sql=select%20TSTAMP,MESSAGETEXT,EVENTTYPE,EVENTSWID%20from%20EVENT.UNACKEDALARMS%20o rder%20by%20TSTAMP%20desc
>
> %20 is the hex for a space... I just want the first n records from this.
> Tricky.
>
> Their interpreter limits things for me.
Is there a manual on-line somewhere, so we can find out just
how "cut down" this query interpreter might be? Can it do
subqueries?
select alarms.alarmtext, alarms.alarmtype, alarms.alarmtime
from alarms
where alarms.alarmtime = (select max(alarmtime) from alarms)
or alarms.alarmtime = (select max(alarmtime) from alarms
where alarmtime < (select max(alarmtime) from alarms))
--
Joe Foster <mailto:jlfoster%40znet.com> On the cans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
# 7 Re: getting the top 2 records
Are you using ado? If so use the recordset extended properties i am not sure
wich one but i know it is there :)
jason
"Mike B" <mbarham@enbridgeibt.com> a crit dans le message news:
3d528101$1@10.1.10.29...
>
> I'm using ansi SQL (not MS-SQL)...
> I'm trying to return the top two records from a list:
>
> eg.
> SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
>
> How do I do the above but only select the top two newest alarms? I can
only
> fire one Select command as it is being passed as a URL...
>
> Any help would be appreciated...
> LIMIT is not available, FIRST is but I think it is the function FIRST()...
>
> Cheers!
>
> Mike B
>
# 8 Re: getting the top 2 records
This will be rather ugly, but I believe it will work.
SELECT alarmtype, alarmtext,alarmtime
FROM alarms
WHERE alarmTime >= (SELECT Max(alarmtime) FROM alarms WHERE AlarmTime <>
(Select Max(alarmTime) FROM alarms))
ORDER BY alarmtime desc
In essance it says, get the alarm type text and time from the alarms
table provided that the alarm time is greater than or equal to
the Maximum alarm time excluding the most recent alarm time.
Now, if you know that alarms can occur at the same time, this ain't gonna
work ;)
Hope this helped,
Q*bert
@#&$#
"Mike B" <mbarham@enbridgeibt.com> wrote:
>
>I'm using ansi SQL (not MS-SQL)...
>I'm trying to return the top two records from a list:
>
>eg.
>SELECT AlarmType,AlarmText,AlarmTime FROM ALARMS ORDER BY AlarmTime DESC
>
>How do I do the above but only select the top two newest alarms? I can
only
>fire one Select command as it is being passed as a URL...
>
>Any help would be appreciated...
>LIMIT is not available, FIRST is but I think it is the function FIRST()...
>
>Cheers!
>
>Mike B
>
Qbert at 2007-11-12 0:00:42 >
