|
Not too hard, the basic principal is to create a query that gets the distinct line/form along with the min wgt and then join this back to the main table to get the final results.
My table was named "test" and I got the results you expect with this query:
select test.date,test.line,test.wgt,test.form
from test
inner join
(
select line,form,min(wgt) as minWgt
from test
group by line,form
) groupedTest
on test.line = groupedTest.line
and test.form = groupedtest.form
and test.wgt = groupedtest.minWgt
|
|
|
|
|
Excellent - just what I needed to point me in the right direction.
My actual query is much more complicated that the outline I gave, but I was able to expand your sample to complete the job.
Many thanks
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
All,
I have created LinkedServer and try to run a query. it is throwing error like
"
OLE DB provider "SQLNCLI" for linked server "SRCSVR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "SRCSVR" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
"
My SP_AddLinedServer is
"
exec sp_addlinkedserver
@server='SRCSVR',
@srvproduct= '',
@provider='SQLOLEDB',
@datasrc='SERVER=MyServer;UID=user;PWD=pass'
"
I tried to execute this Query
select Name from SRCSVR.DBNAme.dbo.TableName
I have enabled
Firewall
tcp/ip
Named Pipes
But still it comes.
Please let me help to overcome this problem.
Thanks
Praveen Kuamr K
|
|
|
|
|
Is the server you're trying to connect to, default instance. If not try adding instance name after server name (if i remember correctly, @datasrc='SERVER=MyServer\InstanceName;UID=user;PWD=pass')
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
I have a .sql script file and wanted to use the following code to write it into one of the database. How should I configure the connection string? I'm new to database programming.
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
FileInfo file = new FileInfo("C:\\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
|
|
|
|
|
|
I used the following string:
"Driver={SQL Native Client};Server=MACHINE_NAME\\SQLEXPRESS ;Database=C:\\tset.mdf;";
but ended up with exception saying "Driver" is an unsupported keyword and when I removed this keyword the unhandled exception occurred.
|
|
|
|
|
You should use the one SqlConnection (.NET) section
|
|
|
|
|
I am trying to compute the difference between two double precision columns, start_time and stop_time. I used this select statement:
Select (stop_time - start_time) AS Duration
From table;
The result I get back is a column name Duration with empty cells. What should be the correct syntax? Thanks.
|
|
|
|
|
What are datatypes of columns stop_time and start_time ?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
the columns are of type double.
|
|
|
|
|
Could it be that either stop_time or start_time is NULL. In that case the result would be NULL.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i am having some trouble with a correlated-subselect-statement (using mysql 5.0):
i have a table that looks simplyfied like this:
myTable:
--------------------------
ColID NameID ColDate
--------------------------
1 1 2008-10-01
2 1 2008-07-01
3 2 2008-06-11
4 3 2008-09-12
5 4 2008-03-15
6 2 2008-11-21
7 3 2008-08-19
8 4 2008-01-30
--------------------------
now i need for every nameID to select only the row with the latest date.
desired result:
--------------------------
ColID NameID ColDate
--------------------------
1 1 2008-10-01
4 3 2008-09-12
5 4 2008-03-15
6 2 2008-11-21
--------------------------
i tried the following select-statement:
SELECT * FROM myTable AS T1 WHERE ColDate=(SELECT MAX(T2.ColDate) FROM myTable AS T2 WHERE T1.NameID=T2.NameID)
but got the error message: #1064 - You have an error in your SQL syntax.
thanks for any help with this.
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
First I have to say that I haven't used MySQL so I'm guessing here.
Typically table aliases do not use AS keyword so perhaps the statement should be:
SELECT * FROM myTable T1 WHERE ColDate=(SELECT MAX(T2.ColDate) FROM myTable T2 WHERE T1.NameID=T2.NameID)
Also there may be problems if there are two records with the same date since then your subquery will return two rows and that's not acceptable when using = operator. If this happens, use for example both MAX(ColDate) and MIN(ColID) (or something similar) to get only one row from subwuery.
edit: Another option could be using GROUP BY ... HAVING ... clauses
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
modified on Tuesday, November 4, 2008 12:19 PM
|
|
|
|
|
Tried it with and without AS-keyword, but without any success. Still the same error message. I realized earlier, that the existence of two entries with the same date might cause trouble in this query, but that isn't the case here. I changed all entries in the table to show different days but I still get the error message.
So next thing tomorrow when I am not that sleepy any more: I will give GROUP BY...HAVING a try.
Thanks for your answer so far, I will come back, when I have a (hopefully) positive result.
|
|
|
|
|
I checked the syntax for MySQL 5 and everything in your example seems right.
However, if I understood you correctly, that's not the actual statement you use, so you may have a 'hidden' typo in the actual statement (for example missing parenthesis or so on). If still having problems with that version tomorrow, perhaps you can post the full statement.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I have try this in MySQL
<br />
select distinct nameid,<br />
(select t1.colid from mytable as t1 where t1.nameid=mytable.nameid limit 1) colid,<br />
(select t1.coldate from mytable as t1 where t1.nameid=mytable.nameid order by coldate desc limit 1) coldate<br />
from mytable
but result is this:
<br />
1 1 2008-10-01<br />
2 3 2008-11-21<br />
3 4 2008-09-12<br />
4 5 2008-03-15
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
That might be, what I am looking for (I am not sure, since I didn't describe the problem entirely in my first question - see my answer to SomeGuyThatsMe below). I tried that query, but still got an error message.
|
|
|
|
|
try
SELECT NameId, Max(Date)
FROM table
WHERE whatever your clause is
GROUP BY NameId
i think that will work, but i'm not sure if there are any differences between MySql and SqlServer for doing this kinda stuff, i havent used MySql
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
No, sorry, that didn't work. As I said, the table in my example is simplified: There are a few more columns, the important one called "LocationID". For every NameID I need the LocationID of the latest date, to join the result with further tables (NameID and LocationID being foreign keys from other tables, as you might have already guessed). So if I use the query proposed by you, I get a table with a row for every nameID, showing the latest date (as desired), but not the LocationID relevant to that date but the first LocationID of each NameID's group.
Uhm, I hope that was comprehensible?
But thanks for you answer.
|
|
|
|
|
i think it makes sense, you want the nameID, most recent date, and the locationID for the most recent date for a given name correct? If not let me know what you need to return and where those fields are and i can try to come up with something to help.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Yes, that's right. I need to find the most actual locations for a given list of names. I should have described this earlier.
Would be great, if you'd knew an answer. 
|
|
|
|
|
SELECT t1.NameId, Max(t1.DateTime), t2.Location
FROM Table1 t1
INNER JOIN Table2 t2
on t1.LocationId = t2.LocationId
where something
Group By t1.NameId, t2.Location
should work for you as long as your first group by is the nameId. it worked like that for my test i even selected a half dozen or so columns from another table.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
That one still didn't work. The problem is, when using MAX(ColDate) I get the latest date in that group but not the LocationID from the latest date's row, but the LocationID from each group's first row.
But don't bother, I found another solution to my problem (see below).
But thanks alot for your help.![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
Okay, since none of the solutions proposed above worked for me, I came up with something different.
This one's working fine:
SELECT s1.NameID, s1.LocationID, s1.ColDate
FROM myTable s1
LEFT JOIN myTable s2 ON s1.NameID= s2.NameID AND s1.ColDate < s2.ColDate
WHERE s2.NameID IS NULL
Returns for every NameID only the LocationID with the most actual date, exactly as desired.
Thanks to Mika Wendelius, Blue_Boy and SomeGuyThatIsMe for their answers and the time they spent trying to solve my problem.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
