newbie question on select

Hi All,
I need to get records vayring from 1 to 100, What is the best way without g
etting one record at a time.
Is the only other way by generating a select command with In Statment each t
ime,
But this will make the string very long.
Any help will appreicated
Thank You.Sound like you need a server side cursor / paging solution for your
problem:
http://www.google.com/search?hl=de&...ql+server&meta=
There are tons of hits on the internet, perhaps you take a deeper look
in the examples to decide for one.
HTH, jens Suessmeyer.|||Please post your DDL plus sample data and expected results. Do you want the
rows where a particular column is in the range 1 - 100? If so, try:
select
*
from
MyTable
where
MyCol between 1 and 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
Hi All,
I need to get records vayring from 1 to 100, What is the best way
without getting one record at a time.
Is the only other way by generating a select command with In Statment each
time,
But this will make the string very long.
Any help will appreicated
Thank You.|||What version are you using ?
SQL Server 2005
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
score INT NOT NULL,
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 1)
INSERT INTO SpeakerStats VALUES('Ron', 2)
INSERT INTO SpeakerStats VALUES('Kathy', 3)
INSERT INTO SpeakerStats VALUES('Suzanne', 4)
INSERT INTO SpeakerStats VALUES('Joe', 5)
INSERT INTO SpeakerStats VALUES('Robert', 6)
INSERT INTO SpeakerStats VALUES('Mike', 7)
WITH myCTE (rownum,speaker,score)
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, score
FROM SpeakerStats
)
SELECT * FROM myCTE WHERE rownum BETWEEN 5 AND 7
ORDER BY rownum DESC
SQL Server 2000
SELECT * FROM
(
SELECT * ,(SELECT COUNT(*) FROM SpeakerStats S
WHERE S.speaker<=SpeakerStats.speaker)rownum
FROM SpeakerStats
) AS Der WHERE rownum >=5 AND rownum <8
ORDER BY rownum
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||We need more information on what you're trying to do. An example that we
could work with would be more helpful.
Regards
Colin Dawson
www.cjdawson.com
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||Sorry, I have posted this in the wrong group, it should have posted it to th
e Access group.
I have table with 500 employee details depending on the user selection it c
an be between
1 and 100 emp records of the 500 records not necessarily consecutive record
s.
I will google with link provided.
Thank you all for the quick replies.

Newbie Q: Executing StoredProc?

hi all,
i just wonder if below storedproc will update record correctly if executed
concurrently by multiple user using
varying parameter value or it containe logic error.
CREATE PROCEDURE UpdateQTY @.QTY int
AS
UPDATE PRODUCT SET Quantity = Quantity + @.QTY
GO
Hi,
The Syntax of the Storeprocedure (SP) shows that it would update all the
records in the Product table.
Is this what you want to achieve ?
If not, then add a where clause in the Update statement, where it would
contain one or more columns, that would together select a distinct row.
for example
CREATE PROCEDURE UpdateQTY @.QTY int,@.ProductID int
AS
UPDATE PRODUCT SET Quantity = Quantity + @.QTY
where productid = @.productid
GO
The above example would find a row with the matching Productid and update
the Qty field for it.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
I missed one point in your question.
There will be no problems if multiple users call the SP simultaneously to
update the Product Table.
One more thing, is your QTY as integer or a numeric column. If it requires
to store decimal, then setting the parameter as int would make it to loose
its accuracy. So just make sure that the datatype of QTY matches that in
the table definition.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||thanks for the response

newbie needs help with @@identity

I have a form that submits to multiple tables. After insertion into the first table I need to access the identity key from the record and use is to associate a record in another table. The form element I'm inserting into the second table however, is not a required field so I think I need to check IS NOT NULL first. In my code below I have copied the insert statement for the first table and the conditional and subsequent insert into the 2nd table. I am uncertain where and how I get and use @.@.identity. The error I'm getting when I run the Check Syntax button is: 'incorrect syntax near @.@.identity.'

I appreciate someone telling me how to correct my syntax.

INSERT INTO GPRA_Activities
(
SubmitDate,
StaffId,
GPRAId,
FreedomID,
DocumentDesc,
ActivityTitle,
ActivityDesc

)
VALUES
(
getDate(),
@.StaffId,
@.GPRAId,
@.FreedomID,
@.DocumentDesc,
@.ActivityTitle,
@.ActivityDesc

SELECT @.@.identity
)

if @.KeywordId1 IS NOT NULL

@.@.identity smallint,

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.@.identity,
@.KeywordId1
)

GO

You need to get the value of @.@.IDENTITY Into a local variable and use it. You cannot use the @.@.IDENTITY by itself.

Declare @.valintINSERT INTO GPRA_Activities(SubmitDate,StaffId,GPRAId,FreedomID,DocumentDesc,ActivityTitle,ActivityDesc)VALUES (getDate(),@.StaffId,@.GPRAId,@.FreedomID,@.DocumentDesc,@.ActivityTitle,@.ActivityDesc)SELECT @.val = SCOPE_IDENTITY()if @.KeywordId1ISNOT NULL-- @.@.identity smallint, I dont know what you are trying to do hereINSERT INTO GPRA_KeywordsUsed ( ActivityId, KeywordId )VALUES ( @.val, @.KeywordId1 )GO

|||

I finally got the SQL code below not to error (though I haven't been able to submit my form yet. Keep getting error message about expected number of parameters. That one will be my nemesis.

What is the difference between @.@.identy and SCOPE_IDENTITY?

DECLARE
@.ActivityId smallint
SELECT @.ActivityId = @.@.Identity

if @.KeywordId1 IS NOT NULL

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.ActivityId,
@.KeywordId1
)

|||

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

I'm new at this so please forgive my ignorance.

So, if I use @.@.identity and there are multiple users of the application at once, could the wrong identity get "grabbed"?

|||

Possible. HEre's some info from Books on line:

For example, there are two tables,T1 andT2, and an INSERT trigger is defined onT1. When a row is inserted toT1, the trigger fires and inserts a row inT2. This scenario illustrates two scopes: the insert onT1, and the insert onT2 by the trigger.

Assuming that bothT1 andT2 have identity columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement onT1. @.@.IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted inT2. SCOPE_IDENTITY() will return the IDENTITY value inserted inT1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

|||thank you. I'll change it.

newbie - Most Recent Records from multiple tables

Hi,

I'm trying to create a view or TSQL statement to return in one recordset...

a) the most recent record of a PK in table1 [foodRecipes]

b) the most recent record (if exists) of FK from table 1 with the PK from table2

Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...

example:

table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)

foodGroup [nvarchar (50)]

recipeName [nvarchar (50]

recipeDateModified [datetime]

cupsOfSugar [float]

sampleData:

cookies, peanutButter, 3/3/2007, 1.5

cookies, peanutButter, 3/4/2007, 2.0

cookies, sugar, 3/3/2007, 5.0

table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)

foodGroup [nvarchar (50)] ...FK from table1

recipeName [nvarchar (50] ...FK from table1

recipeDateModified [datetime] ...FK from table1

historyDateModified [datetime]

cupsOfSugarHistory [float]

sampleData:

cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5

cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0

cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2

What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)

SELECT * FROM myRecipies

sample Resultset:

foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory

cookies, peanutButter, 3/4/2007, 2.0, 2.2

cookies, sugar, 3/3/2007, 5.0, <NULL>

What I've got now:

1. TSQL that gives me back the most recent recipes (No History yet)

SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

...and this works great, I get back each unique recipe from table #1, the most recent...

anyone good at this?

thanks in advance,

bsierad

You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)

Anyway, try the query below to see if this is what you're after.

Chris

SELECT foodGroup,

recipeName,

recipeDateModified,

cupsOfSugar,

CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,

(SELECT TOP 1 frh.cupsOfSugarHistory

FROM dbo.foodRecipeHistory frh

WHERE frh.foodGroup = oher.foodGroup

AND frh.recipeName = oher.recipeName

AND frh.recipeDateModified = oher.recipeDateModified

ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory

FROM dbo.foodRecipes oher

WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN

(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))

FROM dbo.foodRecipes

WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

|||

Thanks!

Works great...and I can soak this in and apply it in other areas...

My real fields don't taste this good...machineGasFlow sounds pretty boring...

Can't thank you enough,

bsierad

Newbe to SPs

Ey,
I need to do few operations on every record in a table. Do I have to use cursor? Do SQL has something like 'FOR' or 'WHILE'? I've read somewere that cursors should be avoided due to their time consumption.
ThnxTell us what you need to do ... before we can comment on whether you need a cursor or not.

It would be helpful if you paste some DDL ... and sample data|||That was qucik :)
Im trying to write proc that would be run on a daily basis. It would have to deal with around 12000 records. What it has to do is to take data from flat, multicolumn table, check for some conditions and spread them into real relational db.
Lets say people input things into that flat table and they often misspell i.e. city names due to fast input. It gotta take a record, chceck if inserted city name exists in cities table in db, if not it goes to the missspelled names table and chcecks if it exists there, if not again it adds a new record to missspelled table and lights a flag to inform admin and he could make a decision if to move it to cities table or leave it in misspelled.
Same thing would happen for few columns in every row.

I hope I made myself clear enough...

Thnx again.