SELECT TOP N Queries In SQL 7

Jul 28, 1999

I'm having problems executing TOP n queries on a database that was migrated from 6.5 to 7. I can get it to work on the Authors table in pubs, not in my other dbs. Here is an example:

CREATE TABLE dbo.tblsapParentCust (
Parent char (10) NOT NULL ,
Name varchar (40) NULL,
IsSoldTo bit NOT NULL DEFAULT 0,
CONSTRAINT PK_tblsapParentCust PRIMARY KEY CLUSTERED
(Parent)
)
GO

<load in some data>

SELECT TOP 10 * FROM tblsapParentCust

The select statement results in a syntax error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '10'.


I can switch over to pubs and change the query to reference the Authors table, and it runs fine.

If anyone can explain this behavior to me, I would appreciate it.

Thanks,
Buddy

View 1 Replies


ADVERTISEMENT

Typecasting In Select Queries

Jan 28, 2005

Hi,

In my database I have a field with the type decimal. In the select query I want to return true if this field is smaller than 1 and false if this field is 1. How can I do this?

I need something like that:

Select id, name, (mydecimalField < 1) from mytable

KaaN

View 2 Replies View Related

Insert Into From 4 Select Queries

Oct 8, 2014

I have 4 tables which I am extracting 2 distinct values;

Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4

Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)

Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .

View 2 Replies View Related

Select Into Using Dynamic Queries

May 5, 2008

I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion.

Can someone help me out on this.

EG
sp_executesql N'Select top 1 * into #tmp from table1'
select * from #tmp

View 5 Replies View Related

Create A Table From Select Queries.

Feb 6, 2006

Hi,

I wanted to know a query which will create a final result table from a combination of select queries.

The select query is like :
1. select col1 , col2 , null from table1
2. select null , col2 , null from table2
3. select null , null , col3 from table 3.

null are inserted as i wanted a single select query which will merge all the columns from all the tables and finally create a result table.

Thanks in advance.

View 1 Replies View Related

Total Of Your Counts In Select Queries

Mar 19, 2007

I wrote a simple select query that counts the number of records I have in certain zip codes. How can I get a total of the "count" column at the bottom of the results? For example, my results may look like this:

ZIP | (no column name for "count")
_____________________________________
89502 | 10
89509 | 15
89521 | 25

What statement would I use to get the total of '50' displayed in the resluts? Thank you in advance

-Lance

View 6 Replies View Related

How To Form SQL Select Queries Using Drop Down Lists??

Nov 8, 2006

Hi
 Will somebody please explain how  to combine asp.net dropdown lists to write
a  SQL database select query. I am using VWdeveloper and C Sharp.
For example, say I have  3 dropdownlists on my  webpage  as below,
List 1, Cities, London, Rome,  Barcelona etc
List 2, Restaurants by  Type, Italian, chinese, Indian etc
List 3, Number of tables/ seats 10-20,  20- 40, 50  -100
I want someone to be able to  search for a restaurant by selecting  an  item from  each dropdownlist
such as, "Barcelona" "Italian" "50-100"
This search query would return all the Italian restaurants in Barcelona with  50-100  tables/seats.
I  would also like the select query to work even if one of the dropdownlists items  is not selected.
Hope  somebody can clear this up?
Also would sql injection attacks be a threat by doing it this way?
Thanks all
 
 
 

View 9 Replies View Related

Can I Write Multiple 'Select' Queries In Access? Please Help!

Jul 22, 2004

I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)

I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.


Code:


SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)

SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;

drop table dupContactKeys



This of course doesn't work. Please help, as I am going slightly mad!

View 5 Replies View Related

3rd Party App For Building Select Queries Faster?

Feb 13, 2008

I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.

What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?

TIA.

View 7 Replies View Related

Select Max Values From Queries For Multiple Schedule_Number

Jan 8, 2008

I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number

View 1 Replies View Related

Select, Insert And Delete Queries Timing Out

Jul 20, 2005

I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Massive UPDATE And SELECT TOP 1 QUERIES, Slowing Down...

Apr 10, 2007

Background

SQL Server 2005 Standard 9.0.1399 64bit

Windows 2003 64-bit

8gb RAM

RAID-1 70gb HD 15K SCSI (Log Files, OS)

RAID-10 1.08TB HD 10K SCSI (Data Files)

Runs aproximately _Total 800 Transaction/Second

We deliver aproximately 70-80 million ad views / day



8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites

All 8 servers talking to the one SQL server via a private network (server backbone).



In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:



select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits



and



UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;



Where the hits number is incremented by one each time that is selcted for that keyword ID.



Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.



Any ideas on why this is happening?



Regards,

Joe







View 6 Replies View Related

How Can I Do Amalgamate 3 Select Queries And Then Get Unique Entries From The Result

Mar 7, 2006

Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie

View 1 Replies View Related

All Select Queries From Stored Procedure Not Appearing Under Dataset

Jan 29, 2008

I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.

But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.

i am not seeing the remaingin 3 resultsets, on the reports dataset.

Is it possible on the reports or not.

In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.

Thank you all very much for the information.

View 1 Replies View Related

Integration Services :: Joining Two Select Queries Results In One Row?

Jun 12, 2015

I want to get output of below query in single row.

Select 'Name'
Select 'Surname'

Expected output is Name,Surname

View 6 Replies View Related

Parameter Information Cannot Be Derived From SQL Statements With Sub-select Queries

Apr 24, 2006

Parameter Information cannot be derived from  SQL statements with sub-select queries. Set Parameter information before preparing command.

Here's the query:

update GCDE_SEQ
set LAST_NO =  (select  max(FLD_NO)
   from PONL_FLD)
 ,UPDT_USER = ?
 ,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'

Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?

View 8 Replies View Related

Select Top 10 Used Select Queries

Apr 28, 2008

I found out how to select the top 10 used stored procedures. But how do you select the top 10 select statements that are used on the system or database? I had a DBA show me this one time but forgot it after that job. Thanks

View 1 Replies View Related

SQL Server 2008 :: Select Queries With Join Sometimes Fail On Some Remote PCs

Jul 22, 2015

I have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.

I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.

There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.

View 5 Replies View Related

Parameterized Queries Running Slower Than Non-parameterized Queries

Jul 20, 2005

HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)

View 1 Replies View Related

How To Run Queries???

Aug 9, 2006

Hi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.   
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
 
Regards,
 
 

View 1 Replies View Related

Is It Possible To Put Several Queries Into One Sp

Dec 7, 2007

 I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this.  previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants. 

View 4 Replies View Related

2 Queries Together

Dec 18, 2007

 how can i execute  for example



2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product  if i execute such one  how can i get the 2 results in vb/c#  ?

View 3 Replies View Related

Queries Or SP

Jun 18, 2004

hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code

View 3 Replies View Related

Sql Queries

Oct 21, 2004

Hi All,

Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.

All I have is a normal query (below). Can anyone help me please?

SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;

Regards,

Rich

View 2 Replies View Related

Please Help In Queries

Jan 23, 2006

Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.

View 1 Replies View Related

Queries

Jul 16, 2002

Could anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please

View 1 Replies View Related

Queries In SQL

Aug 3, 2000

I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?

Thank you all very much in advance.

View 5 Replies View Related

Mdx Queries

Feb 7, 2003

Hi!
Where to write MDX Queries and how to call the query in Analysis Services.

Please help me on this.

View 2 Replies View Related

SQL Queries...

May 1, 2001

I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...

Any ideas? Thanks.

View 1 Replies View Related

Two Queries Necessary?

Oct 12, 2007

Hi and thanks for reading.

I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:

Code:


SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk,
eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate,
l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription
FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk
LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk
LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID
LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID



This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows.
But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question).
Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.

My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (".
But, I hate using two queries and just wonder if there's a better way around this.

Appreciate the help, and thanks for reading.

-colin

View 1 Replies View Related

What To Use For SQL Queries?

Feb 15, 2007

I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)

Is there some other tool I can use for SQL queries?

Thanks.

View 5 Replies View Related

Sql Queries

Jul 2, 2007

It is given the following schema of relations:

Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
City(IdCity <<PK>>, Name, State);

I need to find a query for each of the following:

1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.

My solution:
SELECT d.Name, d.Surname
FROM Donators d, Filles f
WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45

2. average age of the plasma donators

SELECT AVG(d.age)
FROM Donators d, Files f
WHERE f.type='plasma'

3. the states which have more donators

I have no idea how to do it

4. the id's of the plasma donators from Chicago

SELECT d.id
FROM Donators d, Files f, City c
WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'

5. the id's of the cities without plasma donators

SELECT c.idcity
FROM City c
WHERE NOT IN (SELECT c.idcity
FROM City c, Donators d, Files f
WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)

6. remove the donators older than 80, after having removed the corresponding files

No idea how to do it

In conclusion, could you tell me if my queries are correct and help me with the others?

Thanks

View 14 Replies View Related

SUM Of Two Sub-queries

Jun 26, 2012

Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:

cust_id int
dateon DATETIME
dateoff DATETIME

There are two possibilites

1) Its no longer on hold and so a dateon and dateoff value will be in the table.

2) Its still on hold and so dateoff will be NULL.

I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802

SELECT Hon,Hoff FROM

(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
FROM dbo.tblonenet_sdt_proj_hold
WHERE cust_id='802' AND dateoff IS NULL) AS HON

[code]...

The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved