All, I have a table containing an ID and a text column. I need to dump the
content of the table into a text file using the following code:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
set textsize 62713
DECLARE @.ptr varbinary(16)
SELECT @.ptr = textptr(note)
FROM notes
WHERE id = 307
READTEXT note @.ptr 0 62713
COMMIT TRAN
GO
However, I get only 8K out of the text column where I am suppose to get
62713. I ran OSQL to re-direct the output to a file.
thanks.
JohnHi
Query Analyser has a maximum column width or 8192 characters and you can not
increase the size beyone that in the options dialog. Although I have not
found anything to say that osql has a similar limit I can get 8342 character
s
out.
To write to a file correctly look at
http://support.microsoft.com/defaul...kb;en-us;317043
John
"John Smith" wrote:
> All, I have a table containing an ID and a text column. I need to dump the
> content of the table into a text file using the following code:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> GO
> BEGIN TRAN
> set textsize 62713
> DECLARE @.ptr varbinary(16)
> SELECT @.ptr = textptr(note)
> FROM notes
> WHERE id = 307
> READTEXT note @.ptr 0 62713
> COMMIT TRAN
> GO
> However, I get only 8K out of the text column where I am suppose to get
> 62713. I ran OSQL to re-direct the output to a file.
> thanks.
> John
Showing posts with label containing. Show all posts
Showing posts with label containing. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
help with Query please
Hi,
I have a table with (sports) results, containing an userID, EventId and
a Time for each result recorded.
How do I select a list ordered from fastest to slowest containing the
fastest time for each userID recorded ? The challenge here, that I don't
understand how to do is to not get a list of all results for an event,
but only a single entry for each UserID with this userIDs fastest time.
Is there any way to do this except looping through each user ID from my
front end code and selecting the fastest time and build a dataset from
this that I order from a dataview ? Was hoping this could be done in SQL
rather than my VB .net code.
Any help appreciated.
Niclas
*** Sent via Developersdex http://www.examnotes.net ***It is hard to suggest something without seeing the code
SELECT * FROM Users WHERE datetime_column=
(SELECT MAX(datetime_column) FROM Users U WHERE U.userid=Users.userid)
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If I understand your requirements correctly,
you can do this. Note that this will give you
all UserIDs that share the same fastest time
for an event.
SELECT s.UserID,
s.EventID,
s.RecordedTime
FROM SportsResults s
WHERE s.RecordedTime IN (SELECT MIN(s2.RecordedTime)
FROM SportsResults s2
WHERE s.EventID=s2.EventID)|||On Wed, 10 May 2006 01:23:02 -0700, Niclas wrote:
>Hi,
>I have a table with (sports) results, containing an userID, EventId and
>a Time for each result recorded.
>How do I select a list ordered from fastest to slowest containing the
>fastest time for each userID recorded ? The challenge here, that I don't
>understand how to do is to not get a list of all results for an event,
>but only a single entry for each UserID with this userIDs fastest time.
Hi Niclas,
SELECT userID, MIN([Time]) AS FastestTime
FROM YourTable
GROUP BY userID
ORDER BY FastestTime ASC
(Based on lots of assumptions - see www.aspfaq.com/5006 if I answered
the wrong question).
Hugo Kornelis, SQL Server MVP|||This is a fairly straight forward group by. See if this approach works for
you (If I understand your requirements correctly). Regardless of whether or
not this works, check out these links for a quick SQL overview. I think you
will find them helpful.
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
Select userID
, EventId
min(Time) as BestTime
from MyTable
group by userID
, EventId
Order by BestTime
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Just what I needed, works OK.
Thanks
Niclas
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uJZvYhDdGHA.4312@.TK2MSFTNGP05.phx.gbl...
> This is a fairly straight forward group by. See if this approach works
> for
> you (If I understand your requirements correctly). Regardless of whether
> or
> not this works, check out these links for a quick SQL overview. I think
> you
> will find them helpful.
> http://www.w3schools.com/sql/sql_intro.asp
> http://sqlzoo.net/
> Select userID
> , EventId
> min(Time) as BestTime
> from MyTable
> group by userID
> , EventId
> Order by BestTime
>
> "Niclas" <NOSpam@.Notmail.com> wrote in message
> news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
>
I have a table with (sports) results, containing an userID, EventId and
a Time for each result recorded.
How do I select a list ordered from fastest to slowest containing the
fastest time for each userID recorded ? The challenge here, that I don't
understand how to do is to not get a list of all results for an event,
but only a single entry for each UserID with this userIDs fastest time.
Is there any way to do this except looping through each user ID from my
front end code and selecting the fastest time and build a dataset from
this that I order from a dataview ? Was hoping this could be done in SQL
rather than my VB .net code.
Any help appreciated.
Niclas
*** Sent via Developersdex http://www.examnotes.net ***It is hard to suggest something without seeing the code
SELECT * FROM Users WHERE datetime_column=
(SELECT MAX(datetime_column) FROM Users U WHERE U.userid=Users.userid)
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If I understand your requirements correctly,
you can do this. Note that this will give you
all UserIDs that share the same fastest time
for an event.
SELECT s.UserID,
s.EventID,
s.RecordedTime
FROM SportsResults s
WHERE s.RecordedTime IN (SELECT MIN(s2.RecordedTime)
FROM SportsResults s2
WHERE s.EventID=s2.EventID)|||On Wed, 10 May 2006 01:23:02 -0700, Niclas wrote:
>Hi,
>I have a table with (sports) results, containing an userID, EventId and
>a Time for each result recorded.
>How do I select a list ordered from fastest to slowest containing the
>fastest time for each userID recorded ? The challenge here, that I don't
>understand how to do is to not get a list of all results for an event,
>but only a single entry for each UserID with this userIDs fastest time.
Hi Niclas,
SELECT userID, MIN([Time]) AS FastestTime
FROM YourTable
GROUP BY userID
ORDER BY FastestTime ASC
(Based on lots of assumptions - see www.aspfaq.com/5006 if I answered
the wrong question).
Hugo Kornelis, SQL Server MVP|||This is a fairly straight forward group by. See if this approach works for
you (If I understand your requirements correctly). Regardless of whether or
not this works, check out these links for a quick SQL overview. I think you
will find them helpful.
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
Select userID
, EventId
min(Time) as BestTime
from MyTable
group by userID
, EventId
Order by BestTime
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Just what I needed, works OK.
Thanks
Niclas
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uJZvYhDdGHA.4312@.TK2MSFTNGP05.phx.gbl...
> This is a fairly straight forward group by. See if this approach works
> for
> you (If I understand your requirements correctly). Regardless of whether
> or
> not this works, check out these links for a quick SQL overview. I think
> you
> will find them helpful.
> http://www.w3schools.com/sql/sql_intro.asp
> http://sqlzoo.net/
> Select userID
> , EventId
> min(Time) as BestTime
> from MyTable
> group by userID
> , EventId
> Order by BestTime
>
> "Niclas" <NOSpam@.Notmail.com> wrote in message
> news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
>
Help with query NOT IN
I have a view containing column X and column Y and a foreign key F. I
want to filter the view so that it does not contain any rows which are
in the foreign table, which also contain columns X and Y.
I want to do something like this:
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t ON t.f = v.f
WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
ThanksTry,
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t
ON v.x = t.x and v.y = t.y
WHERE t.X is null and t.Y is null
AMB
"larzeb" wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
>|||larzeb wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
Not sure I understand youtr specs. Are you saying you want to see all
rows from the view that do not have a match of all columns (key, x, and
y) in the foregn key table? I don't understand what you mean by "which
also contain columns X and Y" - I assume you mean the same values in x
and y?
Select col1, col2, col3
From vView v
Where Not Exists (
Select *
From Table1 t
On v.f = t.f
and v.x = t.x
and v.y = t.y)
David Gugick
Imceda Software
www.imceda.com
want to filter the view so that it does not contain any rows which are
in the foreign table, which also contain columns X and Y.
I want to do something like this:
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t ON t.f = v.f
WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
ThanksTry,
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t
ON v.x = t.x and v.y = t.y
WHERE t.X is null and t.Y is null
AMB
"larzeb" wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
>|||larzeb wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
Not sure I understand youtr specs. Are you saying you want to see all
rows from the view that do not have a match of all columns (key, x, and
y) in the foregn key table? I don't understand what you mean by "which
also contain columns X and Y" - I assume you mean the same values in x
and y?
Select col1, col2, col3
From vView v
Where Not Exists (
Select *
From Table1 t
On v.f = t.f
and v.x = t.x
and v.y = t.y)
David Gugick
Imceda Software
www.imceda.com
Monday, March 12, 2012
Help with query
I have 3 tables
A table of users containing the fields always required for a user.
users:
userid
email
name
password
projectid
A table of extra properties for a user that can be different from each
project.
These properties can be "location", "department", and so on.
userproperties
userpropertyid
propertyname
projectid
A table of the value of each of the extra properties for each user
userpropertyvalues
userpropertyid
userid
userpropertyvalue
Now I want to do a select statement that returns
userid, email, name, password, propertyname[1], propertyname[2],
propertyname[3]
[userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propertyvalue[3]
(when I pass a projectid)
I just don't seem to be able to do that. Any ideas?
Thank you very much in advance.
FlemmingHi
You design does not look to be correct. If a user can only be in one project
then I would not expect projectid to be in userproperties but I would expect
userid to appear in it. If a user can have multiple projects then I would
expect both to be in userproperties and userpropertyvalues. It is also not
clear how you would rank these assuming propertyid is a numeric then the
first property has the the minimum propertyid, the second property is the
minimum propertyid greater then the first propertyid, the third property is
the minimum propertyid greater then the second propertyid. You can then join
to the users to userproperties/userpropertyvalues three times to get the
values you want.
Also check out how to post DDL and example data at
http://www.aspfaq.com/etiquett**e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.**htm#inserts
It is also useful to post your current attempts at solving the problem.
John
<flemming.madsen@.gmail.com> wrote in message
news:1111790204.659952.289470@.z14g2000cwz.googlegr oups.com...
>I have 3 tables
> A table of users containing the fields always required for a user.
> users:
> userid
> email
> name
> password
> projectid
> A table of extra properties for a user that can be different from each
> project.
> These properties can be "location", "department", and so on.
> userproperties
> userpropertyid
> propertyname
> projectid
> A table of the value of each of the extra properties for each user
> userpropertyvalues
> userpropertyid
> userid
> userpropertyvalue
>
> Now I want to do a select statement that returns
> userid, email, name, password, propertyname[1], propertyname[2],
> propertyname[3]
> [userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propertyvalue[3]
> (when I pass a projectid)
> I just don't seem to be able to do that. Any ideas?
> Thank you very much in advance.
> Flemming|||(flemming.madsen@.gmail.com) writes:
> A table of users containing the fields always required for a user.
> users:
> userid
> email
> name
> password
> projectid
> A table of extra properties for a user that can be different from each
> project.
> These properties can be "location", "department", and so on.
> userproperties
> userpropertyid
> propertyname
> projectid
> A table of the value of each of the extra properties for each user
> userpropertyvalues
> userpropertyid
> userid
> userpropertyvalue
>
> Now I want to do a select statement that returns
> userid, email, name, password, propertyname[1], propertyname[2],
> propertyname[3]
>
[userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propert
yvalue[3]
> (when I pass a projectid)
> I just don't seem to be able to do that. Any ideas?
For this kind of questions, it always a good idea to include:
o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.
This permits anyone who answer to post a tested query.
In you case, there are several loose ends. For instance, in the
userpropertyvaules, I would expect a projectid, since I would
expect (projectid, userpropertyid) to be the primary key of
userproperties. It seems now that userpropertyid alone is the
key. Another loose end is how you now which propertyvalue is #1
and so on. Furthermore, do we know if all users have all properties
for a project?
So this query is very much just a sketch, but hopefully you can work
from it.
SELECT u.userid, u.email, u.name, u.password, upv1.userpropertyvalue,
upv2.userpropertyvalue, upv3.userpropertyvalue
FROM users u
JOIN userproperties up1 ON u.projectid = up1.projectid
JOIN userproperties up2 ON u.projectid = up2.projectid
JOIN userproperties up2 ON u.projectid = up2.projectid
LEFT JOIN userpropertyvalues upv1
ON up1.userpropertyid = upv1.userpropertyd
AND upv1.userid = u.userid
LEFT JOIN userpropertyvalues upv2
ON up2.userpropertyid = upv2.userpropertyd
AND upv2.userid = u.userid
LEFT JOIN userpropertyvalues upv3
ON up3.userpropertyid = upv3.userpropertyd
AND upv3.userid = u.userid
WHERE u.projectid = @.projectid
AND up1.projectid = @.projectid
AND up2.projectid = @.projectid
AND up3.projectid = @.projectid
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland,
Your query works. Thank you very much.
One issue though: I might not in advance be aware of the number of
properties for each user.
Any ideas on alterting the query (or splitting it up) so I can deal
with that?
Thank you very much,
Flemming|||Update:
Erland,
I modified your proposal slightly and I have now solved my problem.
Once again, thank you very much for your kind help.
Sincerely,
Flemming|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> SELECT u.userid, u.email, u.name, u.password, upv1.userpropertyvalue,
> upv2.userpropertyvalue, upv3.userpropertyvalue
> FROM users u
> JOIN userproperties up1 ON u.projectid = up1.projectid
> JOIN userproperties up2 ON u.projectid = up2.projectid
> JOIN userproperties up2 ON u.projectid = up2.projectid
> LEFT JOIN userpropertyvalues upv1
> ON up1.userpropertyid = upv1.userpropertyd
> AND upv1.userid = u.userid
> LEFT JOIN userpropertyvalues upv2
> ON up2.userpropertyid = upv2.userpropertyd
> AND upv2.userid = u.userid
> LEFT JOIN userpropertyvalues upv3
> ON up3.userpropertyid = upv3.userpropertyd
> AND upv3.userid = u.userid
> WHERE u.projectid = @.projectid
> AND up1.projectid = @.projectid
> AND up2.projectid = @.projectid
> AND up3.projectid = @.projectid
Flemming said that my query worked, which is sort of funny, because I
forgot there conditions in the WHERE clause, which I had intended to
read:
WHERE u.projectid = @.projectid
AND up1.projectid = @.projectid
AND up2.projectid = @.projectid
AND up3.projectid = @.projectid
AND up1.propertyname = 'propertyname1'
AND up2.propertyname = 'propertyname2'
AND up3.propertyname = 'propertyname3'
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)