Select Query - Get Top 3 For Repeating Area
Mar 12, 2014
The data as it's recorded in the SQL table looks like this:
IDbatchdate_timecompany_namesymbolscore
4670713/11/14 9:30 AMJ.C. PENNEY COMPANY INC.JCP97
4670813/11/14 9:30 AMZOGENIX INCZGNX97
4670913/11/14 9:30 AMEXCO RESOURCES INCXCO93
4671013/11/14 9:30 AMMARVELL TECHNOLOGY GROUP LTDMRVL91
4671113/11/14 9:30 AMCABOT OIL & GAS CORPCOG90
[code]....
Basically what I'm after, I need to pull only the top 3 records based on score in descending order BUT these should be grouped by batch. For example, I need to select the top 3 records which have the highest score from batch 1, 2, 3, etc. where there could be any number of records in a batch and any number of batches.
View 1 Replies
ADVERTISEMENT
Oct 25, 2007
What I want to do is select only records from my Orders table that have repeating values in the ORDREF column.
If the order method "web" repeats 3 or more times for any customer, I want to show it.
use ws_data
select company,invno,invdte, ordref from Orders
where ordref like 'web%' or ordref like 'dir%'
order by company, invdte
TIA
ck
View 2 Replies
View Related
Mar 22, 2005
Hi
I am working this stored procedure that retrieve the name of person and also the last day he did a report.
I only want to display people who had a date for their report.
I came up with this stored procedure that works but is not very optmized.
Do you have any idea of how I can optmize this stored procedure?
SELECT Tuserproject.etridperson, Tperson.name, Tperson.email,
(SELECT TOP 1 [day]
FROM TDailyReport
WHERE project = 7 AND TDailyReport.people=Tuserproject.etridperson
ORDER BY [day] DESC) AS lastday
FROM Tuserproject INNER JOIN
Tperson ON Tuserproject.etridperson = Tperson.idperson INNER JOIN
TDailyReport ON Tuserproject.etridperson = TDailyReport.people
WHERE (Tuserproject.etridproject = 7) AND (Tperson.login IS NOT NULL) AND
((SELECT TOP 1 [day]
FROM TDailyReport
WHERE project= 7 AND TDailyReport.people=Tuserproject.etridperson
ORDER BY [day] DESC) IS NOT NULL)
GROUP BY Tuserproject.etridperson, Tperson.name, Tperson.email
ORDER BY Tperson.name
Thanks
View 1 Replies
View Related
Dec 12, 2007
i have a query when i run it shows data rows repeating like 5 times. I am not sure what am i doing wrong. hope someone can help me out.
this is my query.
SELECT "INVOICEDETAILS"."SeviceAmount", "RptSalesUser"."SalesPersonId", "RptSalesUser"."SalesPersonName", "RptSalesUser"."TemplateStatus", "INVOICE"."InvoiceDate"
FROM ("imacstest"."dbo"."INVOICE" "INVOICE" INNER JOIN "imacstest"."dbo"."INVOICEDETAILS" "INVOICEDETAILS" ON "INVOICE"."InvoiceNo"="INVOICEDETAILS"."InvoiceNumber") INNER JOIN "imacstest"."dbo"."RptSalesUser" "RptSalesUser" ON ("INVOICE"."InvoiceNo"="RptSalesUser"."invoiceNumber") AND ("INVOICEDETAILS"."InvoiceNumber"="RptSalesUser"."invoiceNumber")
WHERE "RptSalesUser"."TemplateStatus"='A' AND ("INVOICE"."InvoiceDate">={ts '2007-01-01 00:00:00'} AND "INVOICE"."InvoiceDate"<{ts '2007-12-13 00:00:00'}) AND "RptSalesUser"."SalesPersonId"=5 and "Invoice"."InvoiceNo"='1004447' ORDER BY "RptSalesUser"."SalesPersonId","Invoice"."InvoiceDate"
when i execute this i get about 12 data rows which are fine but then the same 12 data lines repeats 4 more times. hope someone be able to help me quick.
View 17 Replies
View Related
Apr 15, 2008
I have a table called Customers which holds the customers details including CustID primary key a table called DVDs which holds all the dvd details including DVDID primary key and a table called WishList which holds the CustID and DVDID I want to take the CustID given my the user and display all the dvds in the wishlist with that CustID the code I have now is int CustID = Convert.ToInt16(Session["CustID"]); comm = new SqlCommand("SELECT DVDs.DVDID, dvds.Title, Director, Actor, SUBSTRING(Description,0,200) AS Description, Image FROM DVDs, Customers, WishList WHERE WishList.CustID = @CustID AND WishList.DVDID = DVDs.DVDID", conn); comm.Parameters.Add("@CustID", SqlDbType.Int).Value = CustID; but when i run the query it displays each dvd 5 times not sure why can any one help cheers
View 2 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
View 1 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
View 5 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
Sep 6, 2006
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
Oct 23, 2007
I have a list that is grouped by Department inside it I have a table with two columns: name and hours. For example:
Department A
Name Houres
Mike 1
Department B
Name Houres
Mike 1
Steve 1
Department C
Name Houres
Mike 1
Steve 1
Outside the list I have another table that has a column for total hours for all departments:
Which in this case is 5. Everything work fine, but I have problem with displaying the total hours for all departments. In other words this will be my output
5
5
5
5
5
5
5
5
5
5
5
5
It will be repeated multiple times. I believe I have to add group to the table that contains the column (total hours for all departments.) but I can€™t do that b/c It is the total for all departments. Also I tried to hide duplicate wich works, but the problem with that when I export it to pdf it will print empty pages.
Any thoughts!
Thanks
View 1 Replies
View Related
Jul 9, 2002
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
View 1 Replies
View Related
Jun 4, 2008
This is actually not asp.net, but I'm hoping someone can help me. I have a report that is pulling from a couple of different tables. I am trying to add a meal choice to the report. Let's say they have the option of choosing chicken, beef or fish. They check the checkbox next to the choice. My report is pulling the infor, but it is putting 3 rows for each person not taking into consideration what choice they chose. The checkbox's all write to the same column - ses. Here is the code for the stored procedure:
CREATE PROCEDURE [dbo].[spGetCourseEmailList1]( @Code1 char(9)) AS SELECT DISTINCT dbo.[names].lname as LastName, dbo.[names].fname as FirstName,dbo.[evser].ses as MealChoice,dbo.[evldg].paid as AmountPaid, dbo.[names].gp as PreferredAddress, dbo.[names].mi as MiddleInitial, dbo.[names].nname as NickName, dbo.[names].xname as Suffix, dbo.[names].hphone as HomePhone, dbo.[names].email as EmailAddress, dbo.[names].addr1 as HomeAddress1, dbo.[names].addr2 as HomeAddress2, dbo.[names].city as City, dbo.[names].st as State, dbo.[names].zip as ZipCode, dbo.[firms].fname1 as FirmName1, dbo.[firms].fname2 as FirmName2, dbo.[firms].faddr1 as FirmAddress1, dbo.[firms].faddr2 as FirmAddress2, dbo.[firms].fcity as FirmCity, dbo.[firms].fst as FirmState, dbo.[firms].fzip as FirmZip, dbo.[firms].fphone as FirmPhone, dbo.[names].udflist1 FROM dbo.[firms] INNER JOIN dbo.[names] ON dbo.[firms].firm = dbo.[names].firm
INNER JOIN dbo.evldgON dbo.[names].id = dbo.[evldg].id
INNER JOIN dbo.evregON dbo.[evldg].id = dbo.[evreg].id
Full OUTER JOIN dbo.evserON dbo.[evreg].code1 = dbo.[evser].code1
WHERE dbo.[evldg].code1 = @Code1 AND dbo.[evreg].code1 = @Code1 AND dbo.[evser].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'ORDER BY dbo.[names].lname, dbo.[names].fnameGO
The items in bold are what I added.
View 3 Replies
View Related
Feb 11, 2005
When using Access - you had to be careful about the autoincrement feature. If you delete a record from a table ( autoincrement id=1000) -then compact/repair - then add a new record to that table - the autoincrement field will say 1000 - if that autoincrement value was used to uniquely identify something - it is no longer unique. This is all background for my question...
Does SQL 2000 do this also ? I must have read somewhere that it doesn't - since I have code that moves records around ( delete from one table - insert into other), but the other night, I was awakened by the thought that SQL2000 does the same as Access - i.e. repeating identity after compact/repair
Do I need to worry ?
View 5 Replies
View Related
Jun 7, 2004
How can I display same repeating record in a table?
Any help will be appreciated.
View 3 Replies
View Related
May 18, 2007
Hi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.
CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );
My statement
SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;
the result is
ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5
The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?
View 3 Replies
View Related
Feb 12, 2008
Hi everybody!
I've got this little problem.
I need to insert data from a table to another table.
The scenario looks as follows:
I've got 'Company' table (no duplicated records there) and 'Contacts' table (one to many relation: for one company there can be more than one contact).
The following statement retrieves the data but it shows me everything, including all contacts and therefore I get duplicating values, e.g. company name.
Is there any way of changing the following query so it works?
INSERT Projects(
CompanyID,
ContactID,
CustomerName,
EntryTime,
SetupFee,
ForecastRevValue06_07,
ClosureProbabilityID,
ExpectedClosingDate,
TechnologyID,
Service_ProvidedID,
Dropped,
AgreementTerm,
AgreementTermDisplayOnly,
ForecastStartDate,
ForecastEndDate,
LongTerm,
UserID
)
SELECT Distinct
CI.CompanyID,
CC.ContactID,
--CC.FirstName + ' ' +CC.LastName,
getDate(),
0,
0,
5,
DateAdd(month,2,getDate()),
2,
1,
0,
2,
'2 month(s)',
DateAdd(month,2,getDate()),
DateAdd(month,4,getDate()),
0,
2
FROM
CompanyInfo CI
left outer JOIN CompanyContacts CC ON
CC.CompanyID = CI.CompanyID
Kind Regards
View 11 Replies
View Related
Mar 17, 2008
Hi.,
I dont know to eliminate the repeting record in the ID column how to do that.,
for ex., i have given an example.,
create table Example (ID int, Name varchar(30))
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Chirag')
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Shailesh')
INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Dipak')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush')
select * from Example.,
i will get.,
ID Name
----------- ------------------------------
1 Chirag
1 Shailesh
2 Dipak
4 Mihir
4 Piyush
.....
but i need like
ID Name
----------- ------------------------------
1 Chirag
Shailesh
2 Dipak
4 Mihir
Piyush
I dont want repeated ID., How can i do that.,
View 14 Replies
View Related
Jul 23, 2005
Any help on how to get rid of repeating records
View 1 Replies
View Related
Apr 24, 2008
I have a store procedure that pulls info for a meeting coming up. They can choose their meal choice which in this case is fish, chicken, beef. What is happening on the report, it's listing each person 3 times with each choice no matter which is was. How do I correct this? Code below:
CREATE PROCEDURE [dbo].[spGetCourseEmailList1]( @Code1 char(9)) AS SELECT DISTINCT dbo.[names].lname as LastName, dbo.[names].fname as FirstName, dbo.[evser].ses as MealChoice, dbo.[evldg].paid as AmountPaid, dbo.[names].gp as PreferredAddress, dbo.[names].mi as MiddleInitial, dbo.[names].nname as NickName, dbo.[names].xname as Suffix, dbo.[names].hphone as HomePhone, dbo.[names].email as EmailAddress, dbo.[names].addr1 as HomeAddress1, dbo.[names].addr2 as HomeAddress2, dbo.[names].city as City, dbo.[names].st as State, dbo.[names].zip as ZipCode, dbo.[firms].fname1 as FirmName1, dbo.[firms].fname2 as FirmName2, dbo.[firms].faddr1 as FirmAddress1, dbo.[firms].faddr2 as FirmAddress2, dbo.[firms].fcity as FirmCity, dbo.[firms].fst as FirmState, dbo.[firms].fzip as FirmZip, dbo.[firms].fphone as FirmPhone, dbo.[names].udflist1 FROM dbo.[firms] INNER JOIN dbo.[names] ON dbo.[firms].firm = dbo.[names].firm
INNER JOIN dbo.evldgON dbo.[names].id = dbo.[evldg].id
INNER JOIN dbo.evregON dbo.[evldg].id = dbo.[evreg].id
INNER JOIN dbo.evserON dbo.[evreg].code1 = dbo.[evser].code1
WHERE dbo.[evldg].code1 = @Code1 AND dbo.[evreg].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'ORDER BY dbo.[names].lname, dbo.[names].fnameGO
THANKS!!!
View 7 Replies
View Related
May 3, 2006
hello i'm using asp.net 2 with VB and i have a table in my db the problem is that table has a repeated records like this imagenow i want to update the absence record but cos it's repeated it will only update one record what i want is to apply the update on all other records ? hope u can help me .thanks
View 6 Replies
View Related
Apr 29, 2004
I would like to know how to produce the following in a query.
Parent Child
---------------
Dave Sarah
.......... Brad
.......... Alice
.......... Hanna
Rather than:
Parent Child
---------------
Dave Sarah
Dave Brad
Dave Alice
Dave Hanna
View 5 Replies
View Related
Jul 20, 2005
I am a developer who works with MS SQL Server a lot, but I'm far froman expert. I am revamping an appointment scheduling system thatallows for appointments to reoccur daily, weekly, monthly and yearly.Currently, I am saving the appointment date as the initial appointmentdate. Then when I want to check for appointments, my stored proc doesdoes a select on the appropriate records fitting certain critieria(like only appointments for this doctor, at this location, etc). OnceI have these records I cycle through them calling the DateAdd() andDateDiff() functions to see if the appointment is reoccuring duringthe dates I'm looking for.Here's is a mock up of what I'm doing. I know cursors are a huge hitperformance-wise (especially how they are used in this scenario) andwant to get a way from this, but I can't figure out how to getreoccuring appointments to work. Any help is appreciated. Thanks.sp_GetAppointments(@StartDate, @EndDate)set @DateToCheck = @StartApptDatewhile @DateToCheck <= @EndApptDatebegin--Start a cursorDECLARE RepeatCursor CURSORFORWARD_ONLY STATIC FORselect ApptDate from ApptTable where DoctorID = 1 and--Check if it repeats daily((repeat = 1 andDateAdd(d,DateDiff(d,ApptDate,@DateToCheck),ApptDa te) =@DateToCheckand DateDiff(d,ApptDate,@DateToCheck) >0)--Check if it repeats weeklyor (repeat = 2 andDateAdd(wk,DateDiff(wk,ApptDate,@DateToCheck),Appt Date) =@DateToCheckand DateDiff(d,ApptDate,@DateToCheck) >0)CLOSE RepeatCursorDEALLOCATE RepeatCursorset @DateToCheck = DateAdd(d,1,@DateToCheck)end
View 4 Replies
View Related
Nov 29, 2007
Ok, so I've been racking my brains on this one for a while now and figured it was time to ask for some help...
I have a table named tblAppInfo that looks something like this:
Code Block
user_name app_name app_ver
User1 MS Word 2000
User2 MS Word 2000
User3 MS Excel 2000
User4 MS Excel 2000
User5 MS Excel 2000
User6 MS Excel 2000
User7 MS Outlook 2000
User8 MS PowerPoint 2000
User9 Adobe Acrobat 5.0
User10 Adobe Acrobat 6.0
User11 Adobe Acrobat 7.0
What I want to do is remove any rows that repeat the app_name and app_ver more than twice. Basically in the example above, it would remove the lines that have MS Excel 2000 (as it's listed 4 times). MS Word 2000 would stay as it's only listed twice. As would Outlook and PowerPoint. All 3 Adobe's would also stay as their versions are different.
I've tried the following but it still repeats applications that occur more than twice:
Code BlockSELECT user_name, app_name, app_ver
FROM tblAppInfo
GROUP BY app_name, app_ver, user_name
having count(distinct app_name + app_ver + tblAppInfo.host_name) < 3
ORDER BY app_name, app_ver
I've also tried using nested select statements and everything else in my arsenal to no avail. I hope someone here will be able to help me out as I'm afraid I'll go bald if I try anymore!
Thanks in advance!
Tom
View 6 Replies
View Related
Sep 4, 2007
Hi
Pulled all my hair out now, can someone please offer some help before I go totally mad...
I have no page header, this is all in the body. I have a List with account number and job number in it at the top, called list1. In list1 I have list2, with the job details, basically a list of people who attended the job. The fields in list2 are textboxes, they're not in a table.
EG:
Acc No: 12345
Job No: 54321
Name
Fred Bloggs
Joe Bloggs
Etc
When my list2 of names goes over 1 page, I want to repeat the account number and job number on each subsequent page. In my real report I actually have 10 fields I want to repeat.
I then want to be able to export this report to a PDF.
I can't put the header in the page header and RepeatWith doesn't work when exporting to PDF, or at all depending on what mood it's in.
Does anyone have a workaround for this please?
Cheers
View 2 Replies
View Related
Dec 14, 2007
I have the option for repeating table headers for every page turn on as I would like the table headers repeated on every page. In the HTML report view, this works fine, however when the report is exported to pdf (a total of 80 some pages), when there is a table that contains a lot of data, the table headers are not displayed. I know this could be because the table includes a subreport and sometimes the subreport runs more than one page, but shouldn't the table header still be diplayed on the new page?
Is there something else that I need to check or should I move the table/column header information somplace else?
Thanks for any information.
View 1 Replies
View Related
Dec 3, 2007
Hi,
I have a matrix which displays like this
Title1 A Book
Another Book
Title2 Yet another book
My Book
How can I display it like this:
Title1 A Book
Title1 Another Book
Title2 Yet another book
Title2 My Book
So that when I export this to excel I can sort and filter data easier. Thanks very much.
View 4 Replies
View Related
Aug 3, 2007
I have run into a strange issue that I believe is a SQL Reporting Services issue.
I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.
The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.
When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.
What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.
I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.
If anyone has run into this and found a work around, let me know.
Thank you,
T.J.
View 1 Replies
View Related
Sep 6, 2001
Does anyone know if SQL7 will work with Storage Area Networks(SAN's)? I've read that SQL2000 implements something called a Virtual Interface System Area Network (VI SAN) that allows communication with devices connected via a SAN.
My site is installing a SAN and I need to know if SQL7 can utilize those resources (Storage,etc) and how reliable if so.
Randy
View 1 Replies
View Related
Jan 29, 2004
I have files with area codes that are several years old. Everything I've seen about updating area codes deals with area codes that are current and are about to split in the near future. How would I go about bringing old area codes up to date?
View 8 Replies
View Related
Apr 14, 2008
Dear All,
i'm trying to publish a maintenance plan in sql server 2005.
but soon after right click and selecting a new maintenance plan, it is displaying an alert with "AgentXP's component" is turned off.
please let me know how to ON those services. i've read and followed the instructions but i was unable to find where exactly to start.
please guide me in this regard
my version of OS is sql server 2000 professional
my version of sql server is :Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 1 Replies
View Related
Feb 28, 2007
Hi, we are having a strange problem when trying to configure the surface area of my sql server 2005. When we open the tool the only nodes that appears are: Reporting Services and Integration Services. But the Database Engine node is not showing. What do we have to do to access the Database Engine node and configure Remote Connection.
Any help would be extremely appreciated...
View 12 Replies
View Related
Feb 20, 2008
Is it possible to display fields from 2 datasets in one table?
View 3 Replies
View Related
Aug 2, 2006
We have the need to determine surrounding counties of a particular county.
For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa
Could it be determined by the longitude and latitude of the zip code in comparison with the counties?
View 1 Replies
View Related
