Query help

For reasons i wont go into i need to have an identity field when returning a
select statement
ie Select * from Table1 returnn
Field1 field2 etc
aa aa aa
bb bb bb
and i want
Field1 field2 etc IDENT
aa aa aa 0
bb bb bb 1
any clues ?Assuming that you PK is field1 and the order of Id cols is also based on Fie
ld1
select
t1.*,
(select count(*) from table1 t2 where t2.Field1 < t1.Field1) as IdCol
from Table1 t1
- Sha Anand
"Peter Newman" wrote:

> For reasons i wont go into i need to have an identity field when returning
a
> select statement
> ie Select * from Table1 returnn
> Field1 field2 etc
> aa aa aa
> bb bb bb
> and i want
> Field1 field2 etc IDENT
> aa aa aa 0
> bb bb bb 1
> any clues ?|||How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Peter Newman" wrote:

> For reasons i wont go into i need to have an identity field when returning
a
> select statement
> ie Select * from Table1 returnn
> Field1 field2 etc
> aa aa aa
> bb bb bb
> and i want
> Field1 field2 etc IDENT
> aa aa aa 0
> bb bb bb 1
> any clues ?

Query Help

Hi i need some help in a Query,
i have a table
Table1 ( field1) with values 1,2,3,4 in the field 1.
when i run,
select field1 from table1
the query returns
1
2
3
4
but instead i need to get these values comma seperated
like 1,2,3,4. i dont want to use cursors for this.
can somebody help me in this.
thanks in advance.
sathyaHi
The only safe way is to use a cursor, alternatively the better method it to
do it on the client or a third party tool such as http://rac4sql.net/
John
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Isn't this something cool:
declare @.List varchar(1000)
select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
select @.List
hth
Quentin
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Hi
This solution has been suggested by many people in the past (including
myself) but it is not a safe solution.
John
"Quentin Ran" <ab@.who.com> wrote in message
news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
> Isn't this something cool:
> declare @.List varchar(1000)
> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
> select @.List
> hth
> Quentin
>
> "sathya" <softwaremaniac@.hotmail.com> wrote in message
> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> > Hi i need some help in a Query,
> >
> > i have a table
> >
> > Table1 ( field1) with values 1,2,3,4 in the field 1.
> >
> > when i run,
> >
> > select field1 from table1
> > the query returns
> > 1
> > 2
> > 3
> > 4
> >
> > but instead i need to get these values comma seperated
> > like 1,2,3,4. i dont want to use cursors for this.
> >
> > can somebody help me in this.
> > thanks in advance.
> >
> > sathya
>|||hi john,
What is the problem you are expecting
for me is in a loop, if i use it will slow down the
process.
it works fine for me
thanks
>--Original Message--
>Hi
>This solution has been suggested by many people in the
past (including
>myself) but it is not a safe solution.
>John
>"Quentin Ran" <ab@.who.com> wrote in message
>news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
>> Isn't this something cool:
>> declare @.List varchar(1000)
>> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim
(col1)) from table1
>> select @.List
>> hth
>> Quentin
>>
>> "sathya" <softwaremaniac@.hotmail.com> wrote in message
>> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
>> > Hi i need some help in a Query,
>> >
>> > i have a table
>> >
>> > Table1 ( field1) with values 1,2,3,4 in the field 1.
>> >
>> > when i run,
>> >
>> > select field1 from table1
>> > the query returns
>> > 1
>> > 2
>> > 3
>> > 4
>> >
>> > but instead i need to get these values comma
seperated
>> > like 1,2,3,4. i dont want to use cursors for this.
>> >
>> > can somebody help me in this.
>> > thanks in advance.
>> >
>> > sathya
>>
>
>.
>

Query Frustrations

Hi i got this query

SELECT COUNT(dbo.Table1.activity) AS TOTAL, dbo.Table2.name
FROM dbo.Table1 LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.activity = dbo.Table2.type
WHERE (dbo.Table1.activity = 2)
GROUP BY dbo.Table2.name

#-------------------------
and the result is this.....

name TOTAL
Not Sold 12179
Hangup 12179
Doesn't Want to Give ACH 12179
Doesn't Want to Give CC 12179
#-------------------------

what chages should i make in my query to have a result like this....

name TOTAL
Not Sold 13
Hangup 300
Doesn't Want to Give ACH 25
Doesn't Want to Give CC 30

here's my table definitions
Table1
---
activity
2
3
2
3
5

Table2
-------
type | name
2 | Not Sold
3 | Blah
2 | Hangup
2 | Doesn't Want to Give ACH
5 | Do Not Call
2 | Doesn't Want to Give CCType needs to be unique in Table2.sql

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
..
and many other questions need to be asked and answered to come up with the
best choices.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> For this particular query, an index on (col2, col1) would most likely be
>> faster. But index tuning isn't done one query at a time.
>> What other queries reference table1 and access col1 and col2?
>> You need to design indexes so that all the queries that access the table
>> will perform reasonably well. There may be other choices of indexes that
>> work well for this query, and also work well for many other queries.
>> What percentage of the table have col2 values less than 100?
>> How many rows fit per page?
>> Are you also performing updates to the table?
>> ..
>> and many other questions need to be asked and answered to come up with
>> the best choices.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster
>> ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>>
>