comparing specific rows

Question

Post reply

comparing specific rows

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

achowe SSCrazy Points: 2075 More actions · Answer 1

Is there a way to assign each of the values, i.e. t1.datefield to a variable....

i beleive the code would be

DECLARE @var varchar(255)

set @var = t1.datefield

Is it possible to do this and if so where would i put the code?

Thanks guys

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hi Andrew,

quote:
Is there a way to assign each of the values, i.e. t1.datefield to a variable....
i beleive the code would be
DECLARE @var varchar(255)
set @var = t1.datefield
Is it possible to do this and if so where would i put the code?

you can declare the variables and assign like

select

@oldcpu_busy = cpu_busy, @last_run = sampletime

FROM

svrmonitor

WHERE

id=(SELECT MAX(id) FROM svrmonitor)

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 3

Where does this line of code come from:?id=

(SELECT MAX(id) FROM svrmonitor)

Cheers Frank

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Andrew,

quote:
Where does this line of code come from:?id=
(SELECT MAX(id) FROM svrmonitor)

originally from the keyboard, where I entered this

It's a subquery and as such explained in BOL in 'subqueries'

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 5

quote:
Is there a way to assign each of the values, i.e. t1.datefield to a variable....
i beleive the code would be
DECLARE @var varchar(255)
set @var = t1.datefield
Is it possible to do this and if so where would i put the code?

The point is this:

Since you are getting the value into a variable, (Which can hold only one value at a time) your select statement should return only one row.

Eg.

This code is wrong when table1 has more than 1 rows:

Declare @@var varchar(255)

select @var = t1.datefield from Table1 t1

However this is correct

Declare @@var varchar(255)

select @var = t1.datefield from Table1 t1

Where t1.Primarykey=8439

In Franks example, id is set to be the maximum id, and if you can have only one of that case it will work

G.R. Preethiviraj Kulasingham

Chief Technology Officer.

Softlogic Information Systems Limited,

14 De Fonseka Place,

Colombo 05.

Sri Lanka.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi Preethi,

quote:
The point is this:
Since you are getting the value into a variable, (Which can hold only one value at a time) your select statement should return only one row.
Eg.
This code is wrong when table1 has more than 1 rows:
Declare @@var varchar(255)
select @var = t1.datefield from Table1 t1
However this is correct
Declare @@var varchar(255)
select @var = t1.datefield from Table1 t1
Where t1.Primarykey=8439
In Franks example, id is set to be the maximum id, and if you can have only one of that case it will work

Oops, thanks for explaining this. To me it was obvious.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 7

Thanks guys, really appreciate your help

Cheers for the humour Frank! Must be the German hospitality that Im oh so fond of..Regards

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

quote:
Cheers for the humour Frank! Must be the German hospitality that Im oh so fond of..Regards

I had to look up hospitality in a dictionary.

Hospitality <=> Germany ????

Doesn't fit together very well, even if you are no foreigner in this country.

PLEASE, don't take this too serious or even political !!!

Must be my near-weekend sarcasm

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 9

I wasnt bein serious actually......all works now..isnt this forum a marvel?

Hip hip hooray!

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

achowe SSCrazy Points: 2075 More actions · Answer 10

Declare @@var varchar(255)

select @var = t1.datefield from Table1 t1

Where t1.Primarykey=8439

I presume 8439 is merely a random number youve picked out.........how can i make it so it works for any instance of the primary key....if u see what i mean........

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

achowe SSCrazy Points: 2075 More actions · Answer 11

I presume 8439 is merely a random number youve picked out.........how can i make it so it works for any instance of the primary key....if u see what i mean........

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

Hi Andrew,

quote:
I presume 8439 is merely a random number youve picked out.........how can i make it so it works for any instance of the primary key....if u see what i mean........

I don't think you get a quicker response if you post your question multiple times!

Here are two interesting links I've found some time ago on this

http://www.eyrie.org./~eagle/faqs/questions.html

http://perl.plover.com/Questions.html

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 13

Very dry Frank, thanks.

Next time I want to speak to a comedian Ill go on a joke site...

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Hi Andrew,

quote:
Declare @@var varchar(255)
select @var = t1.datefield from Table1 t1
Where t1.Primarykey=8439
I presume 8439 is merely a random number youve picked out.........how can i make it so it works for any instance of the primary key....if u see what i mean........

modify it like this

Declare @@var varchar(255)

Declare @id int

Set @id = <some_value>

select @var = t1.datefield from Table1 t1

Where t1.Primarykey=@id

...and it should work

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 15

No insult, Andrew!

quote:
Very dry Frank, thanks.
Next time I want to speak to a comedian Ill go on a joke site...
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

I like this classic

http://www.klawitter.de/enhumor.html :o)]

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]