Simple And Quick Query Questions
Jan 13, 2008
Hello, Everyone,
Consider the following:
One Table
3 Columns: StudentName, ExamSubject, FinalScore
A class with 40 students
Each student took the 3 exams and have scores.
All student took the same 3 exams.
120 rows total.
Task One:
Top Score in each subject
Task Two:
Top 3 student name in each subject
Task Three:
Top 3 total score student in the class
Task Four:
Avg. Score for each student
Thank you very much...
Jack
View 1 Replies
ADVERTISEMENT
Sep 14, 2007
I have a year and a month parameter list in a report. Is there any way to get the month parameter list to appear as the name on fhe month instead of the month number?
I have a report that displays fine in Visual Studio but when I render it in reports on the web server the data begins about 3/4's of the way down the page with a bunch of white space. Any ideas?
Thanks.
View 1 Replies
View Related
Oct 9, 2006
I need to:
Copy an existing production database to a new database on the same server; this will be used for development.
Copy an existing table for backup purposes in preparation for deleting a couple fields from the table (precautionary process really).
I've been out of the database swing if you will for a while, just now getting the opportunity to get my feet wet so to speak. I can use either Enterprise Manager or connec to the database via ODBC and make the copies/deletes there, but I'm looking to do this via EM or SQL command.
Any help or direction would be greatly appreciated!
Thanks!
Bobby
View 3 Replies
View Related
Feb 28, 2008
I think I have a rather easy one but I'm somewhat new to SQL.
I am currently copying 30,000 of my forums users over to my auction software.
I want to copy some fields from the my "users" table to some fields in the my auction table. Which are all located on the same database.
I've been reading: http://dev.mysql.com/doc/refman/4.1/en/insert-select.html
to help me learn this.
However, I am a bit lost. Could someone give me an example of how to select a table and field, then copy that field into another table/field?
I mean a command to run? Then I can go from there. :-)
Thanks!
View 16 Replies
View Related
Oct 18, 2007
HELP PLEASE! 1) Is it true that INSERT, UPDATE, and DELETE queries can be carried out on only ONE table at a time?2) I have a newly created database which is empty, and has four tables related with FK's. How can I begin to enter data, if i can only do an INSERT or UPDATE on one table. When i do a SELECT query, i don't even get an empty results set to modify. But i think that's an old fashion technique anyway. And if i can only INSERT VALUES into one table at a time, that violates the relational integrity. PLEASE HELP!DanThe only thing I can think of is the de-enforce the Relationships Constraint... Then, one at a time, fill each table's 1st record with fictious data, and then enforce the Relationships again, hoping that the identity columns will match up.
View 2 Replies
View Related
Aug 16, 2006
When I'm designing an Integration Services package, the drag and drop doesn't work. It did at one time but doesn't now. What did I change??? Driving me nuts!!!
Also, this is a simple package. I'm exporting 6 colums from a view (db --> csv). I only want the first initial from the middle name. What data flow transformation object do I use? I know it's not the data conversion because that concerns the column type.
Thanks
View 3 Replies
View Related
Jul 1, 2007
Dear Friends,
I am a PHP/Delphi MySQL programmer for some years, now I am going to create some code in MS SQL and VB. So I downloaded and registered MS SQL 2005 Server Express Edition and some tools like SQL Server Studio Management Express, and VB 2005 Express.
As I have some knowledge of MySQL design and programming, I have installed MS software, rather without problems. Then, using Studio Management I created new database, new table in it with some four fields (int, varchars and smallint). Then from the same tool I opened table and inserted some data, and closed this application.
Next I opened Visual Basic and in Database Explorer I set up connection to my SQL database indicating the file it is stored in (path*.mdf). I ordered not to copy data to new file but to use original one.
Then in Data Sources I created new data source based on previously set connection. In the end I dragged and dropped my table from Data Sources into my new Form of my new Windows Application, nice grid with panel appeared. So I ran the app, I tried to put some data and then after trying to save (using floppy disk icon on panel) i got error:
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
which appears in event handler for saving in line i've indicated.
Me.Validate()
Me.LudzieBindingSource.EndEdit()
Me.LudzieTableAdapter.Update(Me.TestDataSet.ludzie ) ' <-- here highlights
I tried then to add to ID field identity specification, nothing changed. Then in query editor of management console I put some code:
exec sp_configure 'user instances enabled', 1.
Reconfigure.
As some sources mentioned. I restarted Service. Nothing Changed. So this is my problem. What to change.
I use Win XP Pro SP2.
And next one question, even more important for me is the way I connect to database. I found that in Visual Basic Creating new connection in Database Explorer I indicate the file on my local drive!!! And for example if I try to open my table alongside in Management Studio Express and in my App in VB and the error raises
Cannot open user default database. Login failed.
Login failed for user 'FS7120Mm227'.
which is not a surprise for me as in VB i am using file which cannot be shared.
In MySQL I simply put address, port and so on and I could use my database in Delphi, PHP, and another management tools with no hassle. Is it possible in VB? Or it only can be used to exclusive using database?
Please inform me if such questions shall be directed to VB forum, I am not sure, and thank you for _any_ answer.
MS goes right way giving people great possibility to use and even sell small apps using their Express Editions of SQL and VB, but it is still too confusing for people who encouraged start using their apps and have such basic problems.
Michael.
View 5 Replies
View Related
Apr 28, 2007
1. Let's say I have a SqlDataSource named Fred. Say I have a string variable, call it Barney. If I call Fred.Insert(Barney) HOW Does the insert command GET the Barney value and pass it to the SQL Insers string? 2. Lets say also in this same insert, I want the user Lets say Batty, who is logged in, to have her username automatically inserted into the table along with her Barney value. What is the method to get a hold of user name that is magically floating around in the session somewhere? All said and done, I'd love for it to work like this: "INSERT INTO [BedRock] ([UserName], [Character] ) VALUES (SomethingThatResolvesToSignedInUserName, SomehtingThatResolvesToStringValueofVariable) Table BedrockUserName CharacterBettyRubble 'Bam-Bam' THANKS inadvance fro ANY HelpDan-Simple and sleepless-Harper
View 3 Replies
View Related
Mar 27, 2007
I have a some raw data in a string that looks like
'1989'
I need that to become numeric 19.89
if I define a type double for rawAmount
and do this:
rawAmount = CInt(iRest.Substring(41, 4))
Row.amount2 = rawAmount / 100
where row.amount2 is a type double precision float from my script component task output, I get:
19.899999999999999
what output should i use in this data flow component to get 19.89.
thanks.
View 3 Replies
View Related
Mar 2, 2007
Hi all,
I need few clarifications from you experts regarding SQL server 2005 & CLR integration ( my questions might be simple and silly, please bear with me).
A web service should be invoked from the SQL Server , Is CLR stored procedure only way to do that ?
Does SQL Server uses the CLR only when the CLR support is enabled ? OR SQL Server itself runs on top of the CLR no matter it is enabled or not ?
What are the major disadvantages of using CLR stored procedures instead of T-SQL?
Thanks in advance,
DBLearner
View 3 Replies
View Related
Dec 7, 2007
this query works, i want to add a 4th column that is the value of the 3rd column subracted from the value of the 2nd column, how can i add this?? SELECT `tagid` AS w1, (SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) AS w2, (SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) AS w3FROM tagsWHERE `value` > -1GROUP BY `tagid`ORDER BY w2 DESC
View 1 Replies
View Related
Jun 24, 2004
I have a quick question involving a query I need to run.
I have a field on a form that I can only use a calculated SQL command to fill.
The field is going to be a calculated date field. It is based on a selected urgency (which equates to time), and an open date. What I need this new field to be is the sum of these 2 fields, but if it is after 5:00, then it should take the difference between the time it would end at and 5:00, and then add it to 8:00 the next day.
so if the open date is 6/24/04 16:30:00 and the urgency would call for 04:00:00 hours then this new field should come out to 6/25/04 11:30:00
if it is 06/24/04 12:00:00 and 04:00:00 hours then it would show 06/24/04 16:00:00. (this part is easy, but the top part is confusing)
Any help is appreciated
View 3 Replies
View Related
Jan 24, 2008
I have created an Insert Query in a dataset using the wizard and it has added a little SELECT statement to the end of it.
I was interested in how this works. And what it will actually do, does it just re-fill the dataset after the insert. If it does as i have the dataset bound to a datagridview does this inturn display the new row automtically?
Code Snippet
INSERT INTO dbo.tblPackType(PackType)
VALUES (@New_PackType);
SELECT DataID, PackType FROM dbo.tblPackType
WHERE (DataID = SCOPE_IDENTITY()) ORDER BY PackType
Also what does (DataID = SCOPE_IDENTITY()) do?
Cheers, Darren.
View 1 Replies
View Related
Sep 28, 2006
any idea?
quick query duplicate records (speicifed fields.value are same) using T-SQL?
View 5 Replies
View Related
Nov 19, 2007
Probably SQL newbie stuff, but...
I need to remove all records except a short list from two related tables (related by uniqueidentifiers). I'm working with CE 3.5.
First I tried a coworker's suggestion:
Code Block
delete from [SYSTEM_USER] su, SU_PERMISSION where su.SU_USER_NAME IN ('friendlyName1','friendlyName2')
CE doesn't like multiple tables in a delete.
Then I tried a join to remove the foreign key rows:
Code Blockdelete from SU_PERMISSION sp join [SYSTEM_USER] su on sp.SUP_SU_ID = su.SU_ID where su.SU_USER_NAME IN ('friendlyName1','friendlyName2')
CE doesn't like a join in a delete
Then I queried SU_PERMISSIONS to get the GUIDs associated with the usernames, and built my query (I manually did a SELECT subquery, which I know CE does not support):
Code Block
delete from SU_PERMISSION where SUP_SU_ID not in ('ea6b9b05-e1b1-b86a-e072-d6c8af80b051','0a102ce9-bca5-8df4-e062-d6c8af804072')
CE removed every row in the table, including the ones with these GUIDs.
Any way out of my situation?
Dave Michel
View 1 Replies
View Related
Apr 4, 2006
I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
View 4 Replies
View Related
Oct 26, 2004
I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information.
I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns
I will use Honda Accord as an example.
Honda has made the Accord since probably the 80's
I know that if I
SELECT yearnum
FROM YearNum
ORDER BY yearnum
I am using C++ Builder too..
it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord.
Is there anyway to filter out multiple years, so I could just have the regular order of years?
View 1 Replies
View Related
Oct 1, 2007
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
View 2 Replies
View Related
Jan 7, 2008
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
View 2 Replies
View Related
Mar 18, 2008
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
View 2 Replies
View Related
Apr 21, 2005
Hi All
Can anyone tell me what this simple SQL query would be:
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
View 4 Replies
View Related
May 28, 2006
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on:
I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD.
When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?
View 2 Replies
View Related
Mar 7, 2003
Hi,
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name
Gates || Bill
Boyce || Mike
Gates || Bill
Gates || Phil
Results I want:
Last Name || First Name || Count
Gates || Bill || 2
Boyce || Mike || 1
Gates || Phil || 1
Thanks a lot,
Heta
View 2 Replies
View Related
Aug 21, 2004
Hi,
I have a very simple question:
declare @treshold int
set @treshold = 10
SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold
I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?
I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?
thanks
View 2 Replies
View Related
Jan 17, 2002
Hi,
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
Thanks in advance for your help
Kevin
View 1 Replies
View Related
Oct 21, 2006
This is my query...I am attempting to only return the records for each
sales_contact with the greatest issue_id
select
sc.sales_contact_id
, idd.issue_id
, sr.code
from
sales_contact sc
, invoice i
, invoice_line_item il
, sales_region_special_section srss
, sales_region sr
, issue_date idd
where
sc.sales_contact_id = i.sales_contact
and i.invoice_id = il.invoice
and srss.sales_region = sr.sales_region_id
and il.issue_date = idd.issue_date_id
and srss.invoice_line_item = il.invoice_line_item_id
order by
sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id
for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC
B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are
situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
thanks in advance for your help.
View 1 Replies
View Related
Jan 7, 2005
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
Code:
-----
| ID |
-----
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 4 |
------
Would return something like:
Code:
----------------
| ID | Count |
----------------
| 1 | 3 |
| 2 | 1 |
| 4 | 2 |
-----------------
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
View 3 Replies
View Related
Sep 26, 2006
OLD_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____0
NEW_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____1111
(end result)
FINAL_TABLE
43434_____9258_____9496_____1111
I have two tables ... I am trying to get only the rows that are different.
View 2 Replies
View Related
Sep 19, 2005
Hi folks,
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0
if count(*) from question where serialno > $serial != 0
else
select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
View 5 Replies
View Related
Sep 1, 2006
Hi,
This is probably a simple SQL statement question, so please bear with me.
Basically I have a CustomerMaster with the following fileds
1) CustomerID
2) CustName
3) Address
4) ResTelNo
I want retreive the CustName,Address and ResTelNo if the ResTelNo has occured in the table more than twice.
Could someone please help with this. I would like to use the same query in MS Access as well.
Thanks in advance for your help.
View 2 Replies
View Related
Sep 17, 2004
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like
SELECT Count(*) AS Expr1
FROM Table1
WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
thanks
View 4 Replies
View Related
Feb 29, 2008
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate
,OutEndDate,OutStartTime,OutEndTime,OutID
FROM vwOutOfOffice
LEFT OUTER JOIN vwStaffList ON OutUser=StaffID
WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example
Current date=29/02/2008
Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of date)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
View 2 Replies
View Related
