|
|
Hi,
I have the following table t
ID, M_ID, S_ID, Date
1 , 2 , 7 , 2005-05-06 13:45:56:000
2 , 2 , 7 , 2005-05-06 13:45:58:000
3 , 2 , 8 , 2005-05-06 13:46:56:000
4 , 2 , 9 , 2005-05-06 13:46:57:000
The only thing differing row 1 and 2 is the date.
How can i perform a "distinct select" on the 3 first columns (ID, M_ID, S_ID) i.e ignoring date.
The result i want is
ID, M_ID, S_ID, Date
1 , 2 , 7 , 2005-05-06 13:45:56:000
3 , 2 , 8 , 2005-05-06 13:46:56:000
4 , 2 , 9 , 2005-05-06 13:46:57:000
Niclas Ahlqvist
|
|
|
|
|
select ID, M_ID, S_ID, min(Date)
from t
group by ID, M_ID, S_ID
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hehe easy enough..
thanks..
nic
|
|
|
|
|
I am having a simular problem as posted above but I want to Min/Max data other than a date field and i'm getting mixed data.
select username AS UserID, domainname AS Domain, dt AS Date from testtable
gives me:
UserID, Domain, Date
kt001, c_domain, 2/20/2006
kt001, p_domain, 2/15/2006
SELECT username AS UserID, MAX(domainname) AS Domain, MAX(dt) AS Date
FROM testtable
GROUP BY username
gives me:
UserID, Domain, Date
kt001, p_domain, 2/20/2006
Any clue?
|
|
|
|
|
Your getting exactly what you ask for! Which in this case is what's the MAX of the domain column and the MIN of the date column. I think what you are after is the row of data, that is identified by the MAX of the domain column. To do that you need to use a subquery or a view. Here's using a subquery.
select tt.userid, tt.domain, tt.date
from ( select m.userid, max(m.domain)
from testtable m
group by m.userid
) tmp
, testtable tt
where tt.userid = tmp.userid
and tt.domain = tmp.domain
;
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi!
I have some problems with my typed dataset, the dataset contains several datatables and the problem occurs when i try to load the dataset with the ReadXml function. The problem is that some of the tables won't load properly. For example:
<br />
MyDataSet ds = new MyDataSet;<br />
ds.ReadXml("ds.xml");<br />
The table table1 in ds will be empty, even if the xml-file contains data that belongs to table1.
<br />
MyDataSet ds = new MyDataSet;<br />
ds.table1.ReadXml("table1.xml");<br />
Doesnt work either, but this will work:
<br />
MyDataSet ds = new MyDataSet;<br />
MyDataSet.table1 tb1 = new MyDataSet.table1();<br />
tb1.ReadXml("table1.xml")<br />
ds.table1.Merge(tb1);<br />
Any ideas what the problem is?
/Jesper Miller
|
|
|
|
|
Hi,
I'm populating a datatable from a database file and then populating a combobox with it:
ComboBox1.DataSource = myDataTable;
ComboBox1.DisplayMember = "Name";
ComboBox1.ValueMember = "NameID";
The problem is:
I need to have more than one hidden value. I would like to have two hidden values in ValueMember. I thought maybe I could combine two columns (split with a comma) and then use an array to get the right value via split. Is it possible to commbine columns at the ValueMember?
ComboBox1.ValueMember = "NameID" + ',' + "IsMarried";
Nothing I tried like this works, so I'm guessing you can only assign ONE column to the ValueMember?
I thought this might be true so I tried to combine the two columns at the sql line:
NameID + ',' + IsMarried AS Name_IsMarried
This did not work as well, I think because I'm trying to combine a string with a boolean column.
Any help would be greatly appreciated!
Ron
|
|
|
|
|
Hi,
Use this in ur SQL query
SELECT NameID + ','+ convert(nvarchar,IsMarried ) Name_IsMarried
I hope this will help you...
Regards,
Ritesh
|
|
|
|
|
Hi Ritesh,
Thanks for the advice!
I tried the convert, but I'm getting this error...
ERROR: Undefined Function 'Convert' in Expression
OleDbCommand("SELECT convert(nvarchar,NameID) + ',' + convert(nvarchar,IsMarried) AS Name_IsMarried, myConnection);
Any idea why I would get this error?
Thanks!
Ron
|
|
|
|
|
You can access more than just the 2 values of the combobox, every item shown in the combobox is a datarow (if binded to a datatable), if you need another columnvalue just get the current row with
Dim r As DataRow = Me.cboArticulos.SelectedItem
then you can access any column of the current row with r.item("Columnname")
im not sure if this is what you are looking for
Alexei AR
Theres always a better way
Look for it 
|
|
|
|
|
Hi Alexie,
Thanks for the advice, I'm new to programming and havn't tried binding yet.
I'm trying to set it up, but I use C#. Are you able to convert your code
to C#? I tried this, but it did not work.
comboBox1.DataBindings.Add("SelectedValue", myDataTable, "Store.Supplier");
//This code must be incorrect, it crashes the event (item selected)
DataRow myRow = (DataRow)myDataTable.SelectedItem;
Thanks for your help,
Ron
|
|
|
|
|
ComboBox1.DataSource = myDataTable;
ComboBox1.DisplayMember = "Name";
ComboBox1.ValueMember = "NameID";
that code is correct, you can bind it like that
Just whenever you want to acces any other value of the current row (the one in the combobox), just declare a datarow with the combobox.selecteditem:
dim R as datarow = me.combobox.selecteditem, then just access any column you need with R.item("ColumnName")
Alexei AR
Theres always a better way
Look for it
|
|
|
|
|
hi, my Date field in the database formated as 2/15/2006 11:40:46 AM . i try to manually give a date (no) but it give me error. the error come from myReader!. help me to correct, thanks
no = "2152006"
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String
myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()
Author = myReader("Author")
myReader.Close()
myConn.Close()
lblShow.Text = Subject
-- modified at 4:41 Sunday 19th February, 2006
|
|
|
|
|
First and foremost: Don't inject values into the SQL string. here's why[^]
Second: You would be better using an ExecuteScalar for this than using ExecuteReader. This is because you are only receiving one value and ExecuteScalar is designed for retrieving one value (the first column of the first row).
Thirdly: You may wish to consider layering your application. It is bad practice to have database code in the same method as something setting user interface controls. For more information search the internet for the Layering Pattern[^]
sebastian yeok wrote: my Date field in the database formated as 2/15/2006 11:40:46 AM .
No it isn't. What you are seeing is a localised version of the date coming from the SQL Server. This has nothing to do with the way it is stored. So....
Lastly, use a proper DateTime object and pass it to SQL Server as a parameter. That way you do not have to deal with the formatting of the date.
Dim no As DateTime = New DateTime(2006, 2, 15)
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String
myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE [Date] = @date";
myCmd.CommandText = strSQL
' The parameter is added so that (1) you don't inject the value into the SQL String and
' (2) you don't have to worry about the formatting of the date as the framework will
' do that for you. Notice how @date corresponds to the @date in the strSQL above.
myCmd.Parameters.Add("@date", no)
myConn.Open()
myCmd.Connection = myConn
' Use execute scalar. Remember to check for DBValueNull in case no rows were found
' and to cast the result to what ever type Author is.
Author = myCmd.ExecuteScalar()
myReader.Close()
myConn.Close()
lblShow.Text = Subject
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Colin Angus Mackay,help me more.
1) How to display the "author" using the lblShow.
2) in this, it retrive one record, how about retrive more records? example SELECT * FROM Booktbl WHERE Date = @date". let say 10 records, how to display it?
3) how about retrive data between 2005/5/15 and 2006/5/15
what my problem is 2) and 3), retrive data from Booktbl between the date and display all the selected data.
very appreaciate
|
|
|
|
|
sebastian yeok wrote: 1) How to display the "author" using the lblShow.
If you are referring to how the layering pattern works then you put all your data access in one class (or a group of classes dedicated to data access) and call a method on the class. This removes the data access code from the user interface class. If you design it correctly it also means that at some point in the future you can swap out the data access classes and implement a new set of classes (that have the same public interface) that access a different database.
sebastian yeok wrote: in this, it retrive one record, how about retrive more records?
Go back to using the data reader. From the code you originally supplied you were only ever reading one column and one row from the database - so it would appear to be a good candidate for using ExecuteScalar()
sebastian yeok wrote: let say 10 records, how to display it?
I don't know enough about your intended user interface to answer that.
sebastian yeok wrote: how about retrive data between 2005/5/15 and 2006/5/15
Change you SQL to handle a date range. For example:
SELECT Columns
FROM MyTable
WHERE DateColumn >= @startDate
AND DateColumn <= @endDate
And pass in the parameters for @startDate and @endDate. Remember that a DateTime object for a date only sets the time to midnight. So, for the end date, you might want to set the time element as well to 23:59 to ensure you get results that occur on the end date.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
myCmd.Parameters.Add("@startDate", sDate)
myCmd.Parameters.Add("@endDate", eDate)
is't correct i do like this?
10 records means, retrive 10 rows of record or more.
author date
a ....
b ...
and so on
|
|
|
|
|
sebastian yeok wrote: myCmd.Parameters.Add("@startDate", sDate)
myCmd.Parameters.Add("@endDate", eDate)
is't correct i do like this?
Assuming your SQL Statement now contains a WHERE clause that uses the parameters @startDate and @endDate then I would assume it is correct.
sebastian yeok wrote: 10 records means, retrive 10 rows of record or more.
 The context of this statement has been lost. What does this refer to? (You may find it useful to use the "Quote Selected Text" button to insert a quote from the previous post so that the context of statements is not lost.)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hello,
I have made a search but could not find how to indent correctly SQL statements.
For instance;
SELECT * FROM a WHERE att1 IN ( SELECT att8 FROM b WHERE att2 = 10 AND att3 = 20 AND att4 = 30 GROUP BY att8 HAVING count(*) > 3 )
How this sample statement can be indented correctly(standard way)?
Any opinion or any article would be appriciated.
Kind Regards,
Sarp
|
|
|
|
|
I've not come across a standard way of doing this. My particular scheme is something like this:
SELECT Column1, Column2, Column3
FROM MyTable AS mt
INNER JOIN SomeOtherTable AS sot ON mt.pk = sot.fk
INNER JOIN (SELECT fk, ColumnA, ColumnB, ColumnC
FROM InnerTable
WHERE ColumnD = 10) AS it ON mt.pk = it.fk
WHERE mt.Column4 IS NOT NULL
ORDER BY Column2;
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I write my SQL like this:
SELECT
filed1,
field2
FROM
table1,
table2
WHERE
condition 1
AND
contidion 2
ORDER BY
field
LIMIT
1
DESC
Q:What does the derived class in C# tell to it's parent?
A:All your base are belong to us!
|
|
|
|
|
Colin Angus Mackay wrote: My particular scheme is something like
I do the same here. My old Oracle book from my undergraduate database course does it this way.
PJC
|
|
|
|
|
Thanks all for your opinions.
|
|
|
|
|
hi, i have to question here hope someone can help me.
1) INSERT INTO table2 (col1, col2, col3....)
SELECT col1,col2, col3
FROM table1
WHERE someting.....
the select statement used the .ExecuteReader and the insert statement used .ExecuteNonQuery how to join this both used in question (1)? guide me step by step.
2) by refer to question (1), how to move 1000 or more rocord from table1 to table2 ?. how to i store the 1000 records from table1. is there any solution to this problem.
anyone help appreciate! thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
