How Can I Return The 5 Records From A Database Table Using Ado.net
Apr 24, 2007
I have an incident reporting management application. People are supposed to report incidents by this application and every time some one reports an incident, they also select thier employee#(reqiured field). so how can write an sql statement that returns only the Top 5 incident reporters i.e going by employee number. Iam thinking of applying a COUNT function on the incident_id and grouping by Employee# but then how do i make sure that only the top 5 incident reporters are returned.
View 1 Replies
ADVERTISEMENT
Feb 13, 2008
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
View 5 Replies
View Related
Aug 18, 2005
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
I used Express Manager ( XM ) to try the select statements. That is how I isolated the problem. Even using a "Select * from timecards where employee = 'test' " returns the inserted test records. I found that if I use a WHERE or ORDER BY clause in the SELECT statement to .fill the .net dataset, all records are returned.
I am familiar with DB2 but I am a newbie at VB.NET and MSSQL
Any suggestions?
Thanks!
GordonG
View 1 Replies
View Related
Sep 27, 2005
Hello, everyone:
I have two tables with some duplicated records like,
ZZZTest:
C_IDC1C2C3
10AAA
20BBB
30AAA
40BBB
ZZZTestTable:
D_IDC11C22C33
1AAA
2AAA
3BBB
4BBB
5AAA
6AAA
7BBB
I wand to get the unique records by SELECT / JOIN statement. Now I used a query,
SELECT * FROM ZZZTest t
INNER JOIN ZZZTestTable tt
ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33
and got the records like,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA1AAA
10AAA2AAA
30AAA2AAA
20BBB3BBB
40BBB3BBB
20BBB4BBB
40BBB4BBB
10AAA5AAA
30AAA5AAA
10AAA6AAA
30AAA6AAA
20BBB7BBB
40BBB7BBB
What I am expecting is,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA2AAA
20BBB3BBB
40BBB4BBB
Any suggestion will be great appreciated.
Thanks
ZYT
View 3 Replies
View Related
Jan 5, 2006
I have looked everywhere but cannot find the solutions. I have two tables.
Table1 = tblvacation (10 records)
fields vacation_id, land, destination
Table2 = tblimages (1000 records)
fields image_id, vacation_id, folder, name
Both are related through vacation_id. Every vacation however has many pictures. I need a query where the result would give me back all records (vacations) from Table 1 with only 1 record from Table 2(one image per vacation).
View 5 Replies
View Related
Aug 5, 2014
I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.
CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT
[Code] .....
View 1 Replies
View Related
Aug 6, 2007
Hi Gurus,
I have a table having sales records and there are more than one record per one customer. The sales table has a reference number like below.
CustomerID
Sales_Ref
2
H_1123
2
H_2344
2
H_4322
I need to do a query and generate the following query.
CustomerID Ref
2 H_1123,H_2344,H_4322
Could someone help me on this.
Thanks.
Cheers,
Vijay
View 3 Replies
View Related
Oct 26, 2006
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies
View Related
Dec 19, 2013
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
View 9 Replies
View Related
May 5, 2008
How do I return a list of all user tables in a database where table name starts with EmpID_
for example the table names are:
EmpID_Countries
EmpID_Regions
EmpID_States
EmpID_Colorado
EmpID_Arizona
etc etc...........
I am using SQL Server 2005. Thanks.
View 1 Replies
View Related
Aug 8, 2007
How do I select data from another database name (in the same SQL SERVER LOCATION) and its child table?
SELECT user_id
FROM [10.10.20.3].CC_host.usr_table
Where(email LIKE N'%@%' And email LIKE N'%usa.com') Order By user_id
View 4 Replies
View Related
Jun 30, 2004
Hi, all I got one stupid question in mind. The idea is that I want my SQL database to install into another computer. The best way for me is I want to create a script file for creating tables, triggers, stored procedure. But I want all the SQL statement that insert all the records in to each table too.
Does anyone have a solution for me? Or there be other way?
I am always waiting for ur suggestion.
View 2 Replies
View Related
Sep 13, 2006
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies
View Related
May 6, 2007
Hi,
I have a set of records in database table and I want to update one column to be the same for all of them.
Can you suggest code solution?
View 1 Replies
View Related
Aug 24, 2015
I'm trying to delete some records from some tables in a SQL Server 2008 R2 database. There's a foreign key relationship between the two tables. To make things easier here's the definition of both tables:
-- Parent table
CREATE TABLE [dbo].[PharmInvInItemPackages](
[InventoryInDetailID] [int] IDENTITY(1,1) NOT NULL,
[InventoryInID] [int] NOT NULL,
[ItemPackageID] [int] NOT NULL,
[code]....
View 5 Replies
View Related
Aug 2, 2007
I have a question here.
I have a Audit tables database. I want to archive the Audit Tables data to another new database in the same server and remove the data in the Actual database but continues with the table seed where it's the primary ID.
Example:
I have a AuditProducts Table with AuditID as BIGINT and auto increment value.
contains10,000 records in AuditTable Database.
I want to archive the tables to a new database: Audit200707
After archived, the AuditProducts Table in AuditTable database will remove all the records and continues with 10001 in the next record.
How can I done the whole process using T-SQL so that it can schedule and run for every month? BTW I am using SQL Server 2005 SP2.
Thanks and regards,
View 5 Replies
View Related
Jun 9, 2007
is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?
View 4 Replies
View Related
Jun 11, 2006
hi all,
I need to select the no of records on the basis of specified range of records.
In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..
I have used temporary table,map the table primary key to the next table with identity
but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...
Is there some other best way to extract the specified data
here is the type of query.
select * from customers where rownum between 1000 and 10000
this is in oracle
i am in need to do this in the sql server
waiting for the response...............................
View 5 Replies
View Related
Apr 12, 2006
Hi
i need to inser the .dbf file records into sql table.
How should i go about it?
regards,
Kiran
View 6 Replies
View Related
Aug 11, 2015
Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
View 9 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
View 3 Replies
View Related
Jan 10, 2013
I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.
id, team, date, setnr, series
1, 3, 1/1/2013, 1, 1102
1, 3, 1/1/2013, 2, 1231
1, 3, 1/1/2013, 3, 1023
1, 3, 1/5/2013, 4, 1024
1, 3, 1/5/2013, 5, 1123
1, 3, 1/5/2013, 6, 1232
2, 2, 1/1/2013, 1, 1032
2, 2, 1/1/2013, 2, 1221
2, 2, 1/1/2013, 3, 1023
2, 2, 1/5/2013, 4, 1231
2, 2, 1/5/2013, 5, 1112
2, 2, 1/5/2013, 6, 1231
I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query
select sum(series), date from table group by date order by sum(series) desc
This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.
View 1 Replies
View Related
Feb 27, 2007
Hello experts,I'm trying the run the following query with specific intentions.I would like the query to return 5 results; i.e., 4 distinct and oneduplicate. I am only getting, however, 4 distinct records. I wouldlike the results from the '007' id to spit out twice.I'm not using 'distinct,' and I've tried 'all.' I realize that Icould put my 5 employee id's in a table and do a left or right join; Iwould like to avoid that, however. Any thoughts?SelectEmployee_last_name,Employee_first_name
Quote:
View 3 Replies
View Related
Feb 15, 2007
Hi,
I need some help with this please.
I have a database table which contains customer orders. I am trying to code my SQL select statement to:
1) Only return records where the record orderdate is within the last 30 days
2) Between two dates, selected from the datepicker control.
With regards to issue 1, I could fill a table with all the records for the account in question and then for each record do a datediff between the records order date and the current date to determine if the number of days is within 30 days. If yes then add this record to a temp table and then set this table as the datasource for the datagridview.
There must be a more efficient way?
With regards to issue 2) ?
View 1 Replies
View Related
Nov 21, 2006
Can anyone help me modify this sproc's Where clause or Joins to let (T) task records with a 0 to be returned?
If I enter a ClientID I want to return only those task records with the ClientID I entered (this works).
If I enter no ClientID I want to return all task records, even those with a 0 in the ClientID field.
ALTER PROCEDURE dbo.CMAdmin
@SID int
AS SELECT A.CompanyName, C.FirstName, C.LastName, C.ClientID,Convert(varchar(10), T.ActionDate, 10) AS [Action Date], T.Priority, T.Status, T.Subject, T.Note, T.CompletionDate, 10) AS Completed, T.DateEntered AS Entered, T.EnteredBy AS [Entered By],
CASE WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +' '+ C.[LastName] ELSE A.[CompanyName] END AS DRName
FROM tblClients C LEFT OUTER JOIN tblClientAddresses A ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T ON C.ClientID = T.ClientID
WHERE C.ClientID = Isnull(@SID,C.ClientID)
View 1 Replies
View Related
Feb 10, 2007
I've got a product table in SQL 2000 that contains say these rows:
ProductID intProductName varcharIsSpecial bit
I want to always return 4 random specials from a query and can do this fine by using:
SELECT TOP 4 ProductID,ProductNameFROM Products WHERE IsSpecial = 1ORDER BY NEWID()
This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.
What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?
Is this possible?
Thanks
View 2 Replies
View Related
Mar 1, 2008
Is there a way to limit the records that SqlDataSource returns with sql query, say records 10 to 20 only?
View 10 Replies
View Related
Nov 14, 2000
What is the preferred method of returning a set of records from a stored procedure? Could you please provide a short example?
Thanks ahead of time!
G.
View 3 Replies
View Related
Apr 28, 2008
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
View 11 Replies
View Related
Apr 17, 2008
Hi
I am looking to write a query that returns all records Inserted in the last hour.
The problem, as I see it, is that the column I need to refer to is a VARCHAR() datatype. Can I convert from varchar (example 14:04:31)to a time value and calculate from this ?
I would like to subtract 1 hour from current_timestamp or similar, so that the query dynamically changed.
Many thanks
View 16 Replies
View Related
Dec 1, 2007
The topic pretty much sums up my question.
What would the sql query be to return only all the records in a table that are 45 days old from today.
Thank you for any help in this matter
Al
View 5 Replies
View Related
Jan 18, 2008
Here is a very basic question that I have.
I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.
How can I pull back the records from table A that do not have a corresponding combination in B?
I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?
THANKS!
View 1 Replies
View Related
Nov 9, 2006
Hi,
I'm trying to retrieve some records from an SQL database.
I've a table named CustomerOrder with three fields - custID , productname and quantity
No keys in the table. it's row based approach. every custID can have multiple entries for different products.
Example:
CustID ProductName Quantity
1 Product1 2
1 Product2 3
2 XXX 1
2 Product1 2
1 Product3 4
I would like to write a query that gives the result as follows :
CustID ProductName Quantity
1 Product3 4
2 Product1 2
Meaning that, query has to retrieve only one record per custID based on highest quantity.
select custId,Productname,quantity from customerorder where quantity = (select max(quantity) from customerorder)
the above query returns only one record. (ofcourse..)
Kindly help me to get the desired.
Thank You.
View 5 Replies
View Related