Need query to return "top 5"

[JeremyG]

I need a query that will return only 5 records containing the highest value in a field.

Specifically I need to query the EventLog with WMI and get the 5 most recent events. Normally this would be very easy, but WQL does not support ORDER BY. So basically it returns an un-ordered collection.

To avoid doing a large set of compares to find the most recent events in the collection, there MUST be a way to do a SQL query like this. No?

 

[NoCoolHandle]

I think you are mistaking "WQL" for "Microsoft SQL Server"..

Microsoft SQL Server (and most real realational databases that support ODBC level 2 commands) will support order by.

MSDE (sql server for 5 worker threads) will do everything you want and it is FREE!

 

[miq]

hi,

select TOP 5 * from [table_name]
order by [field_name] Desc

is a standard way to SQL server. And it will work with all versions of sql server / oracle.


bye
miq

 

[JeremyG]

Hmmm I dont think i was clear in my question. I actually DO mean WQL. WQL is a subset of SQL, and it's used in WMI programming. Unfortunately it doesnt support ORDER BY.

So if i have this Dataset:
Name Score
John 8
Tim 10
Jen 30
Joe 5
Tina 22
Eric 2
James 8

I want a query that will return:
Jen
Tina
Tim
John
James

...because those records have the 5 highest values in the Score field.

Thanks for the "TOP" command, i never knew about that, good to know....but the table I'm querying is not in the order of the feild i want to select from.

 

[JamesLean]

If WQL doesn't support ORDER BY, and has no equivalent method of returning an ordered resultset, then I guess you are going to have to do some fiddly coding in your app to get the results you need.

As this is a SQL Server forum, any advice given will obviously be based on the flavour of SQL it uses, namely T-SQL (which explains the slight confusion). You may be better off trying a forum/site which is more geared towards WMI/WQL to get better help with this problem.

--James

 

[jimmYLepp]

JeremyG, try the Microsoft: Active Server Pages (ASP) forum, it has helped me for most of my WMI problems.

jimmY