Getting the matching columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...On 1 Mar 2006 03:03:08 -0800, vvyshak@.gmail.com wrote:
>Hi all..
>I have two tables such as cisco and ciscocom. and i wan to compare each
>row of ciscocom with cisco having same column values. i wan to get the
>count of matching columns for each row in cisco...
>eg:
>Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
>columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
>for each row in ciscocom. please provide me with the procedure...
>Waiting for your response...
Hi Kuttan,
I just answered this question in microsoft.public.sqlserver.programming.
Please don't post seperate copies of the same question to several
groups!
--
Hugo Kornelis, SQL Server MVP

Getting the latest row from a batch

Hi All

This is a belter that my little brain can't handle.

Basically I have 1 SQL table that contains the following fields:

Stock Code
Stock Desc
Reference
Transaction Date
Qty
Cost Price

Basically this table stores all the transaction lines of when a user
books stock items into stock so that they can look at a journal of
this goods in as and when they please.

My task is that the user wants a list of all the stock items with the
last cost price that they were booked in at.

So I think I have to find the last transaction date used for each
stock code and then bring this back as 1 row per stock code with the
above fields of data.

How the whats-its can I do this? Is it acutally possible?

Any help you can give is much appreciated.

Rgds

LaphanSELECT S1.stockcode, S1.transdate, S1.costprice
FROM Stock AS S1
JOIN
(SELECT stockcode, MAX(transdate)
FROM Stock
GROUP BY stockcode)
AS S2(stockcode, transdate)
ON S1.stockcode = S2.stockcode
AND S1.transdate = S2.transdate
GROUP BY S1.stockcode, S1.transdate, S1.costprice

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks David

Very much appreciated.

Rgds

Laphan

David Portas <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:nJmdnSNww5bmVkmi4p2dnA@.giganews.com...
SELECT S1.stockcode, S1.transdate, S1.costprice
FROM Stock AS S1
JOIN
(SELECT stockcode, MAX(transdate)
FROM Stock
GROUP BY stockcode)
AS S2(stockcode, transdate)
ON S1.stockcode = S2.stockcode
AND S1.transdate = S2.transdate
GROUP BY S1.stockcode, S1.transdate, S1.costprice

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks for the help David. The below script works perfectly
apart from the fact that I need to add a cost price field from another
table:

SELECT S1.STOCKID AS 'Stock Code', S1.TRANSACTIONDATE AS 'Transaction
Date', S1.QUANTITY AS 'Quantity', S1.COSTPRICE AS 'Cost Price'
FROM STOCKTRANSACTIONS AS S1
JOIN
(SELECT STOCKID, MAX(TRANSACTIONDATE)
FROM STOCKTRANSACTIONS
WHERE TRANSACTIONTYPE = 3
GROUP BY STOCKID)
AS S2(STOCKID, TRANSACTIONDATE)
ON S1.STOCKID = S2.STOCKID
AND S1.TRANSACTIONDATE = S2.TRANSACTIONDATE
GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE

Could you please let me know how I can add an additional COSTPRICE
column from a STOCKPRICES table to this script.

FYR, if I was to perform a straightforward query to join the
STOCKTRANSACTIONS and the STOCKPRICES tables together this is how it
would look to get the required data, but this wouldn't contain the new
fangled 'find last date' thing that you sent me:

SELECT STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.TRANSACTIONDATE,
STOCKTRANSACTIONS.QUANTITY, STOCKTRANSACTIONS.CURRENCYID,
STOCKTRANSACTIONS.COSTPRICE, STOCKPRICES.COSTPRICE
FROM STOCKPRICES, STOCKTRANSACTIONS
WHERE STOCKTRANSACTIONS.CURRENCYID = STOCKPRICES.CURRENCYID AND
STOCKTRANSACTIONS.STOCKID = STOCKPRICES.STOCKID AND
((STOCKTRANSACTIONS.TRANSACTIONTYPE=3) AND
(STOCKPRICES.PRICELEVELID='1'))

Any ideas on how to sync these 2 queries??

Rgds

Laphan

Getting the latest dated Row for each user in a table

Hi all,
I want to have a query that will return me a single row for each userfrom a table where the table has many rows for each user. The singlereturned row for each user must be the most recently dated entry([I7-Change-Date]) for that user.
An example of the code I have so far is as follows, but it obviously doesn;t work.

select DISTINCT([I1-Customer-Ref]) AS Cust,
([i7-w-fixed-amnt]) as WaterFixedAmt,
([i7-w-rv-amnt]) as WaterRVAmt,
([i7-s-fixed-amnt]) as SewerageFixedAmt,
 ([I7-Change-Date]) AS [Date]
from r07UnMeasuredBills
ORDER BY Cust, [I7-Change-Date] DESC

I am using MS SQL Server 7 for this.
Thanks
Tryst
Have you tried aggregate functions?
i.e.
SELECT I1-Customer-Ref,Max(I7-Change-Date) FROM rO7UnMeasuredBills GROUP BY I1-Customer-Ref

|||

SELECT r.*

FROM r07UnMeasuredBills r

WHERE r.I7-Change-Date IN (SELECT I7-Change-DATE FROM r07UnMeasuredBills r2 WHERE r.I1-Customer-Ref=r2.I1-Customer-Ref)

|||No, I haven't tried that.
Will MAX work on dates (DATETIME)?
(I can't check it now as I am not in work)
Tryst
|||

Yes, infact I forgot to actually do that in my previous post, bad me... It should have been

SELECT r.*

FROM r07UnMeasuredBills r

WHERE r.I7-Change-Date IN (SELECT max(I7-Change-DATE) FROM r07UnMeasuredBills r2 WHERE r.I1-Customer-Ref=r2.I1-Customer-Ref)

|||Hi Motely, thanks for the reply.
Regarding the SQL query you provided, although it works (well, I hope, I haven't tried it yet :)), I am trying to get an understanding of how it works, but the sub-SELECT is confusing me a little. If your using the MAX function, won't that just bring back the row with the most recent date from all the rows and not for each user in the table? Would you be able to just break it down for me, so I can get an understanding of it?
Thanks once again.
Tryst
|||

It woul, however the table in the Sub Query is matched to the customer-ref in the where clause:

r.I1-Customer-Ref=r2.I1-Customer-Ref

Getting the identity of some newly created row

If I have an account table where the column "accountno" is an identity
i.e. auto-generated, and I insert a new row like so (sorry, but all-
caps keywords are so 70s):
insert account
values (20000.00, 42)
and then want to do something with the newly created account rows
accountno, like insert it in another table, like:
insert account
values (20000.00, 42)
insert transactions
values (accountno, 20.000)
except this of course does not work. Maybe something like:
insert account
values (20000.00, 42)
declare @.accountno int
select @.accountno = ?
insert transactions
values (accountno, 20.000)
Help!Check out Scope_Identity in BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<pinkfloydhomer@.gmail.com> wrote in message
news:1194105832.364153.128820@.o38g2000hse.googlegroups.com...
> If I have an account table where the column "accountno" is an identity
> i.e. auto-generated, and I insert a new row like so (sorry, but all-
> caps keywords are so 70s):
> insert account
> values (20000.00, 42)
> and then want to do something with the newly created account rows
> accountno, like insert it in another table, like:
> insert account
> values (20000.00, 42)
> insert transactions
> values (accountno, 20.000)
> except this of course does not work. Maybe something like:
> insert account
> values (20000.00, 42)
> declare @.accountno int
> select @.accountno = ?
> insert transactions
> values (accountno, 20.000)
> Help!
>|||SELECT @.@.IDENTITY
Returns the last-inserted identity value
From http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||No it doesn't in some cases. Use @.@.IDENTITY instead. Check out
Scope_Identity in BOL against @.@.IDENTITY and you will see why..
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Use SCOPE_IDENTITY unless you have a compelling reason to use @.@.IDENTITY.
See BOL for the differences.
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Getting the identity of some newly created row

SELECT @.@.IDENTITY
Returns the last-inserted identity value.
From http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
No it doesn't in some cases. Use @.@.IDENTITY instead. Check out
Scope_Identity in BOL against @.@.IDENTITY and you will see why..
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
|||Use SCOPE_IDENTITY unless you have a compelling reason to use @.@.IDENTITY.
See BOL for the differences.
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Getting the Id of the row that i inserted with SQL 2005

Hi,

I use a Stored Procedure who works very well

...
INSERT INTO Computers (CategoryID, SubCategoryID, .....
VALUES (@.CategoryID, @.SubCategoryID, ....
...

But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.

How can i do that? I code in VB.

Thanks

Add "RETURN SCOPE_IDENTITY()" to your stored procedure. Then you should be able to pick up this value as the stored procedure's return value.

|||

Thank you very much it works!

Getting the ID from an insert

Is there a way using t-sql to get the ID of an inserted row following the
insert?
Example:
@.RowID as int
@.RowID = INSERT INTO Contacts (Person,Phone_Number) Values('John
Smith','123-456-7890')
SELECT * FROM Contacts WHERE ID = @.RowID
Thanks,
ROnIf ID is an Identity column then you can use SCOPE_IDENTITY().
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:OJoIDBu7FHA.1000@.tk2msftngp13.phx.gbl...
> Is there a way using t-sql to get the ID of an inserted row following the
> insert?
> Example:
> @.RowID as int
> @.RowID = INSERT INTO Contacts (Person,Phone_Number) Values('John
> Smith','123-456-7890')
>
> SELECT * FROM Contacts WHERE ID = @.RowID
>
>
> Thanks,
> ROn
>|||Till the days of SQL Server 7.0 we used to rely on @.@.IDENTITY function. But
in my experience that function isn't that reliable. i.e., @.@.IDENTITY isn't
dependent on the current scope. Even if we have inserted some records in a
different table it would fetch us that identity value ;) I am sure we
wouldn't be interested in that.
From SQL Server 2000 there is a new function by name SCOPE_IDENTITY which
returns the last IDENTITY value produced on a connection and by a statement
in the same scope. So its better to use SCOPE_IDENTITY in our select
statement to retrieve the identity value for the record which we inserted no
w.
Example: SELECT SCOPE_IDENTITY()
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Andrew J. Kelly" wrote:

> If ID is an Identity column then you can use SCOPE_IDENTITY().
> --
> Andrew J. Kelly SQL MVP
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:OJoIDBu7FHA.1000@.tk2msftngp13.phx.gbl...
>
>|||So just to clarify another example might by
INSERT INTO PERSON ( SSN, BIRTHDATE)
VALUES( '123456789', '7/4/1976')
SELECT SCOPE_IDENTITY() AS PERSONID;
Is this correct?|||Contraptor@.gmail.com wrote:
> So just to clarify another example might by
> INSERT INTO PERSON ( SSN, BIRTHDATE)
> VALUES( '123456789', '7/4/1976')
> SELECT SCOPE_IDENTITY() AS PERSONID;
> Is this correct?
I would personally use an OUTPUT parameter, but the syntax you have is
correct.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes its correct!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Contraptor@.gmail.com" wrote:

> So just to clarify another example might by
> INSERT INTO PERSON ( SSN, BIRTHDATE)
> VALUES( '123456789', '7/4/1976')
> SELECT SCOPE_IDENTITY() AS PERSONID;
> Is this correct?
>sql

Getting the actual row ID.

Hello everybody...
I'm trying to get the Row ID of a result set. Is there a way to do it?
Something like this i think:
select @.@.rowid, column1, column2
from table
Best regards,
Dalcom.It's a mythical creature that lives in the land of Oracle...

No it doesn't exists

What do you want to do?|||See this link http://www.craigsmullins.com/ssu_0599.htm for information on similar approach.|||I just see information about Identity columns. I don't think, that this is helping to get a record counter.

I would try to create a procedure, which is performing the query by a cursor, and to add a record number within a loop.|||Why a cursor? If the only requirement is to produce some nice looking gapless sequence, some solutions are provided here http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1|||Ok.

I'm just trying to get some information about the record on table.
But, if I can't do it, i'll try other thing :)

Thank you a lot!

dalcom.|||Originally posted by dalcom
Ok.

I'm just trying to get some information about the record on table.
But, if I can't do it, i'll try other thing :)

Thank you a lot!

dalcom.

What informations?|||The identity property is a very powerful and useful, yet under-utilized feature of Microsoft SQL Server.

Give me a break...under-utilized...

Surrogate keys...ho|||Originally posted by Brett Kaiser
Give me a break...under-utilized...

Surrogate keys...ho

Has someone deleted his posts or where do you read this, Brett?|||Originally posted by Frank Kalis
Has someone deleted his posts or where do you read this, Brett?

No, it's in here..

http://www.craigsmullins.com/ssu_0599.htm

Let's take a poll...

Are IDENTITY Columns under utilized?

Well by me they are...

You know what though, it all depends on how that's quantified I think (and If I did a lot more of that, the better off I'd be...MOO)|||They're back up...

and it's...

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ooops...wrong thread...|||Okay, sit back and relax. Get a cup of coffee and enjoy this answer from the one and only Joe Celko to such a question on the MS newsgroups. I really like it.

>> I'm trying to change an existing field [sic] to an
IDENTITY field [sic], but I'm getting syntax errors. It's got to be
simple, but somewhere I'm missing something. <<

What you are missing is the basic concepts of the relational model.
Columns are not fields; rows are not records; tables are not files. An
IDENTITY property cannot be a key by definition. A key is a subset of
attributes that uniquely define an entity in your data model.

The IDENTITY column is a holdover from the early programming language which were very close to the hardware. For example, the fields in a COBOL or FORTRAN program were assumed to be physically located in main storage in the order they were declared in the program.

The early SQLs were based on existing file systems. The data was kept
in physically contiguous disk pages, in physically contiguous rows, made
up of physically contiguous columns. In short, just like a deck of
punch cards or a magnetic tape.

But physically contiguous storage is only one way of building a
relational database and it is not always the best one. But aside from
that, the whole idea of a relational database is that user is not
supposed to know how things are stored at all, much less write code that
depends on the particular physical representation in a particular
release of a particular product.

One of the biggest errors is the IDENTITY column (actually property) in
the Sybase family (SQL Server and Sybase). People actually program with this "feature" and even use it as the primary key for the table! Now, let's go into painful details as to why this thing is bad.

The practical considerations are that IDENTITY is proprietary and
non-portable, so you know that you will have maintenance problems when you change releases or port your system to other products.

But let's look at the logical problems. First try to create a table
with two columns and try to make them both IDENTITY. If you cannot
declare more than one column to be of a certain datatype, then that
thing is not a datatype at all, by definition. It is a property which
belongs to the PHYSICAL table, not the data in the table.

Next, create a table with one column and make it an IDENTITY. Now try
to insert, update and delete different numbers from it. If you cannot
insert, update and delete rows from a table, then it is not a table by
definition.

Finally create a simple table with one IDENTITY and a few other columns.
Use a few statements like

INSERT INTO Foobar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO Foobar (a, b, c) VALUES ('a2', 'b2', 'c2');
INSERT INTO Foobar (a, b, c) VALUES ('a3', 'b3', 'c3');

to put a few rows into the table and notice that the IDENTITY equentially numbered them in the order they were presented. If you delete a row, the gap in the sequence is not filled in and the sequence continues from the highest number that has ever been used in that column in that particular table.

But now use a statement with a query expression in it, like this:

INSERT INTO Foobar (a, b, c)
SELECT x, y, z
FROM Floob;

Since a query result is a table, and a table is a set which has no
ordering, what should the IDENTITY numbers be? The entire, whole,
completed set is presented to Foobar all at once, not a row at a time.
There are (n!) ways to number (n) rows, so which one do you pick? The
answer has been to use whatever the physical order of the result set
happened to be. That non-relational phrase "physical order" again.

But it is actually worse than that. If the same query is executed
again, but with new statistics or after an index has been dropped or
added, the new execution plan could bring the result set back in a
different physical order. Can you explain from a logical model why the
same rows in the second query get different IDENTITY numbers? In the
relational model, they should be treated the same if all the values of
all the attributes are identical.

Using IDENTITY as a primary key is a sign that there is no data model,
only an imitation of a sequential file system. Since this number exists
only as a result of the state of particular piece of hardware at a
particular time, how do you verify that an entity has such a number in
the reality you are modeling?

To quote from Dr. Codd: "..Database users may cause the system to
generate or delete a surrogate, but they have no control over its value,
nor is its value ever displayed to them ..."(Dr. Codd in ACM TODS, pp
409-410) and Codd, E. (1979), Extending the database relational model to
capture more meaning. ACM Transactions on Database Systems, 4(4). pp.
397-434. This means that a surogate ought ot act like an index; created
by the user, managed by the system and NEVER seen by a user. That means
never used in queries.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result that
some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased tohave one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] - is
to introduce entity domains which contain system-assigned surrogates.
Database users may cause the system to generate or delete a surrogate,
but they have no control over its value, nor is its value ever displayed
to them...." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture more
meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

The most common use that a Newbie makes of IDENTITY is to use it as a
record number (under the error that a record nubmer is a key!), so that
he does not have to think about keys, DRI, check digits, proper data
types, international standards and all that hard stuff.

While this was meant as an abstract example, I also fear that you have
not read ISO-11179 because of the silly, redundant, dangerous prefixes
on your code.

--CELKO--|||Check this: Intelligent Versus Surrogate Keys

http://www.bcarter.com/intsurr1.htm|||did you all notice how swiftly dalcom tried to withdraw from this discussion?

Frank, when you wish to make a reference to something joe celko wrote, please provide the URL rather than such a large excerpt -- for one thing, it's less scrolling, and for another, people can then bookmark celko's writing|||Originally posted by r937
Frank, when you wish to make a reference to something joe celko wrote, please provide the URL rather than such a large excerpt -- for one thing, it's less scrolling, and for another, people can then bookmark celko's writing

I will consider this, but didn't I mention it's a post on the MS newsgroups?

getting similar fields in same output row for grouping totals

I have a table similar to the below:

Field1 Field2
EXE1 Age
EXE2 Child Under 10
EXE3 Not a citizen
WEB1 Cares for a child under 10
WEB2 Be a US Citizen

and I am getting the totals for the records grouped by Field1 - not a
problem. So, output is something like:

Field2 Total %
Age 10 .4
Child under 10 15 .5
Not a citizen 15 .5
Cares for < 10 20 .6
Be a US citizen 25 .7

However, IF there is an EXE# basically the same as a WEB#, then I need
to put them together in my report output, like so:

Field EXEtotal WEBtotal Total %
Age 10 0 10 .4
Child <10 15 20 35 1.0
Citizen 15 25 40 1.1

So, I'm getting the data I need returned, but don't know how to sort it
in my report to give me the new output I need.

I think I need to do this in my report output rather than my SQL
because I need to pull the individual information. Is there a way to
say "if Field 1 = 'WEB2' and Field 1 = 'EXE3', list in same row, then
total? I just don't know how to get them in the same row...

Thanks!

I would create a new column in the table to keep track of the parent group, ie.. your Web1 and Exe1 would be in the same Parent group.

Let me know if you can't make sense of it.

sql

getting second row of the table

Hi Everyone,

I want to get the second row of the table. is it possible to get that through a query. For example I have a table

Table A

col1 col2 col3

west east North
hat get mouse

I only want the second row of the table. i don't want the first row. I don't want any other rows. Please let me know if it is possible to do that and how.

Thanks.

You will need to define what you mean by "second row". SQL Server does not store records sequentially, so "second row" really has no meaning unless you specify an ORDER BY in your query.sql

Getting second row in a data source

Hi,

I'm generating a report where 3 photos can be shown on a report if there are 3 photos in our data source.

they are layed out with the main photo on the left, and the other 2 photos on top of each other on the right.

The dataset that is returned from the query has a column called PhotoURL and each photo is a row of the returned dataset

I have inserted three images on the report

The first image I set the value to be =First(Fields!PhotoURL.Value, "Image")

How do you get the second image to have a value of the second row of the dataset, I mean there is no Second function, or movenext that I can see.

The same applies with the third image.

Thanks in advance

Auschucky

Hello Auschucky,

The First function is an aggregation function, which is not suitable for your images-issue.

When the dataset is returning a number of rows, you have to use a list or table on the report to get all rows (images) on the report. However, this doesn't match your report design.

If the dataset always contains one, two or three images, I suggest to change the select statement to return one row with three url fields. You can attach these fields to your images on the report.

Getting row position in a select

I need to get the row position in a select.
SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
How can i get the current row number in the result set like this?
MYFIELD1 MYFIELD2 ROW_POSITION
myfield1value1, myfield2value1, 1
myfield1value2, myfield2value2, 2
myfield1value3, myfield2value3, 3
...
Thanksexamnotes (checcouno@.discussions.microsoft.com) writes:
> I need to get the row position in a select.
> SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
> How can i get the current row number in the result set like this?
> MYFIELD1 MYFIELD2 ROW_POSITION
> myfield1value1, myfield2value1, 1
> myfield1value2, myfield2value2, 2
> myfield1value3, myfield2value3, 3
> ...
In SQL 2000, the best is to create a temp table with an identity column
and insert to that one. The IDENTITY column, will give you the value
you need.
If there is a unique key in the result set, you can also do something
like:
SELECT keycol, col1, col2,
rowno = (SELECT COUNT(*) FROM tbl t2
WHERE t1.keycol <= t2.keycol)
FROM tbl t1
But for large data sets, the performance may not be fantastic.
In SQL 2005 it's a lot easier, as it comes with a row_number() function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
SELECT
OT.*,
(SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
OT.pcolumn ) As row_no
FROM YOUR_TABLE OT
OT is Outer Table Allies and IT is Inner Table Allies
I think that it will help you.
"checcouno" wrote:

> I need to get the row position in a select.
> SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
> How can i get the current row number in the result set like this?
> MYFIELD1 MYFIELD2 ROW_POSITION
> myfield1value1, myfield2value1, 1
> myfield1value2, myfield2value2, 2
> myfield1value3, myfield2value3, 3
> ...
>
> Thanks
>|||Hi
Read this thread
http://www.aspfaq.com/show.asp?id=2427
Regards
R.D
"Akbar khan is a Senior Database develope" wrote:
> Hi
> SELECT
> OT.*,
> (SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
> OT.pcolumn ) As row_no
> FROM YOUR_TABLE OT
> OT is Outer Table Allies and IT is Inner Table Allies
> I think that it will help you.
>
> "checcouno" wrote:
>|||It works only with order by pcolumn!
"Akbar khan is a Senior Database develope" wrote:
> Hi
> SELECT
> OT.*,
> (SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
> OT.pcolumn ) As row_no
> FROM YOUR_TABLE OT
> OT is Outer Table Allies and IT is Inner Table Allies
> I think that it will help you.
>
> "checcouno" wrote:
>sql

Getting row order in Input File when Handling Errors

Hi All,

Well, tha case here is simply that i have a (Suppliers.csv) as an Input.

When taking that file, I do some validation on it's rows (Data type validations, Mandatory Fielda validations..etc).

When some rows to do not meet the requirments i put in these validations , it is supposed to be directed to an (Errors) Table in my SQL DB.

I want to include the order of the invalid row in the input File (The row which did not pass from the pre-mentioned validations) within the (Errors) Table when i direct the invalid rows to it.

Any ideas ?

See the script in my post on surrogate keys.

http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

Place that at the beginning of your data flow to number all rows passing through it. Then in the error flow, you'll have the correct row number.

Getting Row Number in the Db

Hi,
How to select a record which has the row number 2694 in the database?
Thanks
--
pmudBased on what criteria?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
Hi,
How to select a record which has the row number 2694 in the database?
Thanks
--
pmud|||Just to add to Tom's response some, SQL Server 2000 has no concept of a row
number. The order in which the rows are stored is totally up to SQL Server.
Further more the order in which they are returned is also totally up to SQL
Server unless you specify an ORDER BY clause. So row number xxxx means
nothing without any other context.
Andrew J. Kelly SQL MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
> Hi,
> How to select a record which has the row number 2694 in the database?
> Thanks
> --
> pmud|||> How to select a record which has the row number 2694 in the database?
How the heck are we supposed to determine what row number 2694 means? Can
you grab me the 82nd toothpick in that box of 250 toothpicks? You might
have a better chance if I tell you to order by length, or width, or hue. If
I don't tell you what my ordering method is, good luck!|||Yes. Thats my mistake. The actual problem I am facing is that I am trying to
convert a varchar field to a datetime field . The reason it was originally
varchar was that the data was initially imported from spreadsheets so the Db
automatically made it a varchar field.
So, i created another table and set the ActivationDate to a datetime type
instead of a varchar and then I try to import data to this table from the
existing one, but I am getting error:
"Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
error: Conversion invalid for datatypes on column pair 8 ( source column
ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
(DBTYPE_DBTIMESTAMP). "
So I was trying to find out how can I know which is row Number 2694 in the
table. Also, in the varchar activationdate field, all dates are in the forma
t
like:
May 4 2005 12:00 AM
Any ideas on this?
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> How the heck are we supposed to determine what row number 2694 means? Can
> you grab me the 82nd toothpick in that box of 250 toothpicks? You might
> have a better chance if I tell you to order by length, or width, or hue.
If
> I don't tell you what my ordering method is, good luck!
>
>|||Usually, these things are imported in the order in which they occurred in
the source files. Can you go to row 2694 in the spreadsheet itself?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
Yes. Thats my mistake. The actual problem I am facing is that I am trying to
convert a varchar field to a datetime field . The reason it was originally
varchar was that the data was initially imported from spreadsheets so the Db
automatically made it a varchar field.
So, i created another table and set the ActivationDate to a datetime type
instead of a varchar and then I try to import data to this table from the
existing one, but I am getting error:
"Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
error: Conversion invalid for datatypes on column pair 8 ( source column
ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
(DBTYPE_DBTIMESTAMP). "
So I was trying to find out how can I know which is row Number 2694 in the
table. Also, in the varchar activationdate field, all dates are in the
format
like:
May 4 2005 12:00 AM
Any ideas on this?
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> How the heck are we supposed to determine what row number 2694 means? Can
> you grab me the 82nd toothpick in that box of 250 toothpicks? You might
> have a better chance if I tell you to order by length, or width, or hue.
> If
> I don't tell you what my ordering method is, good luck!
>
>|||Oh. dont have the spreadsheet from which this was imported. Is there any
other way of finding out?
Thanks
--
pmud
"Tom Moreau" wrote:

> Usually, these things are imported in the order in which they occurred in
> the source files. Can you go to row 2694 in the spreadsheet itself?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
> Yes. Thats my mistake. The actual problem I am facing is that I am trying
to
> convert a varchar field to a datetime field . The reason it was originally
> varchar was that the data was initially imported from spreadsheets so the
Db
> automatically made it a varchar field.
> So, i created another table and set the ActivationDate to a datetime type
> instead of a varchar and then I try to import data to this table from the
> existing one, but I am getting error:
> "Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
> encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
> error: Conversion invalid for datatypes on column pair 8 ( source column
> ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
> (DBTYPE_DBTIMESTAMP). "
> So I was trying to find out how can I know which is row Number 2694 in the
> table. Also, in the varchar activationdate field, all dates are in the
> format
> like:
> May 4 2005 12:00 AM
> Any ideas on this?
> Thanks
> --
> pmud
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||Not that I know of.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:37A6EC15-7D5B-47D5-A169-BC8A5289D092@.microsoft.com...
Oh. dont have the spreadsheet from which this was imported. Is there any
other way of finding out?
Thanks
--
pmud
"Tom Moreau" wrote:

> Usually, these things are imported in the order in which they occurred in
> the source files. Can you go to row 2694 in the spreadsheet itself?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
> Yes. Thats my mistake. The actual problem I am facing is that I am trying
> to
> convert a varchar field to a datetime field . The reason it was originally
> varchar was that the data was initially imported from spreadsheets so the
> Db
> automatically made it a varchar field.
> So, i created another table and set the ActivationDate to a datetime type
> instead of a varchar and then I try to import data to this table from the
> existing one, but I am getting error:
> "Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
> encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
> error: Conversion invalid for datatypes on column pair 8 ( source column
> ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
> (DBTYPE_DBTIMESTAMP). "
> So I was trying to find out how can I know which is row Number 2694 in the
> table. Also, in the varchar activationdate field, all dates are in the
> format
> like:
> May 4 2005 12:00 AM
> Any ideas on this?
> Thanks
> --
> pmud
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||Look up the ISDATE() function in Books Online, and check which rows contain
invalid values before inserting them in the new table.
ML
http://milambda.blogspot.com/|||simple.
select id = identity(int,1,1), *
into #idRecord
from yourtable
later...
select *
from #idRecord
where id = idCriteria.
See you Later...
Nemir Labopaz
"pmud" <pmud@.discussions.microsoft.com> escribi en el mensaje
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
> Hi,
> How to select a record which has the row number 2694 in the database?
> Thanks
> --
> pmud

getting row before in table

Im using following code to get a row from database, but how do I get the row before this row?

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE getMessage

@.username varchar(24),

@.ID int

AS

BEGIN

SELECT Message

FROM Messages

WHERE Username = @.username AND ID = @.ID

END

GO

And another question how do you get the row after the row?

Thank you.

Paul.

SELECT

Message

,(SELECT TOP 1 Message FROM dbo.Messages M1 WHERE M1.ID < M.ID ORDER BY ID ASC) [priorRecord]

,(SELECT TOP 1 Message FROM dbo.Messages M1 WHERE M1.ID > M.ID ORDER BY ID ASC) [nextRecord]

FROM dbo.Messages M
WHERE UserName = @.UserName AND ID = @.ID

|||

Thank you.

Thats was ment to be 2 separate questions, i guess i just have to delete one of the lines i dont need.

|||

Sorry, I thought you were looking for "bookend" records for the passed ID. I also noticed an error in the subquery returning priorRecord. It should be ordered DESC instead of ASC.

So which answer did I not help you with? ;)

Getting output of a one row into 4 columns

I am trying to find a way how to query following data from a table that looks like this:

ColA ColB ColC

1 2 3

Output should look like this:

ColA

1

2

3

You could use a UNION ALL to combine the results of 3 separate SELECTstatements. If you want to eliminate duplicate values, use aplain UNION instead:

SELECT

   ColA AS ColA

FROM

   myTable

UNION ALL

SELECT

   ColB AS ColA

FROM

   myTable

UNION ALL

SELECT

   ColC AS ColA

FROM

   myTable

|||thanx

Getting one row from a clustered date table

Is it possible to efficiently pull one row from a table that is clustered by the date. What i want to do is pull one row wheather it be the first row the 1028th row or row 3000. Is this possible to do?

What I have is:
SELECT * FROM CUST WHERE '?' = 3000

My cust column is already in order because it is clustered.that query will be efficient only if the '?' column has an index

(that's not the real name of the column, is it?)

also, first, you say clustered by date, and then you say clustered by cust -- that's not possible, because a table can have only one clustered index|||OH sorry CUST is the table and i have a date column that clustered. I do have an index "CUST_ID" but this column is not ordered like the date column. When I view the table the CUST_ID values are scattered i am not sure why.

Also, one more question is there any kind of limit to how many tables i can have in one database?|||of course the CUST_ID values are scattered -- the rows are in sequence by date

getting name of trigger in trigger itself

Hello,
i have a trigger on every table to check that only one row is to handle.
If there are more rows i want to show an error message with the
name of the trigger in which the error occured.
Now i have nearly 100 tables and also so much triggers which are
all identically from the statements inside, only the triggername for
the message is different. So is there a way to get the actual trigger
name instead of having the name hardcoded in each trigger?
tia,
HelmutWhy!!? It seems perverse to prevent multiple row updates. Especially
since the code to do this is probably less efficient than actually
performing the update.
Triggers have global scope and aren't easy to bypass on a live system,
so doing what you propose will make it extremely difficult to perform
any administrative maintenance on the data. Think very carefully about
the implications of this. Even if it is a requirement to limit users in
this way I strongly recommend you do so through SPs rather than
triggers. A trigger should always perform correctly for 0,1 or N rows
updated.
OBJECT_NAME(@.@.PROCID) will return the name of the executing trigger or
proc.
David Portas
SQL Server MVP
--

Getting most recent row from one-to-many relationship

I have two tables, Admission and RecordSummary. The Admission table holds
one row for each resident at our facility and the RecordSummary table holds
one row for each admission the resident has (could be more than 1). They
are linked using a Register Number, which is a 6 digit number. The
RecordSummary table has a column named AdmissionNo, which contains the
admission number.
How can I SELECT a resident and their most recent (i.e. highest AdmissionNo)
RecordSummary table row?
Thanks,
DrewDrew
select * from Admission c
join (select resident ,max(orderdate)orddate
from RecordSummary group by resident )
as der on der.resident =c.resident
order by c.resident
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:%23EYeajbeGHA.4532@.TK2MSFTNGP02.phx.gbl...
>I have two tables, Admission and RecordSummary. The Admission table holds
>one row for each resident at our facility and the RecordSummary table holds
>one row for each admission the resident has (could be more than 1). They
>are linked using a Register Number, which is a 6 digit number. The
>RecordSummary table has a column named AdmissionNo, which contains the
>admission number.
> How can I SELECT a resident and their most recent (i.e. highest
> AdmissionNo) RecordSummary table row?
> Thanks,
> Drew
>|||Thanks!
Drew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ecXPnpbeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> Drew
> select * from Admission c
> join (select resident ,max(orderdate)orddate
> from RecordSummary group by resident )
> as der on der.resident =c.resident
> order by c.resident
>
>
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:%23EYeajbeGHA.4532@.TK2MSFTNGP02.phx.gbl...
>

getting MAX value and displaying record

Hi all,
I basically want to display the single row that has the highest 'jobid' using a SP. I was playing with MAX(jobid) but getting errors about no group by, etc. Where do I begin with this?
SELECT Purchord, JobNo, Descr, customer, jobid
FROM JobsSELECT Purchord, JobNo, Descr, customer, jobid
FROM Jobs
INNER JOIN (SELECT MAX(jobid) jobid FOMR Jobs) HighestID ON Jobs.jobid = HighestID.jobid

-or-

SELECT TOP 1 Purchord, JobNo, Descr, customer, jobid
FROM Jobs
order by jobid desc|||select top 1 Purchord, JobNo, Descr, customer, jobid
from Jobs
order by jobid desc

Edit: beaten!!|||Ohhhh......!!!! Thank you very much guys!|||2 MINUTES 2 LATE, RUDY!

BOO-YAH!

Fastest fingers in the midwest...