Getting 2 SUMs from the same table

Hi All

I'm really stuck on this one so would appreciate any help you can give.

In essence, I have 1 SQL 2000 table with rows of data logging stock
movement. To differenciate between a stock sale and a stock receipt the
table has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equals a
receipt.

I've been asked to report on this data by suming the total qty used on
invoices and the total qty recvd for each stock item, but I can't figure out
how I sum the same rows twice in the one query.

For example, my query is as follows:

select st.stockid as 'STYLE',
s.picture as 'COLOUR',
'' as 'IN FIRST IN LAST WEEK',
'' as 'THIS WEEK IN',
'' as 'TOTAL IN',
'' as 'OUT FIRST OUT LAST WEEK',
SUM(st.quantity) as 'THIS WEEK OUT',
'' as 'TOTAL OUT',
'' as 'REMAINING',
'' as 'TOTAL DIGESTION %'
from stocktransactions st, stock s
where st.stockid = s.stockid and
st.transactiontype in (8,7) and
st.transactiondate >= '2005-07-12 00:00:00' and
st.transactiondate <= '2005-07-12 23:59:59'
group by st.stockid,s.picture
order by st.stockid

Apart from the 'THIS WEEK OUT' column SUMing all of the stock sales by
transactiontype 7,8, I also want the 'THIS WEEK IN' column to SUM all of the
transactions by transactiontype 3, so that I get the following results:

STYLE COLOUR ... THIS WEEK IN ... THIS WEEK OUT ......
IVP Red 12 23
STP Blue 4 15
etc etc

My problem is that I don't want to exclude a stock item if it hasn't got a
row/value for the THIS WEEK IN and/or the THIS WEEK OUT. Am I asking too
much of SQL?

My table schemas are as follows:

create table STOCKTRANSACTIONS
(
STOCKTRANSACTIONID T_STOCKTRANSACTIONSDOMAIN not null
identity(1,1),
TRANSACTIONTYPE smallint not null,
TRANSACTIONDATE datetime null ,
REFERENCE varchar(40) null ,
Comment varchar(255) null ,
STOCKID T_STOCKDOMAIN null ,
DESCRIPTION varchar(255) null ,
UNITOFSALE varchar(20) null ,
WAREHOUSEID T_WAREHOUSESDOMAIN null ,
PEOPLEID T_PEOPLEDOMAIN null ,
AccountID T_AccountsDomain null ,
AgentID T_AgentsDomain null ,
PLRate float null ,
CONTACTID T_CONTACTDETAILSDOMAIN null ,
JOBID T_JOBSDOMAIN null ,
QUANTITY float null ,
CURRENCYID T_CURRENCIESDOMAIN null ,
SELLINGPRICE float null ,
DISCOUNTPERCENT float null ,
COSTPRICE float null ,
MINIMUMPRICE float null ,
TILLID T_TILLSDOMAIN null ,
UserID T_UsersDomain null ,
ClockDate DateTime null ,
TimeStamp TimeStamp ,
constraint pk_stocktransactions primary key (STOCKTRANSACTIONID)
)
go

create table STOCK
(
STOCKID T_STOCKDOMAIN not null,
NAME varchar(40) not null,
PICTURE varchar(40) null ,
WEIGHT float null ,
VOLUME float null ,
BARCODE smallint null ,
NumberOfPriceBreaks SmallInt not null default 1,
STOCKCATEGORYID T_STOCKCATEGORIESDOMAIN null ,
SALESNOMINALID T_NOMINALACCOUNTSDOMAIN null ,
PURCHASENOMINALID T_NOMINALACCOUNTSDOMAIN null ,
SELLINGCOMMENT varchar(255) null ,
INCLUDESELLINGCOMMENT TinyInt null ,
DISPLAYSELLINGCOMMENT TinyInt null ,
COSTCOMMENT varchar(255) null ,
DISPLAYCOSTCOMMENT TinyInt null ,
PRODUCTTRACKING smallint null ,
ITEMTYPE smallint null ,
VALUATIONPRICE float not null default
0.00 ,
INCLUDEINCUSTOMERSTURNOVER TinyInt null ,
INCLUDEINAGENTSTURNOVER TinyInt null ,
SUPERCEDED TinyInt null ,
SUPERCEDEDBY T_STOCKDOMAIN null ,
SUPPLIERID T_PEOPLEDOMAIN null ,
SUPPLIERSTOCKID varchar(40) null ,
SUPPLIERCOMMENT varchar(255) null ,
NEXTSERIALNUMBER int null ,
SERIALNUMBERLENGTH smallint null ,
SERIALNUMBERPREFIX varchar(10) null ,
SERIALNUMBERSUFFIX varchar(10) null ,
SERIALNUMBERPREFIXLENGTH smallint null ,
SERIALNUMBERSUFFIXLENGTH smallint null ,
TIMESTAMP timestamp not null,
constraint pk_stock primary key (STOCKID)
)
go

Thanks

RobbieDont repeat the question
http://groups-beta.google.com/group...e663f7fc429d1a3

Madhivanan|||Robbie,

You have some data types in your schema that aren't really data types.
You have STOCKTRANSACTIONID as a data type of
T_STOCKTRANSACTIONSDOMAIN. Are you using SQL Server? How about
posting with good data types and some inserts so people can help you
better.

Thanks,
Jennifer|||(jennifer1970@.hotmail.com) writes:
> You have some data types in your schema that aren't really data types.
> You have STOCKTRANSACTIONID as a data type of
> T_STOCKTRANSACTIONSDOMAIN. Are you using SQL Server? How about
> posting with good data types and some inserts so people can help you
> better.

I assume that these are so-called user-defined data types created with
sp_addtype.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

getting 100 rows with values from 1 - 100

I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
KarlCheck out:
http://msdn.microsoft.com/library/d...r />
p03k1.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<klumsy@.xtra.co.nz> wrote in message
news:1115860445.064283.61420@.f14g2000cwb.googlegroups.com...
I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
Karl

get the rows where the info from one table is not contained in the

hello,
i have 2 tables which have 2 fields.
Common in the 2 tables is the id, the other field is a varchar(256)
example
Table1
Id UserInfo
1 Pc A.Julien-3400
2 Soft V.Noris-2800
3 Liz Barbara -2345
Table2
Id Username
1 Julien
2 Jack
3 Barbara
I want to get the id value where the username is not contained in the UserIn
fo
In the example
for id=1 Julien is contained in Pc A.Julien-3400
for id=2 Jack !!! is not contained .....
for id=3 Barbara is contained in Liz Barbara -2345
For this case i want to get only id=2
thanks
best regardsYou can do something like:
SELECT
<your column list>
FROM
table1
JOIN table2 ON table1.id = table2.id AND CHARINDEX(table2.col,
table1.col) > 0
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:1DB6FF37-9D4A-4A9A-A486-6E0C382F0070@.microsoft.com...
> hello,
> i have 2 tables which have 2 fields.
> Common in the 2 tables is the id, the other field is a varchar(256)
> example
> Table1
> Id UserInfo
> 1 Pc A.Julien-3400
> 2 Soft V.Noris-2800
> 3 Liz Barbara -2345
> Table2
> Id Username
> 1 Julien
> 2 Jack
> 3 Barbara
> I want to get the id value where the username is not contained in the
> UserInfo
> In the example
> for id=1 Julien is contained in Pc A.Julien-3400
> for id=2 Jack !!! is not contained .....
> for id=3 Barbara is contained in Liz Barbara -2345
>
> For this case i want to get only id=2
> thanks
> best regards|||On Wed, 1 Feb 2006 06:52:27 -0800, Xavier wrote:

>hello,
>i have 2 tables which have 2 fields.
>Common in the 2 tables is the id, the other field is a varchar(256)
>example
>Table1
>Id UserInfo
>1 Pc A.Julien-3400
>2 Soft V.Noris-2800
>3 Liz Barbara -2345
>Table2
>Id Username
>1 Julien
>2 Jack
>3 Barbara
>I want to get the id value where the username is not contained in the UserI
nfo
>In the example
>for id=1 Julien is contained in Pc A.Julien-3400
>for id=2 Jack !!! is not contained .....
>for id=3 Barbara is contained in Liz Barbara -2345
>
>For this case i want to get only id=2
Hi Xavier,
SELECT Table1.Id, Table1.UserInfo, Table2.UserName
FROM Table1
INNER JOIN Table2
ON Table2.Id = Table1.Id
WHERE Table1.UserInfo NOT LIKE '%' + Table2.Username + '%'
Hugo Kornelis, SQL Server MVP

get the rank of returned rows (was "SQL Question")

Given the following results:
col0 col1 col2
THY 2,265,850 31
VIE 1,474,994 20
RID 1,221,800 17
ACC 1,124,335 15
FEI 445,184 6
DIR 433,783 6
ROM 324,365 4

What is the best way in a query to get the rank of the returned rows by either col1 or col2. In other words who's the number 1,2,3 etc...

total count col0 = 7
total col1 = 7,290,310
total col2 (would eqaul 100%)= 99%

Looking for a mathmatical solution to this any help would be appreciated.Are you looking for row numbers or running totals? Either (or both) can be done. I assume you are order by COL1 Descending?|||Actaully either but it must be based on the totals. I'm playing with a sub-query at the moment trying to use INTENDTITY(INT,1,1) as myRanK field. Only problem is I don't have control of the resulting inner query. I guess I could use a temp table to query against but I was trying to do this in as few trips as possible.|||What does the desired outpout look like? It looks like you are after

select count(*), sum(col2), sum(col3)
from yourtable|||the desired output would be something like the following:

col0 col1 col2 col3(aka Rank)
THY 2,265,850 31 1
VIE 1,474,994 20 2
RID 1,221,800 17 3
ACC 1,124,335 15 4
FEI 445,184 6 5
DIR 433,783 6 6
ROM 324,365 4 7

So I'd have a rank based on the sum of either col1 or col2 against the totals for the group.

Right now I'm trying something like the following but having trouble controlling my returned records from the inner query:

SELECT IDENTITY (INT, 1, 1) AS rank,q.*
FROM (SELECT col0,col1,col2 FROM mytable) q
ORDER BY q.col1

not working as I'd expect. :confused:|||This is a general solution that numbers the rows of your dataset in descending order of Col0:

Select YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2,
count(SecondInstance.col0) as Rank
from YourDataSet
inner join YourDataSet SecondInstance on YourDataSet.col1 <= SecondInstance.col1
group by YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2|||SELECT q.*,IDENTITY (INT, 1, 1) AS rank
INTO db.dbo.TEST
FROM (SELECT col0,col1,SUM(CASE WHEN Date >= '01/01/2004' AND Date <= '12/31/2004' THEN someValue ELSE 0 END)
AS col2
FROM tab1 INNER JOIN
tab2 ON tab1.ID = tab2.ID
WHERE (Date >= '01/01/2004') AND (Date <= '12/31/2004')
GROUP BY col0,col1) q
ORDER BY q.col1 DESC

Better example of what I'm working with...|||hey toejam, did you try blindman's suggestion with the theta join?

FYI you guys should read IDENTITY() Function Isn't Reliable for Imposing Order on a Result Set (http://www.winnetmag.com/SQLServer/Article/ArticleID/43553/43553.html)|||r937,

I read blindmans post but I wasn't sure if it would get me the results I'm after but I'll try it.

the link you provided is very on point so I'm going to take a moment to read through it.

Thx!

:D|||see http://forums.devshed.com/t218290/s.html|||It'll get you the results you are after. It is a pretty standard solution to your class of problem.|||r937 -

DevShed (http://forums.devshed.com/showthread.php?p=944761#post944761)

Thx very much... I did it the old fashion way. I did a little VB code to get my answer but I'd prefer to do it in a query to let the db do the work. Awesome job man!

Thx a bunch

get the number of rows exported using bcp

Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KR
Why not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegro ups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>
|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf .googlegroups.com...
>
>
>
>
>
> - Show quoted text -

get the number of rows exported using bcp

Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -

get the number of rows exported using bcp

Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.
123200@.e9g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -

Get the ID to start from 1

I have a sql-database with one table and in the table there are now around 70 rows...

I wonder if its possible to "delete" or "remove" these rows so that the ID (the primary key) start to increase from 1 again....or am I forced to create a new table (and delete the one who exists now) to get the ID to increase from 1 again?....

hi,

try

truncate table <tablename>

regards,

joey

|||ok..but I just ment that I should do it in the visual web developer (which is the program I had) and just do it directly in the program (not with code)...I just wonder if its possible to do it...and how I do it if its possible....|||

There is no other way to accomplish this task without running the t-sql on the table either in the vb.net/c# or through an analyzer.

Adamus

|||If you mean use the IDE, you can manually delete all records from the table. From the table definition, you delecte this id column first and recreate it and set it to identity column. It should get the seed to 1 again.|||

Tigers21 wrote:

I have a sql-database with one table and in the table there are now around 70 rows...

I wonder if its possible to "delete" or "remove" these rows so that the ID (the primary key) start to increase from 1 again....or am I forced to create a new table (and delete the one who exists now) to get the ID to increase from 1 again?....

Well you'll have to delete the rows, or you'll get a PK error, this script should do it for you:

truncate table TableName

dbbc checkident('TableName', reseed, 0)

Get Table Rows Priority Wise

I have a table.

Highlight
----

Id

Name

Detail

StartDate

EndDate

Priority

I want to make a query which returns 1 Highlight in the current date.

But remember I have already set the Hightlight Priority 1 to 5. And I want that Hight Priority rows select more times than Low Priority Rows.

Not sure I understand the question, but to return a single row, you can use TOP 1 and for your priority, use an ORDER BY. So for example:

SELECT TOP 1 * FROM [Hightlight] WHERE [StartDate] = GETDATE() ORDER BY [Priority]

-Damien

|||

I need that.

Every time I select top 1 row different Highlight will select.

But the hightlight which has high priority select more times then low priority.

|||

Are you trying to get a random result? Basically the statement I provided will only ever return 1 result with the highest priority.

Not sure I understand what you are trying to do...

-Damien

|||

I think this is waht you need:

1SELECT TOP 1 *2FROM [Hightlight]3WHERE [StartDate] =Convert(varchar(20),GETDATE(),111)4ORDER BY [Priority],[StartDate], [EndDate]

I suggest you to have another column (to act as a flag) for the FinishedTasks (IsFinished or IsDone column).

So, you can list the top 1 high priority that assign to you first and not finished yet and it has the closest deadline (EndDate), by using the following example:

1SELECT TOP 1 *2FROM [Hightlight]3WHERE [StartDate] =Convert(varchar(20),GETDATE(),111)4AND [IsFinished] = 1-- (0:Finished and 1:Not Finished)5ORDER BY [Priority],[EndDate]6

Good luck.|||

But I don't want this.

I want that row will select randomly and the row which has high priority shows many time as compare to low priority hightlight.

But in your prescribed condition High Priority row will select till it expires and other will wait to expire.

|||

Hi,

If you want to show random values, you can try Order By NewID() .

Try the following codes (I suppose Max(Priority) will have the most priority. Or you can use MIN() )

1 SELECT TOP 1 *
2 FROM [Hightlight]
3 WHERE [Priority]=select Max(Priority)
4 ORDER BY NewID()

OR:

select Top1 *

from [hightlight]

order by Priority,NewId()

Hope my suggestion helps

|||

But Sir,

From your prescribed solution.

Random Rows of Same Priority will select till that priority will expires.

But I want random rows from random priorities.

And records of low priority will select lower then high priority

|||

zeeshanuddinkhan@.hotmail.com:

But Sir,

From your prescribed solution.

Random Rows of Same Priority will select till that priority will expires.

But I want random rows from random priorities.

And records of low priority will select lower then high priority

Here it is:

1SELECT TOP 1 * 2FROM Hightlight3WHERE [Priority]in 4 (5select Priority6From Hightlight7Order byNewID()-- for random pripority8 )9Order byNewID()-- for random rows

Good luck

|||

Can you tell me?

How can this query take care that the high priority will select more time as compare to low priority?

Because after trying your query every priority has equal chance to come.

|||

And by the way.

First try the query on your own end.

Because your mentioned query not fits as I ask.

And Second it has following error.

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

|||

zeeshanuddinkhan@.hotmail.com:

Can you tell me?

How can this query take care that the high priority will select more time as compare to low priority?

Because after trying your query every priority has equal chance to come.

Here it is:

1SELECT TOP 1 * 2FROM Hightlight3WHERE [Priority]in 4 (5select Priority6From Hightlight7Order byNewID(), Priority-- for random pripority8 )9Order byNewID()-- for random rows

Now the chances for high priority will be more for the random rows.

Good luck.

|||

Please check it.

This query has an error.

|||

zeeshanuddinkhan@.hotmail.com:

Please check it.

This query has an error.

Sorry for the small mistake, I forgot to use top.. here is the correction and I am sure it will work with you,

11SELECT TOP 1 *22FROM Hightlight33WHERE [Priority]in44 (55select TOP 1 Priority66From Hightlight77Order byNewID(), Priority-- for random pripority88 )99Order byNewID()-- for random rows10

Good luck.

|||

SELECT *

FROM Highlight

WHERE GetDate() BETWEEN StartDate AND EndDate

ORDER BY rand(cast(cast(newid() as binary(4)) as int))*Priority

That will return all the records that have a startdate before now, and a enddate later than now in a random order weighted by priority. (Priority 1 will be more likely to be near the top than a priority 5, etc). Just add TOP 1 if you only want the first record after you are done verifying that the results are what you want. If you wanted priority 5 to be selected more than priority 1, then add " DESC" to the end of the query.

NewId by itself IS NOT random. It is unique on every call, but it's predictable. You can however, use it as a seed to the rand function, which will make it random (or more random).

get some rows into one column

Hello,

can I do this via SQL:

example: tbltest has 5 rows:

col1
===
A
C
F
M
R

What I want is this:

result:
===============
A, C, F, M, R

Do I really have to go through the rows per SP? I could do this:

SELECT UDF(col1)
FROM tbltest

Ain't there a more simple way, maybe theres a T-SQL-command ?

thx
candide_shWell, I knew I had read something about it but where? NOW I found it,
maybe from sqlservercentral.com, don't know the author, but it works
and is a straight solution.

thx for your answers
candide_sh

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

It helps you in situaions whenever you wish to create commm seperated
values actually originating from multiple records. Say, your query
return three records in folloing patter:
Student_Name
=============
Ricky
Adam
Mathew

But, say you wish to have records in following patter:
Student_Name
============
Ricky, Adam, Mathew

That is how it works. Try it...I beleive it will help you a lot

create procedure sp_return_students
as
set nocount off

/* Declare variable which will store all student name */
Declare @.StudentName varchar(8000)

/* Query that will return student names and at the same time
concatenate values. /*
select @.StudentName = coalesce(@.StudentName + ', ', '') + stu_name
from tbl_students

/* At last, you just have to define column name that will store values
*/
Select @.StudentName As Student_Name

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

|||candide_sh,

There is one major problem with this solution: it might not work. It
might not work the next time you run it, after you have run a service
pack, after you have upgraded your server, after you have added another
join to your query, etc. And that is because there is no defined
behavior for this concatenation if multiple rows are involved.

So I would seriously advise you NOT to do it this way.

--
Gert-Jan

candide_sh@.yahoo.de wrote:

Quote:

Originally Posted by

>
Well, I knew I had read something about it but where? NOW I found it,
maybe from sqlservercentral.com, don't know the author, but it works
and is a straight solution.
>
thx for your answers
candide_sh
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

It helps you in situaions whenever you wish to create commm seperated
values actually originating from multiple records. Say, your query
return three records in folloing patter:
Student_Name
=============
Ricky
Adam
Mathew
>
But, say you wish to have records in following patter:
Student_Name
============
Ricky, Adam, Mathew
>
That is how it works. Try it...I beleive it will help you a lot
>
create procedure sp_return_students
as
set nocount off
>
/* Declare variable which will store all student name */
Declare @.StudentName varchar(8000)
>
/* Query that will return student names and at the same time
concatenate values. /*
select @.StudentName = coalesce(@.StudentName + ', ', '') + stu_name
from tbl_students
>
/* At last, you just have to define column name that will store values
*/
Select @.StudentName As Student_Name

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

|||There are a lot of good reasons so many experienced SQL programmers
are warning you about this.

Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? Normal forms are the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
The kludges that have been posted are unpredictable, unsupported and
some are highly proprietary.|||Crawl back under your rock Denial Again.

You've a cheek talking about people not being 'well mannered'.

I'm offensive to one person only - celko (well two if we are counting spats
where you spout dis information and rubbish about SQL Server) because he's
offensive to most people.

You are just offensive 'period' and people don't need to take my word for
it - you have a reputation that preceeds you...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||Nah, I think text() + FOR XML certainly has the flavour of a kludge over

Quote:

Originally Posted by

it. The syntax is anything about intuitive, and if the data include
ampersands or angle brackets, there are a few surprising waiting for us.

I disagree, true - it's unintuitive to relational folk but isn't most XML.

This the XML side of the SQL Server data engine and until you start learning
how it works, syntax etc... then I guess anything to do with XML is a
kludge.

The ampersand and angle brackets are not suprises, books online tells us
what the behaviour is, ampersand and square brackets both have meaning
within the XML which is what text() is giving us.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||On Thu, 1 Nov 2007 05:40:36 -0000, Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Nah, I think text() + FOR XML certainly has the flavour of a kludge over
>it. The syntax is anything about intuitive, and if the data include
>ampersands or angle brackets, there are a few surprising waiting for us.

>
>I disagree, true - it's unintuitive to relational folk but isn't most XML.
>
>This the XML side of the SQL Server data engine and until you start learning
>how it works, syntax etc... then I guess anything to do with XML is a
>kludge.
>
>The ampersand and angle brackets are not suprises, books online tells us
>what the behaviour is, ampersand and square brackets both have meaning
>within the XML which is what text() is giving us.

Hi Tony,

Whether intuitive or not, it still is a kludge in my eyes. The phrase
FOR XML does somewhat imply that it is intended to produce, well, XML,
don't you think? And if you use that to produce straight-text non-XML
output, then I have no other word for it than "kludge".

Though I also agree with Erland that it's better than any of the
alternatives :)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Get rows with duplicate values in certain columns

Hi there,

I would like to know how to get rows with duplicate values in certain
columns. Let's say I have a table called "Songs" with the following
columns:

artist
album
title
genre
track

Now I would like to show the duplicate songs to the user. I consider
songs that have the same artist and the same title to be the same song.
Note: All columns do not have to be the same.

How would I accomplish that with SQL in SQL Server?

Thanks to everyone reading this. I hope somebody has an answer. I've
already searched the whole newsgroups, but couldn't find the solution.<agekay@.gmx.de> wrote in message
news:1120392807.001826.295060@.g49g2000cwa.googlegr oups.com...
> Hi there,
> I would like to know how to get rows with duplicate values in certain
> columns. Let's say I have a table called "Songs" with the following
> columns:
> artist
> album
> title
> genre
> track
> Now I would like to show the duplicate songs to the user. I consider
> songs that have the same artist and the same title to be the same song.
> Note: All columns do not have to be the same.
> How would I accomplish that with SQL in SQL Server?
> Thanks to everyone reading this. I hope somebody has an answer. I've
> already searched the whole newsgroups, but couldn't find the solution.

You probably need something like this:

select
s.artist,
s.album,
s.title,
s.genre,
s.track
from
dbo.Songs s
join
(
select
artist,
title
from
dbo.Songs
group by
artist,
title
having count(*) > 1
) dt
on s.artist = dt.artist and
s.title = dt.title

If this doesn't give the results you expect, then you should post some DDL
and sample data to clarify exactly what you need:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Thank you so much! That's exactly what I was looking for. Works like a
charm!

Get rows for latest date

Hello!

I have a table something like this:

ID INTEGER

Info VARCHAR (actually several columns but that is not important here)

DAT DateTime

For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:

ID - DAT - Info

1 - 2007-02-01 - Info1

1 - 2007-02-01 - Info2

1 - 2006-02-01 - Info3

2 - 2007-05-05 - Info4

2 - 2007-02-01 - Info5

2 - 2006-02-01 - Info6

I would like to get:

Info1

Info2

Info4

This has to be done in one Query. Can anybody help me?

Here it is,

Code Block

Create Table #data (

[ID] int ,

[DAT] datetime ,

[Info] Varchar(100)

);

Insert Into #data Values('1','2007-02-01','Info1');

Insert Into #data Values('1','2007-02-01','Info2');

Insert Into #data Values('1','2006-02-01','Info3');

Insert Into #data Values('2','2007-05-05','Info4');

Insert Into #data Values('2','2007-02-01','Info5');

Insert Into #data Values('2','2006-02-01','Info6');

select main.info from #data main

join (select ID,max(dat) dat from #data group by ID) as latest

on latest.ID=main.ID and latest.dat=main.dat

|||

A couple of options:

Code Block

create table testdata

(ID int, Dat DATETIME, Nm CHAR(5))

INSERT INTO testdata

SELECT 1, '1 feb 2007', 'Info1'

UNION ALL

SELECT 1, '1 feb 2007', 'Info2'

UNION ALL

SELECT 1, '1 feb 2006', 'Info3'

UNION ALL

SELECT 2, '5 may 2007', 'Info4'

UNION ALL

SELECT 2, '1 feb 2007', 'Info5'

UNION ALL

SELECT 2, '1 feb 2006', 'Info6'

--SQL2005

WITH cte

AS

(SELECT ID, Nm, RANK() OVER (PARTITION BY ID ORDER BY Dat DESC) AS D

FROM testData)

SELECT Nm

FROM cte

WHERE D = 1

--SQL2000

SELECT Nm

FROM

(SELECT ID, MAX(Dat) AS Dt

FROM testData

GROUP BY ID) AS Bob

INNER JOIN testData t

ON Bob.Dt = t.Dat AND Bob.ID = t.ID

HTH!|||

One more trick..

Code Block

--SQL2005

;with cte

as

(select id, nm,dat,max(dat) over (partition by id) as latestdat from testdata)

select nm

from cte

where dat = latestdat

Code Block

--SQL Server 2000

select

main.info

from

#data main

where

exists

(

select * from

(

select

d

,max(dat) dat

from

#data

group by D

) data

where

data.d = main.d

and data.dat=main.dat

)

|||

Thank You guys!!

...for the fast and helpful response. I would never figure that out.

Get rows fields as xml name value pairs

Hi,
I'm trying to write a procedure to return the data from a query as xml
in the following format:
<root>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
</root>
so basically i need to turn the fields of a row into new rows?
I want to send them to a waiting app for deserialization into an
object but want the object to able to deserialize the data whatever it
is.
Any ideas?
Thanks,
George
Your best bet is probably to use an AUTO mode query and then apply an XSLT
transform.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"george" <8eu1ukg02@.sneakemail.com> wrote in message
news:d90f7cf1.0405270433.27d2fdbd@.posting.google.c om...
> Hi,
> I'm trying to write a procedure to return the data from a query as xml
> in the following format:
> <root>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> </root>
> so basically i need to turn the fields of a row into new rows?
> I want to send them to a waiting app for deserialization into an
> object but want the object to able to deserialize the data whatever it
> is.
> Any ideas?
> Thanks,
> George

Get Results into one table

What is the easiest way to do the follwoing.
I have 2 tables. I want all the rows from one table and what is left from
the second table.
I.e. table 1.
ID value
1 10
2 20
3 30
i.e. table 2
ID value
1 15
2 25
3 35
4 444
i want my final table to have the following (everything from table 1 and
left over from table 2)
ID value
1 10
2 20
3 30
4 4444
ThanksTry:
select
*
from
Table1
union all
select
*
from
Table2 t2
where not exists
(
select
*
from
Table2 t2
where
t2.[ID] = t1.[ID]
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Fab" <lazzaro@.rogers.com> wrote in message
news:ew4q9HKPGHA.3936@.TK2MSFTNGP10.phx.gbl...
What is the easiest way to do the follwoing.
I have 2 tables. I want all the rows from one table and what is left from
the second table.
I.e. table 1.
ID value
1 10
2 20
3 30
i.e. table 2
ID value
1 15
2 25
3 35
4 444
i want my final table to have the following (everything from table 1 and
left over from table 2)
ID value
1 10
2 20
3 30
4 4444
Thanks|||Here's another solution:
select table2.ID,
ISNULL(table1.value, table2.value) AS value
from table2
left outer join table1 on table2.ID = table1.ID
"Fab" <lazzaro@.rogers.com> wrote in message
news:ew4q9HKPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> What is the easiest way to do the follwoing.
> I have 2 tables. I want all the rows from one table and what is left from
> the second table.
> I.e. table 1.
> ID value
> 1 10
> 2 20
> 3 30
> i.e. table 2
> ID value
> 1 15
> 2 25
> 3 35
> 4 444
> i want my final table to have the following (everything from table 1 and
> left over from table 2)
> ID value
> 1 10
> 2 20
> 3 30
> 4 4444
> Thanks|||Hi Tom.
Me thinks there's a problem with your query.
This should work.
select
*
from
Table1
union all
select
*
from
Table2 t1
where not exists
(
select
*
from
Table1 t2
where
t2.[ID] = t1.[ID]
)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t2
> where not exists
> (
> select
> *
> from
> Table2 t2
> where
> t2.[ID] = t1.[ID]
> )
> --
> Tom|||On Tue, 28 Feb 2006 15:02:56 -0500, Fab wrote:

>What is the easiest way to do the follwoing.
>I have 2 tables. I want all the rows from one table and what is left from
>the second table.
>I.e. table 1.
>ID value
>1 10
>2 20
>3 30
>i.e. table 2
>ID value
>1 15
>2 25
>3 35
>4 444
>i want my final table to have the following (everything from table 1 and
>left over from table 2)
>ID value
>1 10
>2 20
>3 30
>4 4444
>Thanks
>
Hi Fab,
SELECT t2.ID, COALESCE(t1.value, t2.value)
FROM Table2 AS t2
LEFT OUTER JOIN Table1 AS t1
ON t1.ID = t2.ID
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Hugo Kornelis, SQL Server MVP|||Another way is to use "full join".
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJPO4mKPGHA.420@.tk2msftngp13.phx.gbl...
> Hi Tom.
> Me thinks there's a problem with your query.
> This should work.
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t1
> where not exists
> (
> select
> *
> from
> Table1 t2
> where
> t2.[ID] = t1.[ID]
> )
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||Ah, yes. Good catch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJPO4mKPGHA.420@.tk2msftngp13.phx.gbl...
Hi Tom.
Me thinks there's a problem with your query.
This should work.
select
*
from
Table1
union all
select
*
from
Table2 t1
where not exists
(
select
*
from
Table1 t2
where
t2.[ID] = t1.[ID]
)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t2
> where not exists
> (
> select
> *
> from
> Table2 t2
> where
> t2.[ID] = t1.[ID]
> )
> --
> Tom|||--@.@.@. TESTED and works
Create Table #tbl1
([ID] int,
[Value]varchar(10))
INSERT #tbl1 ([ID],[VALUE])
VALUES (1,'10')
INSERT #tbl1 ([ID],[VALUE])
VALUES (2,'20')
INSERT #tbl1 ([ID],[VALUE])
VALUES (3,'30')
Create Table #tbl2
([ID] int,
[Value]varchar(10))
INSERT #tbl2 ([ID],[VALUE])
VALUES (1,'15')
INSERT #tbl2 ([ID],[VALUE])
VALUES (2,'25')
INSERT #tbl2 ([ID],[VALUE])
VALUES (3,'35')
INSERT #tbl2 ([ID],[VALUE])
VALUES (4,'444')
-- View both
SELECT *
FROM #tbl1
SELECT *
FROM #tbl2
-- Combine to make all display
SELECT *
FROM #tbl1
UNION ALL
SELECT *
from #tbl2
where not exists(select *
from #tbl1
where [ID] = #tbl2.[ID])
--@.@.@. TESTED and works
"Fab" wrote:

> What is the easiest way to do the follwoing.
> I have 2 tables. I want all the rows from one table and what is left from
> the second table.
> I.e. table 1.
> ID value
> 1 10
> 2 20
> 3 30
> i.e. table 2
> ID value
> 1 15
> 2 25
> 3 35
> 4 444
> i want my final table to have the following (everything from table 1 and
> left over from table 2)
> ID value
> 1 10
> 2 20
> 3 30
> 4 4444
> Thanks
>
>|||Thanks to everyone for your help...
The solution Joseph provided worked.
:-)
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:E841A384-DEE2-4CC7-A939-A5F6C13DD975@.microsoft.com...
> --@.@.@. TESTED and works
> Create Table #tbl1
> ([ID] int,
> [Value]varchar(10))
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (1,'10')
>
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (2,'20')
>
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (3,'30')
>
>
> Create Table #tbl2
> ([ID] int,
> [Value]varchar(10))
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (1,'15')
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (2,'25')
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (3,'35')
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (4,'444')
>
> -- View both
> SELECT *
> FROM #tbl1
> SELECT *
> FROM #tbl2
>
> -- Combine to make all display
> SELECT *
> FROM #tbl1
> UNION ALL
> SELECT *
> from #tbl2
> where not exists(select *
> from #tbl1
> where [ID] = #tbl2.[ID])
>
>
> --@.@.@. TESTED and works
> "Fab" wrote:
>

Get one row from each group of rows

Hi,

I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1

id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2

For these, I will take the rows with id=2 and id=7.

Thank you, and sory for my english.On 22 Jul, 05:19, deluca.vice...@.gmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1
>
id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2
>
For these, I will take the rows with id=2 and id=7.
>
Thank you, and sory for my english.

The following assumes that there is only one row where column2 = 2 and
column1 is the largest value - as would be the case if (idRef,
column1, column2) was a key for example. If you include DDL with keys
in future posts then people who respond won't have to guess which
columns are unique.

SELECT id, idRef, column1, column2
FROM tbl AS t1
WHERE column2 = 2
AND column1 =
(SELECT MAX(column1)
FROM tbl
WHERE idRef = t1.idRef
AND column2 = 2);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On Jul 22, 9:19 am, deluca.vice...@.gmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1
>
id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2
>
For these, I will take the rows with id=2 and id=7.
>
Thank you, and sory for my english.

select a.* from tbl a
join
(select idref,max(column1) as column1
from tbl
where column2 = 2
group by idref) as b
on a.idref = b.idref
and a.column1 = b.column1

Get Number of rows Deleted

How do we get the number of rows deleted from a table?
@.@.rowcount is returning 0@.@.rowcount (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_20vo.asp) returns the number of rows affected by the last successful statement executed by your spid. It only has meaning for the duration of one statment, then it is reset.

See the code for the gory details of how you can go worng ;)DECLARE @.i1 INT
, @.i2 INT

CREATE TABLE #foo (
fooID INT NOT NULL
)

INSERT INTO #foo (fooID)
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 UNION SELECT 5

SELECT * FROM #foo

DELETE FROM #foo WHERE 1 = fooID % 2

SELECT @.i1 = @.@.rowcount

DELETE FROM #foo

PRINT 'We''re working on it!'

SELECT @.i2 = @.@.rowcount

SELECT @.i1, @.i2

SELECT * FROM #foo

DROP TABLE #foo-PatP|||A true gentleman

Isn't that a fubar table?

Get number of results from SqlDataSource

Hi all,

Is there a way of counting the number of results/rows from an SqlDataSource which uses a select statement like: "SELECT * FROM TABLE1".

I need to get this value from the SqlDataSource into theSub Page_Load.

I don't want to bind to any gridviews, repeaters etc. I just want to get the number of rows/results from the SqlDataSource.

Is there a way of doing this?

Thanks

Public cnt as integerProtected Sub SqlDataSource1_Selected(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Selected cnt = e.AffectedRowsEnd Sub

|||

Thanks, that helped. However I need to get this value many times throughout my sub page_load.

I see this value is calculated when SqlDataSource1 is selected. Is there a way to get this value each time I change the default value of a select parameter, or simply call this sub SqlDataSource1_Selected from the Page_load. Since when I change a select parameter, this then affects the select command statement which is when I need to re-calculate the number of rows (get value of integer cnt).

Im guessing I need to change theHandles SqlDataSource1.Selected to something else?

How could I get this working?

Thanks.

|||

using jMacs code you can then access the Variable cnt in your pageload.

The scope of the variable cnt allows you to access it and use it through out the life of the page in your Page_Load sub, or any other method for that matter. It will be set anytime SqlDataSource1_Selected event is called.

Public cnt as integerProtected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load'//work with the varialbe cnt here.End SubProtected Sub SqlDataSource1_Selected(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Selected cnt = e.AffectedRowsEnd Sub

|||

Exactly. Here's some more info on the SqlDataSource.Selected Event -http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selected(vs.80).aspx

Get Max Date Query

I have the following 4 rows in a table

Company JobNumber BeginDate ModifyDate

1 2 12/12/2005 11/12/2006

1 2 12/12/2005 11/15/2006

2 3 11/12/2005 1/12/2006

2 3 11/12/2005 9/15/2006

The company and Job Number make up the key so yes this table has duplicate keys. My question is how would I return the two keys with the max modify date?

So the results would look like this:

1 2 12/12/2005 11/15/2006

2 3 11/12/2005 9/15/2006

Thanks,

SELECT Company, JobNumber, BeginDate, MAX(ModifyDate)

FROM blah

GROUP BY Company, JobNumber, BeginDate

|||

Thanks, works great!