Selecting The 3rd Highests Item From A Table?
Feb 5, 2004
I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.
I have 2 tables. One table with schedules and one with attended time.
What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?
Example scheduled records
student1 2/4/2004 7 hours scheduled
student2 2/4/2004 8 hours scheduled
student1 2/3/2004 8 hours scheduled
student1 2/2/2004 6 hours scheduled <---Need this one
student2 2/2/2004 8 hours scheduled
student2 1/30/2004 4 hours scheduled <---Need this one
...thousands more...
It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.
View 3 Replies
ADVERTISEMENT
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
View 2 Replies
View Related
Jul 12, 2007
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies
View Related
Dec 6, 2013
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies
View Related
Mar 6, 2007
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
View 3 Replies
View Related
Apr 1, 2008
Hi,I want to check that Is there row exists in table or not.Please correct me. Dim conn As SqlConnection Dim comm As SqlCommand Dim reader As SqlDataReader Dim connstring As String connstring = ConfigurationManager.ConnectionStrings("iharyana").ConnectionString conn = New SqlConnection(connstring) comm = New SqlCommand("select * from test where username=@username", conn) comm.Parameters.Add("@username", Data.SqlDbType.VarChar, 20) comm.Parameters("@username").Value = uname conn.Open() reader = comm.ExecuteReader While reader.Read If reader.Item("username").ToString = "" Then Response.Redirect("http://www.iharyana.com") End If End While reader.Close() conn.Close()
View 1 Replies
View Related
Jan 26, 2004
Thats it
How do you receive the last item(row) in a table.
Thanks
View 11 Replies
View Related
Aug 2, 2006
I have a sql table containing names of departments. Is there a way after a user has typed a department in a textbox on a web page I can search for it in the sql table and if it isn't there then add it. I am using asp.net for the web page.
View 3 Replies
View Related
Nov 27, 2006
Hello people,
I have a table of 30,000 records on my handheld which runs windows 4.2 and sql me. When the item I am searching for is at the top of the table, the search finds it easily. When the item is in the middle or lower, it doesnt find it. I am not running out of memory and my database is on the local memory. Here is my code:
String query = "SELECT * FROM Products WHERE Barcode = " + TheReaderData.Text;
DataSet ds = GetDescription(query);
label2.Text = TheReaderData.Text;
label1.Text = "No description";
if (ds.Tables[0].Rows.Count != 0)
{
label2.Text = (ds.Tables[0].Rows[0].ItemArray[0]).ToString();
label1.Text = (ds.Tables[0].Rows[0].ItemArray[1]).ToString();
}
---------------------------------------------------------------------------------------
private DataSet GetDescription(string str)
{
DataSet ds = new DataSet();
try
{
System.Data.SqlServerCe.SqlCeEngine theEngine = new SqlCeEngine(CONN_STRING);
if (!System.IO.File.Exists(DBFILE))
{
theEngine.CreateDatabase();
}
SqlCeDataAdapter da = new SqlCeDataAdapter(str, localDB);
da.SelectCommand.CommandTimeout = 6000;
da.SelectCommand.CommandType = CommandType.Text;
da.Fill(ds);
}
catch (Exception exce)
{
string total = exce.ToString();
}
return ds;
}
I hope there is some way to find the item in the object. Any ideas?
Thanks in advance, John.
View 9 Replies
View Related
Feb 7, 2007
Is it possible to find the position of an item without retrieving all the records in the table?
I have a products table, and want to find out how its sales are doing within a particular category.
My table consists of the following columns of interest:
ProductID, CategoryID, ItemsSold.
So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?SELECT * FROM dbo.Products
WHERE CategoryID = 10
ORDER BY ItemsSoldAlso, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?Any help would be very much appreciated.Thanks.
View 3 Replies
View Related
Jan 16, 2008
I need to figure out hours based on StartTime and EndTime (year does not matter) from the first table and totaled for the entire week based on the effective date in the second table.
I need it to return the follwing:
WorkHourGroup TotalWorkHours
TJOHNSO 24 Hours
Table 1 -'workhour'
WorkHourGroup DayIDStatrtTimeEndTime
TJOHNSO0NULLNULL
TJOHNSO11899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO2NULLNULL
TJOHNSO31899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO4NULLNULL
TJOHNSO51899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO6NULLNULL
Table 2 -'workhourgroup'
WorkHourGroupWorkHourDescEffective Date
SMBSMB Work Week2007-09-11 00:00:00.000
SMITHBSTANDARD2008-01-12 00:00:00.000
TJOHNSOJohnson12008-01-11 00:00:00.000
Any ideas on how to accomplish this?
Thanks,
DZ
View 2 Replies
View Related
Sep 6, 2015
I am designing an application where multiple users can be assign to a product for review. If a user doesn't have access to the product, they are not allowed to see it. I have attached my table design. All users are assign to a role. See attached screen
View 1 Replies
View Related
May 22, 2008
While building Report Model Solution in Business Intelligent Studio i am getting following errror-
More than one item in the Entity 'table' has the name 'columns'. Item names must be unique among immediate siblings.
Please advice how to resolve this one.
Thanks
Ashwin.
View 1 Replies
View Related
Jan 18, 2007
hi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
View 9 Replies
View Related
Jul 20, 2005
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies
View Related
Sep 11, 2007
helo all...,i want to make procedure like:examplei have table: item (itemid,itemname,stock)orderdetail(no_order,itemid,quantity)itemmoment(itemid,itemname,stock)item table itemid itemname stock c1 coconut 2 p1 peanut 2orderdetail tableno_order itemid quantity 1 c1 5itemmoment tableitemid itemname stock c1 coconut 0 p1 peanut 0 when customer paid, his quantity in orderdetail decrease stock in item table..so stock in item table became:itemid itemname stock c1 coconut -3 p1 peanut 2it's not good, because stock may not minus...so i want to move -3 to itemmoment table..so stock in item table became:itemid itemname stock c1 coconut 0 p1 peanut 2and in itemmoment table became:itemid itemname stock c1 coconut 3 p1 peanut 0my store procedure like:ALTER PROCEDURE [dbo].[orders]( @no_order as integer, @itemid AS varchar(50), @quantity AS INT)ASBEGIN BEGIN TRANSACTION DECLARE @currentStock AS INT SET @currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @itemid) UPDATE [item] SET [Stock] = @currentStock - @quantity WHERE [itemid] = @itemid COMMIT TRANSACTIONENDit's only decrease stock with quantity. i want move stock minus from item to itemmoment..can anyone add code to my store procedure?plss.. helpp.thxx....
View 2 Replies
View Related
Nov 20, 2007
Hi,
These aren't my tables, but lets dumb it down for my sake!
I have 2 tables:
CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount
There are several records in TransactionInfo for each record in CompanyInfo.
I want to:
SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)
How can I do this? Can I?
View 4 Replies
View Related
Apr 7, 2006
is there some way to allow this to execute, using a function or something?:
select * from 'cusomer'
you see i have a list of about 300 table name in string format, and i want to use a cursor to itterate through each table, but as all the table names are in string format i cant get it done. any ideas??
View 1 Replies
View Related
Sep 13, 2005
This is, I'm hoping, a simple thing that I just don't know how to do. But I've searched my books and read a lot of threads but so far, no luck in what I'm trying to do. Here's the basic layout of 2 tables from my d/b (MSDE):
tblCity
cityID (p/k)
stateID (f/k to tblState.stateID)
cityName (varchar 35)
tblStudent
studID (p/k)
cityID (f/k to tblCity.cityID)
hometownID (f/k to tblCity.cityID)
studFName (varchar 25)
studLName (varchar 35)
...
I *hope* that what I tried to do there is allowed. Basically what I'm trying to do is to tie the student to both his current City (by foreign key cityID) and his hometown city (also by foreign key cityID).
So, if New York is (127, 33, 'New York') and Birmingham is (1050, 1, 'Birmingham') (in tblCity), then in the student table (tblStudent), John Smith would be
Quote: 12150, 127, 1050, 'John', 'Smith', ...
How would I format my SQL statement to get both cityNames? I thought this would work
Code:
SELECT s.studID, c.cityID, c.hometownID,
s.studLName + ', ' + s.studFName AS studNameName,
c.cityName, c.cityName AS swapconNum
FROM tblStudent AS s INNER JOIN tblCity AS c
ON (s.cityID = c.cityID)
WHERE s.studID = 12150
Can I do this? If so, what am I doing wrong? Sorry, I'm kinda sorta new to SQL.
RudeDog
View 2 Replies
View Related
Feb 18, 2007
hi, i am sure this is very simple, but its just got me stuck! i have a table which i use for inserting products, now its not very exciting to see the cat_id so i wanted to put a dropdown box on there which would show the cat_description from tbl_cat and then put the tbl_cat.cat_id into tbl_products.cat_id
any help would be greatful
Jez
View 1 Replies
View Related
Jul 3, 2007
hi there, i have a page where on my button click i need to select address, pcode from tbl_person where person_id = querystring and update or insert those values into tbl_del where order_ID = querystring 2 below is the code i am using, although im stuck on the sql stringSqlConnection objConnAddy = new SqlConnection(sConnectionStringCustInfo);using (objConnAddy)
{
objConnOID.Open();
// insert values into delivery table, second line updates price + VAT
string sqlAddy = "?????";
SqlCommand objCmdAddy = new SqlCommand(sqlAddy, objConnAddy);
objCmd1.ExecuteNonQuery();
i had a look on the net and here but i cant seem to find exactly what i want, any help would be great!
Cheers Jez
View 2 Replies
View Related
Sep 23, 2007
How can i just select filed name from the name without data. What query should we write foe that.
View 2 Replies
View Related
Feb 14, 2008
i ve a table named "Employee" in wh i ve fields "Emp_Id","Name" and "Email". i want to select each employee "Emp_Id" one by one from "Employee" table and insert it into another table named "Concerned_Department". like if i ve three rows in "Employee" table with "Emp_Id" 1,2 and 3 respectively,in this case i want to first select "Emp_Id"=1 then "Emp_id"=2 and lastly "Emp_id"=3 and insert them into "Concerned_Department"table.
after that the process starts again like inserting "Emp_Id"=1 in "Concerned_Department" and so on.
plz note that i want to insert "Emp_Id" one by one not all the fields together.
can u plz tell me the query for that wh i ve to write in SQL
Thanks and happy valentine day to all of u
regards
Ahmed Bilal Jan
View 3 Replies
View Related
May 3, 2004
lets say that this select statement will give me 100 records..
"Select UserID from Users where location = 'FL'"
from these 100 records, I want to randomly get 5 records. whats the best way to go about doing this
View 1 Replies
View Related
Dec 13, 2004
Hi, my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code.
SELECT DateOfStats
FROM table1
WHERE (DateOfStats NOT IN
(SELECT dateofstats
FROM table2)) and (DateOfStats NOT IN
(SELECT dateofstats
FROM table3))
Thanks for the help in advance.
View 1 Replies
View Related
May 25, 2005
i need to select records from table "A" if only the "PK" of "A" exists in table "B". I need to return a resultset not just a single record. The problem is table "B" is not a table in database instead a user supplied table which can be a datatable in memory.
View 2 Replies
View Related
Aug 22, 2001
Hello,
We're trying to do the following...
Declare @Name Varchar(15)
Select @Name = 'TableName'
Select @Name = @Name + Rand(SomeSeedValue)
Select *
Into @Name
From Someothertable
This just gives us syntax errors, anyone else had any success with this ?
View 1 Replies
View Related
Mar 5, 2001
Hi!
I need to create a table and populate it with other table's contents. I know this can be done DTS, I wonder if there are means to make it throught a single TSQL command.
Thanks,
Fabio
View 1 Replies
View Related
Jan 7, 2004
I have a temporary table that contain 1000 rows. I want to insert records from 900 to 1000 into another temporary table. Is ther any query that will do this in SQL server. With cursors I can do that but I need one shot query. Please give me some suggestions.
I using this query but it is not returning any rows.
select * into #t2 from #t1 t1 where not exists( select top 900 * from #t1 t2)
View 6 Replies
View Related
Nov 17, 2005
I was given this query but could not build it successfully.
can anybody help me out.
Consider the following scenario:
we have a table with single field of type Varchar(100).
we have few hundred records in it.
we want to retrieve last 'n' records from table.
i know this is bad idea but we need to have some solution to work it out.
i'm breaking my head but to no use.
Please help
View 3 Replies
View Related
Sep 25, 2015
I've got an update statement with a subquery (I'll post the code further down) that I need to either make dynamic or do something else to make sure it does what I want.
The query is as follows:-
UPDATE dbo.tbl_Process_List_Control_Table
SET LastUpdateDateTime = (
SELECT ISNULL(MAX([LatestRowUpdateDateTime]), @LastUpdateDateTime)
FROM [wtbl_Process_List_Patient]
)
WHERE ProcessList = @ProcessName
This is a called proc with the following parameters:-
@LastUpdateDateTime is the date of the last record to be loaded
@ProcessName is the name of the process that was started/finished.
I need to make [wtbl_Process_List_Patient] dynamic so it looks at a different table based on a passed parameter.I've tried making the whole thing dynamic but it states I need to declare @LastUpdateDateTime which I can't see how to do as it's already passed to the proc (as is the process name).
View 6 Replies
View Related
Jun 20, 2008
Hi
I've created a linked table connection to an external data source within sql server 2005.
The link looks OK, I can see all the tables etc, but I want to create a view on another database to some of the tables on the linked database.
How do I perform something as simple as a select statement as I can't seem to be able to select anything.
Is there something that i need to do first in order to be able to select fields from the linked table?
Thanks
View 3 Replies
View Related
Jul 5, 2007
Good Day guys, sorry i'm just new in SQL Server.
My problem is like this:
I want to pass Table Object to a declared variable and make View.
Usually we make like this:
"Select * from Table1"
I want like this:
"Select * from @Table1"
I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.
Please help me guys, i really appriciated everything.
Thanks,
Ero-Sennin26
View 5 Replies
View Related
