|
You can use following query
select max(no),name from trial1
group by name
If have any questions, thn let me know
Dipti Jadhav
|
|
|
|
|
One post is enough.
Regards,
Satips. ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus
|
|
|
|
|
The answer for ur question is here
select sno,name,[desc] from trial1
where convert(varchar(50),sno)+name in(select convert(varchar(50),max(sno))+name from trial1
group by name)
Please let me know, if u have any questions/comments.
|
|
|
|
|
Its me again, i making a grade data entry. I have a database of diffenrent subjects. The fields that i will going to use is these: Subject code, Subject description, and professor.
What I want is to view the subject description and the professor in the textboxes when I select a subject code in the combo box.
I had already made an OleDbDataAdapter for that, and the subject codes was the data member in the combobox. The subject codes is already the item of the combobox but my problem is, the subject description and professor was not appeared in the textbox. What I want is this to view to its corresponding textboxes.
I used this code:
For ctr = 0 To DataSet31.studentsubjects.Rows.Count - 1<br />
dr = DataSet31.studentsubjects.Rows(ctr)<br />
If ComboBox1.SelectedItem = dr("subject") Then<br />
subjdesc.Text = dr("subjdesc")<br />
prof.Text = dr("professor")<br />
End If<br />
Next
Is the code above correct??? If its not, what is the right code????
Where will I going to put the codes???
|
|
|
|
|
Rharzkie wrote: ComboBox1
Can you please name your variables with good names rather than accept the defaults that Visual Studio gives you.
Rharzkie wrote: ComboBox1.SelectedItem = dr("subject")
What does ComboBox1.SelectedItem return? Put a break point down and see what value it is returning. Also it will aid the debugging process if you assign the result of method and property calls to something then test on the local variables - it will make it easier to see what is going on in the debugger.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
The ComboBox1 is for the list of the subject code. I use the DISTINCT in query builder to display the items in the combobox. I use the OleDbDataAdapter1 for that.
But the connection that i will going to display in textbox is OleDbDataAdapter3. In short, i use different connection from the combobox and the textboxes. Is this correct?
|
|
|
|
|
Rharzkie wrote: The ComboBox1 is for the list of the subject code.
Yes, but ComboBox1 still isn't a good name for it, is it?
Rharzkie wrote: OleDbDataAdapter1
Rharzkie wrote: OleDbDataAdapter3
 How do you keep track of what each of these does?
Rharzkie wrote: Is this correct?
I have no idea. This code is obviously a mess. My suggestion is to start by naming things properly. Then it might be an idea to create a DAL and remove all your database interaction out of the user interface classes. It might also be an idea to do what I asked in my previous post and answer the question I asked. ("What does ComboBox1.SelectedItem return?" - And I want actual values and object types, not what you think it should return)
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
I currently have an app up and running in .NET. We have just built the server for the app to run on but there seems to be a problem with some of the returned date formats within the app itself. I checked the original test db and the date format was english style 31/12/2007 but on the new database it was amercian style 12/31/2007. Is there a setting in SQL server 2005 that would aler this?
|
|
|
|
|
Dates are not stored any any locale dependent format. When you get data out the database you should get a DateTime object which you can then format as you need with ToString()
e.g. myDate.ToString("dd/MM/yyyy")
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hi,
Is there any query through which i can get the number of columns in a table.
Best Regards,
Mushq
Mushtaque Ahmed Nizamani
Software Engineer
Ultimus Pakistan
"English is my second language, so please don't mind if i do some grammatical or spelling mistakes in my messages."
|
|
|
|
|
SELECT Count(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_Name = 'TableName'
|
|
|
|
|
I try this code, and it's work.
select name,date=max(date),
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1
group by name
But after few days, my database is update with more than one record in the same name, separate by column name 'date',
Regarding I just want to display data depend on last date,
Example my table is below:
name type value date
-----------------------------------------------------
a A 1 07/10/2007
a B 1 07/10/2007
a C 1 07/10/2007
a A 2 07/13/2007
a B 2 07/13/2007
a C 2 07/13/2007
but the result is:
the result is:
name date A B C
-----------------------------------------
a 06/13/2007 3 3 3
I'm expecting like this:
name date A B C
-----------------------------------------
a 06/13/2007 2 2 2
I try to modified the code:
select name,
sum(case when type='A' and date=max(date) then....
but found error
So, any suggestion, thanks a lot.
|
|
|
|
|
Have you tried:
<code> select name,date,
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1 where date=max(date)
group by name </code>
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
Are your sure and Have you checked is the query wroks.
Regards
KP
|
|
|
|
|
Krish - KP,
This one should do it. I created the same table you had, ran this one and it gives the right results 
<code>select name,max(date),
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table_1 where date=(select max(date) from table_1)
group by name </code>
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
Now syntax is right.
what about logic. is this what required 
Regards
KP
|
|
|
|
|
About logic is OK, I already use convert.
And about your code below:
SELECT a.name, MAX(a.dt) AS [date],
SUM(CASE WHEN type = 'A' THEN valu ELSE 0 END) AS A,
SUM(CASE WHEN type = 'B' THEN valu ELSE 0 END) AS B,
SUM(CASE WHEN type = 'C' THEN valu ELSE 0 END) AS C
FROM TblB a
INNER JOIN (SELECT name, MAX(dt) AS dt FROM tblB GROUP by name) b
ON a.name = b.name and a.dt = b.dt
GROUP BY a.name
this only show each name, with maximum value
not each name, each type with maximum value.
I have been tried to modified inside inner join code but, still don't get.
Pls help mee.....
|
|
|
|
|
example given gives max with name. that's why in inner join table (subquery)
i've not used type.
include type also in subquery's group by clause.
Regards
KP
|
|
|
|
|
OK Krish, I done with your code, it's very2 work thanks.
But how I write sql syntax if I want to select another table
(for example tbl2) instead of a.tbl1 INNER JOIN b.tbl1 ???
Thanks a lot
B.regards
|
|
|
|
|
Hi Jonh, I have error on your code
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
I try to modified the code below:
select name,date,
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1 where date=(select max(date) from table1)
group by name
it's work but, this code will only show maximum date on whole table,
not show each name which have maximum date.
Need your suggestion. thanks
|
|
|
|
|
SELECT a.name, MAX(a.dt) AS [date], <br />
SUM(CASE WHEN type = 'A' THEN valu ELSE 0 END) AS A, <br />
SUM(CASE WHEN type = 'B' THEN valu ELSE 0 END) AS B, <br />
SUM(CASE WHEN type = 'C' THEN valu ELSE 0 END) AS C<br />
FROM TblB a <br />
INNER JOIN (SELECT name, MAX(dt) AS dt FROM tblB GROUP by name) b <br />
ON a.name = b.name and a.dt = b.dt<br />
GROUP BY a.name
Regards
KP
|
|
|
|
|
Does that one do what you need it to?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
Yes, you all great !!!
Thanks a lot.
B.regards
|
|
|
|
|
Hi All, sorry to bothering you again, need your help.
my format date is complete like "7/12/2007 10:40:49 AM"
so, concern that time/date is different each row, example (i write date as integer)
name type value date
-----------------------------------------------
a A 1 1
a B 1 2
a C 1 3
a A 2 4
a B 2 5
a C 2 6
when I run the code the result is:
name date A B C
----------------------------------------
a 6 0 0 2
so, the code show value each name with maximum date,
not show value each name, each type with maximum date.
so, i very confuse, realy need your help, thanks
|
|
|
|
|
how do i make a backup plan that can backup the database to another location automaticly
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
