SEARCH YOUR SOLUTION HERE  

need help with complicated select distinct query

I have a table called gms_tests that keeps track of the results of tests run against servers. Columns are hostname, testname, status and time. The tests are run (about 5 against each server) every half hour, and the results are put in the table.

I need to be able to search this table and obtain the most recent result for each test. For example, I need something similar to:
select distinct hostname,testname from gms_tests order by time desc
but I also need to be able to see the time and status columns.

I've tried something like this:
select hostname,testname,status,max(time) from gms_tests group by hostname,testname

but the data returned in the status column is not correct - it doesn't match the status that corresponds with the latest test time.

Any ideas what might work I'm using MySQL version 3.23.58, so I can't use subqueries.

Posted On: Monday 19th of November 2012 11:56:51 PM Total Views:  315
View Complete with Replies




Related Messages:

I need to write a query in SQL   (119 Views)
im completely new 2 this so it will probably seem piss easy to any1 reading this but heres the query i have to do thanx Consider the database schema on the relations (where the key attributes are followed by a *) COURSES(Number*, Faculty, CourseName) STUDENT(Number*, Surname, FirstName, Faculty) EXAMS(Student*, Course*, Grade, Date) Relation EXAMS stores the registration number(attribute Student) of students that passed the exam for a given course (attribute Course is the number of course, not the name of the course), the grade and the date of the exam. Write the query that finds the surnames of students and the exams they passed (number of course and corresponding grade) in SQL, Relational Algebra, and Relational Calculus.
I need help to a SQL-query to an ACCESS-database   (91 Views)
Originally posted by : 4texx (4texx@tm1.at) I want to make a SQL-query to an ACCESS-database!I need data from two different tables. The first one is called "EL_TABELLE" and the second one "ET source"And I have problems with the "ET Source" table, because its name contains a space character.I think thats the reason for this error message:Microsoft OLE DB Provider for ODBC Drivers-Fehler '80040e10' This error occurs only with Microsoft Access when one of the column names specified in a select statement does not exist in the table being queried. And there is no possibility for me to change the name of the table, because it's not my databaseIf there is anybody who knows how to solve thise problem, please help me!
Strage qurey needed   (119 Views)
I need to come up with a qurey that im having trouble with. I need to pull all emails of users who are not kicked out. There are two tables one is ws_member the other is banned. I dont know why the head programer set it that way but thats what i got to work with. I need a qurey that will pull all the emails that are in ws_member that are not in banned. Any suggustions
need help to find   (155 Views)
Hi I need help to find SQL personal edition port . on both client and server network utility, the default port is 1433, it work fine, and one day go down. I try telnet localhost 1433 just got flash. what can I do now Thank you advance.
i need some help in SQL server 2000 Query   (123 Views)
i am doing a real time project. in that i need an sql query. plase go through the data in the table and help me to get the result. Data in the table is : BulbNo State ChangeTime 2 OFF 19/08/2004 21:23:10 2 ON 19/08/2004 21:22:10 2 OFF 19/08/2004 21:20:10 4 ON 19/08/2004 21:18:50 4 OFF 19/08/2004 21:17:00 7 OFF 19/08/2004 21:13:30 7 ON 19/08/2004 21:12:50 7 OFF 19/08/2004 21:12:40 9 ON 19/08/2004 21:12:30 9 OFF 19/08/2004 21:11:00 10 ON 19/08/2004 21:12:30 10 OFF 19/08/2004 21:11:00 2 ON 19/08/2004 20:22:10 2 OFF 19/08/2004 20:20:10 5 ON 19/08/2004 20:12:30 5 OFF 19/08/2004 20:11:00 7 ON 19/08/2004 20:12:50 7 OFF 19/08/2004 20:12:40 9 ON 19/08/2004 20:12:30 9 OFF 19/08/2004 20:11:00 The result I am expecting is like this : To show the Previous and the Present states of each bulb, with its state change timings and the time diff. between these States B.No Cur. State Cur. Time Prev. state Prev. Time Time Diff.(in sec.) 2 OFF 19/08/2004 21:23:10 ON 19/08/2004 21:22:10 60 4 ON 19/08/2004 21:18:50 OFF 19/08/2004 21:17:00 110 5 ON 19/08/2004 20:12:30 OFF 19/08/2004 20:11:00 90 7 OFF 19/08/2004 21:13:30 ON 19/08/2004 21:12:50 40 9 ON 19/08/2004 21:12:30 OFF 19/08/2004 21:11:00 90 10 ON 19/08/2004 21:12:30 OFF 19/08/2004 21:11:00 90 thanx in adv. Mohan G
Help needed please   (147 Views)
I'm developing a database in sql and i'm faced with this problem in a procedure: Server: Msg 156, Level 15, State 1, Procedure prospect, Line 35 Incorrect syntax near the keyword 'order'. the line is: declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where (cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area=@area and preco!=@preco) order by preco asc)) The preco variable is of typ money in my database, and the @preco variable is of type float, i use the same like before to obtain is there is a line in the database with the parameter that i want and it doesn't have no error, if i take the order by state theres no error. If anybody could help i thanks in advance. I can't finish my work because of this procedure, i'm stuck If it's of any help this the full procedure: create procedure prospect @preco float,@area float,@local varchar(30),@reg varchar(30),@tipolo varchar(20),@acab varchar(20),@estado varchar(20),@tipo varchar(30) as declare @tmplocal as int declare @tmpreg as int declare @tmptipolo as int declare @tmpacab as int declare @tmpest as int declare @tmptipo as int declare @codtmp as int declare @precotmp as money declare @areatmp as float if ((select cod_local from localizacao where localizacao = @local)>0 and (select cod_regiao from regiao where nome = @reg)>0 and (select cod_tipologia from tipologia where tipologia = @tipolo)>0 and (select cod_acabamentos from acabamentos where acabamentos = @acab)>0 and (select cod_estado from estado where estado=@estado)>0 and (select cod_tipo from tipos where tipo=@tipo)>0)begin select @tmplocal=cod_local from localizacao where localizacao = @local select @tmpreg=cod_regiao from regiao where nome = @reg select @tmptipolo=cod_tipologia from tipologia where tipologia = @tipolo select @tmpacab=cod_acabamentos from acabamentos where acabamentos = @acab select @tmpest=cod_estado from estado where estado=@estado select @tmptipo=cod_tipo from tipo where tipo=@tipo --Para todos os parametros create table #prospects(cod_imov int,area float, preco money) if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and preco=@preco and area=@area) begin declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and preco=@preco and area=@area) open reg_cs fetch next from reg_cs into @codtmp,@areatmp,@precotmp while @@fetch_status = 0 begin insert into #prospects(cod_imov,area,preco) values (@codtmp,@areatmp,@precotmp) fetch next from reg_cs into @codtmp,@areatmp,@precotmp end close reg_cs deallocate reg_cs end --Para todos menos o preo if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area=@area and preco!=@preco ) begin declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area=@area and preco != @preco order by preco asc) open reg_cs fetch next from reg_cs into @codtmp,@areatmp,@precotmp while @@fetch_status = 0 begin insert into #prospects(cod_imov,area,preco) values (@codtmp,@areatmp,@precotmp) fetch next from reg_cs into @codtmp,@areatmp,@precotmp end close reg_cs deallocate reg_cs end --para todos menos o preco e a area if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area@area and preco!=@preco ) begin declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area!=@area and preco != @preco order by (preco/area) asc) open reg_cs fetch next from reg_cs into @codtmp,@areatmp,@precotmp while @@fetch_status = 0 begin insert into #prospects(cod_imov,area,preco) values(@codtmp,@areatmp,@precotmp) fetch next from reg_cs into @codtmp,@areatmp,@precotmp end close reg_cs deallocate reg_cs end --para todos menos o preco, area e localiza o if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local!=@tmplocal and area!=@area and preco!=@preco ) begin declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local!=@tmplocal and area!=@area and preco != @preco order by cod_local desc,preco,area) open reg_cs fetch next from reg_cs into @codtmp,@area,@precotmp while @@fetch_status = 0 begin insert into #prospects(cod_imov,area,preco) values(@codtmp,@areatmp,@precotmp) fetch next from reg_cs into @codtmp,@areatmp,@precotmp end close reg_cs deallocate reg_cs end end else print 'Campos introduzidos no estam correctos' Ruben Monteiro
Update sql help needed   (122 Views)
UPDATE one, two SET one.Model = two.Model WHERE one.asn = mainlist.asn; This will update the Model field with the new data, however can i replace Model with something which will update all fields in the tables, bearing in mind the tables are the same format
I need Help!   (108 Views)
Originally posted by : Johnno (scripts@johnno.co.uk)I'm very new to ASP, and have recently tried to create a search engine for my site through a database. However, I'm having quite a few problems;basically, I can't seem to get it to work! If anyone knows of anything that might help, please tell me!
SQL Query Problem (Advanced Help needed!)   (102 Views)
, I have three tables, Blog, Cat, and BlogCatXRef: Blog Column Names: BlogID, BlogName Cat Column Names: CatID, CatName BlogCatXRef Column names: BlogCatID, BlogID, CatID I built these tables so that I could cross reference multiple categories per blog. I need to create an SQL Query that will give me three columns: Query Column Names: CatID, CatName, BlogID And populate these columns with ALL the categories (i.e. ID's 1 to 8) I can get the CatID and CatName quite easily by using a standard SELECT Query. What I need is the BlogID column to show either the BlogID number or nothing (i.e. NULL) but still have ALL the categories listed (i.e. ID's 1 to 8) when I pass a BlogID of 1 to the query. I'm finding this rediculously difficult. So say that, BlogID 1 is related to CatID 1, 2 and 3, BlogID 2 is related to CatID 1, 4 and 5 via the BlogCatXRef table. I want my query to show CatID 1, 2, 3, 4, 5, 6, 7 and 8, the relevant CatNames, and in the BlogID column the BlogID variable that I pass to the query (i.e. 1) or NULL for all the other Categories. With everything that I've tried, it either shows ONLY the categories that BlogID 1 is related to, or categories that BlogID 1 is related to and NULL values, missing out the categories that BlogID 2 is related to. I need it to show ALL the categories (whether related to BlogID 1 or BlogID 2 or Nothing) and IF THEY ARE related to BlogID 1, put the BlogID number in the BlogID column. I think I've made sense there. I know this is possible, it has to be... I'm just tearing my hair out as to how. I've tried Left Outer Joins, Inner Joins, Sub Queries etc, but I don't know how to put them together to get my result. Help me, please, before I have an embolism.
I need SQL Query Help   (107 Views)
Sir, We are try to insert query like this Insert into Table_1 (smDate,Description) Values ('Thu, Feb 15, 2007 03:00 PM', ' It's Just for Testing') This is Exact Query about this... We know about MS ACCESS it has Built in function like Format('Thu, Feb 15, 2007 03:00 PM','mm/dd/yyyy HH:MM') But I am new to SQL. I dont know the The Date Format built in function in SQL Server. How can i Insert this Quesry to SQL Table and also How can insert "it's " this type of words into SQL.. Can you help me this is very helpful for my project
Novice need help!!!   (127 Views)
Im mark. I've downloaded msde2000A from the internet. I don't know how to install, a message comes out saying that strong sa password is required. Please help me, i don't know how to use this program. I gonna use this for my thesis this sem, i gonna use msde2000A with VB.net to create a Human Resources Information System(HRIS). Please provide me help! Thank you!!!
need sql line   (120 Views)
hello all i want some help here plz . i make asp script for books down load i want to make i want to get the largest 10 downloads plz tell me what the command i must using .
SQL Statement searching 1 table for a lot of diff fields :\ need help   (217 Views)
SELECT * FROM Results_Public WHERE Animal_Identifier LIKE 'varanimalid%' AND Breed LIKE 'varbreed%'AND Gender LIKE 'vargender1' AND ' checkbox if selected show male Gender LIKE 'vargender2' AND ' checkbox if selected show female BUT if both are selected show all records for both male & female right now it shows all if none selected & none if both selected ShortName LIKE 'varshortname%' AND RegName LIKE '%varregname%' AND RegNumber LIKE 'varregnumber%' AND
Help needed with error '80040e57'   (91 Views)
I have the following update. I know the error is pertaining to fields shiftbegin and shiftend- I tested update and it worked without these 2 fields. They are updating an access database with field type medium time. I have tried delimiting # and many other attempts and nothing is working. Can anyone give me an example of updating an access database time only (not date and time) field rs.Open SQL, my_Conn SQL = "UPDATE schedule SET shift_dte = '" & Request.Form("dte") & _ "' ,shiftPerson = '"& Request.Form("pname") & _ ", Category =" & Request.Form("cathid") &_ " shiftID =" & Request.Form("shiftsel") & _ ", shiftBegin = " & Request.Form("sftbeg") &_ ", shiftEnd = " & Request.Form("sftend") & _ "' WHERE id = " & Request.Form("ID") my_conn.Execute SQL Microsoft JET Database Engine error '80040e57' The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
need some SQL code, download my samplepage and find my problem.   (135 Views)
Originally posted by : tim (timstark01@hotmail.com)I'm trying to get a list menu to display in repeat region format. I've made a sample database and sample page you can download. If you get it working "cheers!!" email me a copy.you can access the sample page here:http://miona.com/listmenus/tim
I need Correct SQL QUERY   (204 Views)
Code: ----------- i just wanna know if the above codes are correct on the where clause... i want to compare the year and month in my database to the current year and month. this is the format of tsyear in database - 2008 and the tsmonth is number month, like JULY - 7 thats why i used: Code: i need the correct SQL query to process the correct output sorted by current month and year today. tnx!
Hi, I need you help.....on MLM   (106 Views)
Even i am working on MLM project...i would appreciate if you provide me the details of how to create the database and how to use this using C#/ASP.NET. I have to extract till 12th level from any current node..... Please help me.....
Newbie need help with SQL   (159 Views)
I 'm working on a project and i can't seem to figure out how to do the queries, i have attached the database file. Basically i need to finish question #3 and then complete #4 completed. They are completed using Oracle. Any help is surely appreciated. The expected output is listed below. *******************Questions****************** Question 3 Give the name of the pet(s) that has visited the vets on the most occasions. Output only the pet name(s). Question 4 Give the names of the two pets that have cost the most in vets bills. Output only the pet name(s). Hint: you will need to research the use of the rownum column for it.
i need a form that replies back to the user and writes data to the access file   (120 Views)
Originally posted by : skoja (sarajevo99@hotmail.com)did anyone came accross with the form that does just that
need to modify date format in a table   (80 Views)
I am wondering how (if at all) with Oracle/SQL i can modify a column of a table to have a different format, ie. there is a column DATE with format 04-JUN-99 (which is obviously DD-MON-YY) but i need this format to be changed (altered/updated) to format: Month DD, YYYY... can someone plz help me