Identity column: likeselect

Showing posts with label likeselect. Show all posts

Friday, March 30, 2012

If IN is an OR .. what would be the equivalent AND

when one uses a where clause like
Select ...
where ID IN (125,234,300)
It's a shorthand OR operation.
I am wondering if there's a similar way to do an AND? Or do we need to
simply use the usual syntax:
Select ...
where ID = 125 and ID = 234 and ID = 300
Is there a better/shorthand way to achieve multiple ANDs?No, not really. IN() is not just shorthand for OR predicates; you can
have a set in the parentheses that's returned from a subquery (like
"WHERE c1 IN (SELECT Foo FROM dbo.Bar) ...") - you can't do that with OR
predicates.
Often joins can produce the same behaviour that AND predicates can. For
example, if you had a table that had all those IDs in it, you could
simple do an INNER JOIN with your main table to achieve the same
affect. For example:
SELECT * FROM MyTable AS t
INNER JOIN MyIDTable AS i ON t.ID = i.ID
But this would require creating the table if it doesn't already exist
and then populating it. However, this approach can sometimes, in
specific cases, perform better than long strings of ANDs especially if
the query optimiser can take advantage of indexes that have been defined
on the tables.
*mike hodgson*
http://sqlnerd.blogspot.com
rsphere@.gmail.com wrote:

>when one uses a where clause like
>Select ...
>where ID IN (125,234,300)
>It's a shorthand OR operation.
>I am wondering if there's a similar way to do an AND? Or do we need to
>simply use the usual syntax:
>Select ...
>where ID = 125 and ID = 234 and ID = 300
>Is there a better/shorthand way to achieve multiple ANDs?
>
>|||If the column ID is an IDENTITY or UNIQUE column, then your logic will
return nothing: It is impossible a value in this column could be equal to
125 and also equals to 234...
If your real intention is the select records with its ID column's value is
125, OR 234, OR 300, it is perfect to use IN clause.
If your intention is to NOT select anything, then you can use "WHERE ID=125
AND ID=235...", but you may make it simpe: "SELECT...FROM...WHERE 1=2"
<rsphere@.gmail.com> wrote in message
news:1141689883.663017.174190@.v46g2000cwv.googlegroups.com...
> when one uses a where clause like
> Select ...
> where ID IN (125,234,300)
> It's a shorthand OR operation.
> I am wondering if there's a similar way to do an AND? Or do we need to
> simply use the usual syntax:
> Select ...
> where ID = 125 and ID = 234 and ID = 300
> Is there a better/shorthand way to achieve multiple ANDs?
>|||thanks for the replies.
the id column is not indentity - i should have specified that to be
more clear.
heres the query:
SELECT td.TestDescriptionID as tid, (td.TestName + td.ScoreType +
td.Category + td.SubCategory) as fulltestname FROM
TestDescription td INNER JOIN ReportFact rf ON
td.TestDescriptionID = rf.TestDescriptionID WHERE (rf.DistrictID =
@.DistrictId) and rf.CampusID in (767,795,942,768) and (rf.TotCount <>
0) GROUP BY td.TestDescriptionID, td.TestName, td.ScoreType,
td.Category, td.SubCategory order by fulltestname
What I need however is a list of only the tests that all campuses
(767,795,942,768) use. Not the total list of tests that any of the
campuses use.
Would AND still be best?
I am just worried about performance. The list is coming from a
dropdownlist - and they can select any number of items in the list.|||These are equivelent:
rf.CampusID in (767,795,942,768)
(rf.CampusID = 767 OR
rf.CampusID = 795 OR
rf.CampusID = 942 OR
rf.CampusID = 768)
NOTE THE PARENTHESES!! This set of ORs has to be in parentheses so it
can then be ANDed with the other criteria.
Personally I find the IN approach to be clearer and simpler, and it
will perform at least as well as the ORs.
Roy Harvey
Beacon Falls, CT
On 6 Mar 2006 17:12:20 -0800, rsphere@.gmail.com wrote:

>thanks for the replies.
>the id column is not indentity - i should have specified that to be
>more clear.
>heres the query:
>SELECT td.TestDescriptionID as tid, (td.TestName + td.ScoreType +
>td.Category + td.SubCategory) as fulltestname FROM
>TestDescription td INNER JOIN ReportFact rf ON
>td.TestDescriptionID = rf.TestDescriptionID WHERE (rf.DistrictID =
>@.DistrictId) and rf.CampusID in (767,795,942,768) and (rf.TotCount <>
>0) GROUP BY td.TestDescriptionID, td.TestName, td.ScoreType,
>td.Category, td.SubCategory order by fulltestname
>What I need however is a list of only the tests that all campuses
>(767,795,942,768) use. Not the total list of tests that any of the
>campuses use.
>Would AND still be best?
>I am just worried about performance. The list is coming from a
>dropdownlist - and they can select any number of items in the list.|||But i need AND functionality to reduce the set to only matches in all
campuses. I love the IN statement too - but was hoping that there was
something like it with AND behavior...but I gather there is not.|||Yes. The IN (SELECT x FROM Foobar) predicate is the same as
x = ANY(SELECT x FROM Foobar)
Likewise IN (<list> ) is the same as
x = ANY(VALUES <list> )
We also have the
x = ALL (SELECT x FROM Foobar)
or
x = ALL (VALUES <list> )
It is a part of SQL that a lot of newbies do not know about.|||I guess I just don't see what your problem is. If you really must
have AND, there is yet another alternative.

>These are equivelent:
>rf.CampusID in (767,795,942,768)
>(rf.CampusID = 767 OR
> rf.CampusID = 795 OR
> rf.CampusID = 942 OR
> rf.CampusID = 768)
This is the same as either of the above, for what that is worth.
rf.CampusID <> 767 AND
rf.CampusID <> 795 AND
rf.CampusID <> 942 AND
rf.CampusID <> 768
Roy Harvey
Beacon Falls, CT
On 6 Mar 2006 17:47:51 -0800, rsphere@.gmail.com wrote:

>But i need AND functionality to reduce the set to only matches in all
>campuses. I love the IN statement too - but was hoping that there was
>something like it with AND behavior...but I gather there is not.|||i guess I am not explaining it very well.
i was simply polling to see if there was a shorthand or efficient
alternative to using
rf.CampusID= 767 AND
rf.CampusID = 795 AND
...
Since I am in a situation where a user might select up to 40 or 50
items. That's a lot of ANDs... and may not perform well.
I am not a very experienced SQL programmer so I am not sure if I am
explaining this accurately.|||On 6 Mar 2006 18:23:18 -0800, rsphere@.gmail.com wrote:

>i guess I am not explaining it very well.
>i was simply polling to see if there was a shorthand or efficient
>alternative to using
>rf.CampusID= 767 AND
>rf.CampusID = 795 AND
It can never be true that:
rf.CampusID= 767 AND
rf.CampusID = 795
It can be equal to one, or the other, or neither, but it can NOT be
equal to both, and that test would only be true if it was equal to
both.

>Since I am in a situation where a user might select up to 40 or 50
>items. That's a lot of ANDs... and may not perform well.
Use IN. That is what it is there for. It is clearer than any of the
alternatives. It is as efficient as any alternative that will produce
the same results. The optimizer will see to that, you don't have to
second guess it. Concentrate on writting clear SQL that is correct.
Until you have done that it is futile to worry about efficiency. Once
you have done that, you usually find that it is efficient - or that
you need to look at indexing.
Good luck!
Roy Harvey
Beacon Falls, CT

Monday, March 26, 2012

if condition within select query sql server 2000

Hi all,

I have to write a select query which need some logic to be implemented.

Query is like

select name,number,active,

if active ="true" then

select @.days=days from tbl_shdsheet

else

@.days=''

end

from tbl_emp

In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet

So how can i write queery for this.

You probably want to implement this using a Left Join:http://www.w3schools.com/sql/sql_join.asp

|||

Hi Thanks for replying.

My Problem has solved.I wrote a user defined function with the if else condition to be checked by sending the value to be checked as parameter to that function

|||

You can use user defined function to achieve this but you can easily do this by using case..when..else..end statement and joins. Below is your modified statement. You need to fill in the column names for mapping both the tables.

select name,number,active,
case
when te.active ='true'then ts.days
else''
end as Days
from tbl_emp teleftjoin tbl_shdsheeton te.[column to map] = ts.[column to map]

Friday, February 24, 2012

Identity column in query result

I am firing a query like:

SELECT TaskName, StartDate FROMTasks
WHERE StartDate >= '01-Aug-2003'

Now the result comes as:

TaskName StartDate
--------
Task1 01-Aug-2003
Task5 10-Oct-2003

etc.

Now what I want is this:

Sl. No. TaskName StartDate
-----------
1 Task1 01-Aug-2003
2 Task5 10-Oct-2003

How do I get the Sl. No. field (it does not exist in the table).
Can I do it?

-surajit"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> I am firing a query like:
> SELECT TaskName, StartDate FROMTasks
> WHERE StartDate >= '01-Aug-2003'
> Now the result comes as:
> TaskName StartDate
> --------
> Task1 01-Aug-2003
> Task5 10-Oct-2003
> etc.
> Now what I want is this:
>
> Sl. No. TaskName StartDate
> -----------
> 1 Task1 01-Aug-2003
> 2 Task5 10-Oct-2003
>
> How do I get the Sl. No. field (it does not exist in the table).
> Can I do it?
> -surajit

SELECT T1.StartDate, T1.TaskName, COUNT(*) AS Cnt
FROM Tasks AS T1
INNER JOIN
Tasks AS T2
ON T2.StartDate <= T1.StartDate AND
T1.StartDate >= '20030801' AND
T2.StartDate >= '20030801'
GROUP BY T1.StartDate, T1.TaskName

Regards,
jag|||Hi

If TaskName is unique and gives you the correct order then try:

select ( SELECT COUNT(*) FROM tmp c WHERE c.TaskName <= b.TaskName ) as id,
b.TaskName, b.Startdate
from Tasks b
order by TaskName

John

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> I am firing a query like:
> SELECT TaskName, StartDate FROMTasks
> WHERE StartDate >= '01-Aug-2003'
> Now the result comes as:
> TaskName StartDate
> --------
> Task1 01-Aug-2003
> Task5 10-Oct-2003
> etc.
> Now what I want is this:
>
> Sl. No. TaskName StartDate
> -----------
> 1 Task1 01-Aug-2003
> 2 Task5 10-Oct-2003
>
> How do I get the Sl. No. field (it does not exist in the table).
> Can I do it?
> -surajit|||Surajit,

I'm not saying this is a better way, it's just another option:

SELECT identity(int) as Sl,
TaskName,
StartDate
INTO #Tmp
FROM Tasks
WHERE StartDate >= '01-Aug-2003'

SELECT * FROM #Tmp

DROP TABLE #Tmp

Shervin

Unfortunately none of them worked!
The serial no. column returned some big values and do not appear
serially.

The last one (using a temp table) is ok, but I do not want to use temp
tables.

Task name may not be unique, there are Task_ID and Task_UID which
forms a composite key for this table.

But how can that help?

Please help/comment.

-surajit

> "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > I am firing a query like:
> > SELECT TaskName, StartDate FROMTasks
> > WHERE StartDate >= '01-Aug-2003'
> > Now the result comes as:
> > TaskName StartDate
> > --------
> > Task1 01-Aug-2003
> > Task5 10-Oct-2003
> > etc.
> > Now what I want is this:
> > Sl. No. TaskName StartDate
> > -----------
> > 1 Task1 01-Aug-2003
> > 2 Task5 10-Oct-2003
> > How do I get the Sl. No. field (it does not exist in the table).
> > Can I do it?
> > -surajit|||Surajit,

Can you tell us more about the nature of your problem? How big is your
table? Isn't it possible to generate sequence numbers in your front-end
application? Why don't you want to use temporary tables? Is it because of
some technical or performance problem or you just prefer not to use temp
tables?

Shervin

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> Hi all,
> Unfortunately none of them worked!
> The serial no. column returned some big values and do not appear
> serially.
> The last one (using a temp table) is ok, but I do not want to use temp
> tables.
> Task name may not be unique, there are Task_ID and Task_UID which
> forms a composite key for this table.
> But how can that help?
> Please help/comment.
> -surajit
>
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > I am firing a query like:
> > > > SELECT TaskName, StartDate FROMTasks
> > > WHERE StartDate >= '01-Aug-2003'
> > > > Now the result comes as:
> > > > TaskName StartDate
> > > --------
> > > Task1 01-Aug-2003
> > > Task5 10-Oct-2003
> > > > etc.
> > > > Now what I want is this:
> > > > > Sl. No. TaskName StartDate
> > > -----------
> > > 1 Task1 01-Aug-2003
> > > 2 Task5 10-Oct-2003
> > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > Can I do it?
> > > > -surajit|||Hi

It would help if you posted DDL (Create table statements), example data
(Insert Statements) and your own attempts to solve the problem, then
everyone would have a clear understanding of your problem actually is.

John

The table has 25000 rows at this moment and increasing.

I can obviously generate a sequence in the front end, but:
1) I want to do it in the back end, to check if it is possible
2) I dont want to use Temp tables

If I accept any of these, I can solve this problem right away.

But I want to find an option of doing it in the backend, following
these constraints, and want to know if it is possible.

If it is not possible, then it is important for me to know that it is
NOT POSSIBLE.

The description of the table does not help much, as I would like to
use this idea (if I get any) in any query result from any table.

Thanks,

-surajit

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> Surajit,
> Can you tell us more about the nature of your problem? How big is your
> table? Isn't it possible to generate sequence numbers in your front-end
> application? Why don't you want to use temporary tables? Is it because of
> some technical or performance problem or you just prefer not to use temp
> tables?
> Shervin
> "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > Hi all,
> > Unfortunately none of them worked!
> > The serial no. column returned some big values and do not appear
> > serially.
> > The last one (using a temp table) is ok, but I do not want to use temp
> > tables.
> > Task name may not be unique, there are Task_ID and Task_UID which
> > forms a composite key for this table.
> > But how can that help?
> > Please help/comment.
> > -surajit
> > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > I am firing a query like:
> > > > > > SELECT TaskName, StartDate FROMTasks
> > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > Now the result comes as:
> > > > > > TaskName StartDate
> > > > --------
> > > > Task1 01-Aug-2003
> > > > Task5 10-Oct-2003
> > > > > > etc.
> > > > > > Now what I want is this:
> > > > > > > > Sl. No. TaskName StartDate
> > > > -----------
> > > > 1 Task1 01-Aug-2003
> > > > 2 Task5 10-Oct-2003
> > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > Can I do it?
> > > > > > -surajit|||Hi,
I don't think you can get a serial number with a simple SELECT, unless
you migrate on Oracle RDBMS which has a pseudo-column named ROWNUM
very useful for your purpose; rather, on MS-SQL 2K, you can create a
function that return a table. This example run on Northwind sample
database:

######

create function dbo.FN_ORDERS()
returns @.tab table (
ROWNUM int,
OrderId int,
OrderDate datetime,
ShipName nvarchar(40)
) as
begin
declare @.OrderId int
declare @.OrderDate datetime
declare @.ShipName nvarchar(40)
declare @.i int
declare c cursor for
select OrderId, OrderDate, ShipName from Northwind.dbo.Orders
set @.i = 0
open c
fetch c into @.OrderId, @.OrderDate, @.ShipName
while @.@.fetch_status = 0 begin
set @.i = @.i + 1
insert @.tab values (@.i, @.OrderId, @.OrderDate, @.ShipName)
fetch c into @.OrderId, @.OrderDate, @.ShipName
end
close c
deallocate c
return
end
go

select * from dbo.FN_ORDERS()

######

Obviously you have to create one function per table and change your
front end code...

Bye.

s_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0310082016.4fe9bca5@.posting.google.com>...
> Hi,
> The table has 25000 rows at this moment and increasing.
> I can obviously generate a sequence in the front end, but:
> 1) I want to do it in the back end, to check if it is possible
> 2) I dont want to use Temp tables
> If I accept any of these, I can solve this problem right away.
> But I want to find an option of doing it in the backend, following
> these constraints, and want to know if it is possible.
> If it is not possible, then it is important for me to know that it is
> NOT POSSIBLE.
> The description of the table does not help much, as I would like to
> use this idea (if I get any) in any query result from any table.
> Thanks,
> -surajit
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> > Surajit,
> > Can you tell us more about the nature of your problem? How big is your
> > table? Isn't it possible to generate sequence numbers in your front-end
> > application? Why don't you want to use temporary tables? Is it because of
> > some technical or performance problem or you just prefer not to use temp
> > tables?
> > Shervin
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > Hi all,
> > > > Unfortunately none of them worked!
> > > The serial no. column returned some big values and do not appear
> > > serially.
> > > > The last one (using a temp table) is ok, but I do not want to use temp
> > > tables.
> > > > Task name may not be unique, there are Task_ID and Task_UID which
> > > forms a composite key for this table.
> > > > But how can that help?
> > > > Please help/comment.
> > > > -surajit
> > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > I am firing a query like:
> > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > Now the result comes as:
> > > > > > > > TaskName StartDate
> > > > > --------
> > > > > Task1 01-Aug-2003
> > > > > Task5 10-Oct-2003
> > > > > > > > etc.
> > > > > > > > Now what I want is this:
> > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > -----------
> > > > > 1 Task1 01-Aug-2003
> > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > Can I do it?
> > > > > > > > -surajit|||Surajit,

I'm afraid you can't find a straight forward solution for this problem. If
you really want to do this without a temp table and on the back end, the
only other way that I can think about is a self-join and counting the
records (as both Johns posted), but this solution is not efficient on large
result sets.

I wish SQL-Server had a ROWNUM pseudo column like Oracle.

Shervin

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310082016.4fe9bca5@.posting.google.c om...
> Hi,
> The table has 25000 rows at this moment and increasing.
> I can obviously generate a sequence in the front end, but:
> 1) I want to do it in the back end, to check if it is possible
> 2) I dont want to use Temp tables
> If I accept any of these, I can solve this problem right away.
> But I want to find an option of doing it in the backend, following
> these constraints, and want to know if it is possible.
> If it is not possible, then it is important for me to know that it is
> NOT POSSIBLE.
> The description of the table does not help much, as I would like to
> use this idea (if I get any) in any query result from any table.
> Thanks,
> -surajit
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vo8icukkap6l9a@.corp.supernews.com>...
> > Surajit,
> > Can you tell us more about the nature of your problem? How big is your
> > table? Isn't it possible to generate sequence numbers in your front-end
> > application? Why don't you want to use temporary tables? Is it because
of
> > some technical or performance problem or you just prefer not to use temp
> > tables?
> > Shervin
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > Hi all,
> > > > Unfortunately none of them worked!
> > > The serial no. column returned some big values and do not appear
> > > serially.
> > > > The last one (using a temp table) is ok, but I do not want to use temp
> > > tables.
> > > > Task name may not be unique, there are Task_ID and Task_UID which
> > > forms a composite key for this table.
> > > > But how can that help?
> > > > Please help/comment.
> > > > -surajit
> > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > I am firing a query like:
> > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > Now the result comes as:
> > > > > > > > TaskName StartDate
> > > > > --------
> > > > > Task1 01-Aug-2003
> > > > > Task5 10-Oct-2003
> > > > > > > > etc.
> > > > > > > > Now what I want is this:
> > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > -----------
> > > > > 1 Task1 01-Aug-2003
> > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > Can I do it?
> > > > > > > > -surajit|||Hi,

Thanks for youir solution.
Although it uses the idea of inserting the data in a table, it is a good solution.

So, a simple SELECT can not do the trick.

Thanks a lot to you all, who contributed, and helped me a lot!

regards,
-surajit

mj_23@.libero.it (Mauro) wrote in message news:<a2af5c1f.0310090553.279c32c7@.posting.google.com>...
> Hi,
> I don't think you can get a serial number with a simple SELECT, unless
> you migrate on Oracle RDBMS which has a pseudo-column named ROWNUM
> very useful for your purpose; rather, on MS-SQL 2K, you can create a
> function that return a table. This example run on Northwind sample
> database:
> ######
> create function dbo.FN_ORDERS()
> returns @.tab table (
> ROWNUM int,
> OrderId int,
> OrderDate datetime,
> ShipName nvarchar(40)
> ) as
> begin
> declare @.OrderId int
> declare @.OrderDate datetime
> declare @.ShipName nvarchar(40)
> declare @.i int
> declare c cursor for
> select OrderId, OrderDate, ShipName from Northwind.dbo.Orders
> set @.i = 0
> open c
> fetch c into @.OrderId, @.OrderDate, @.ShipName
> while @.@.fetch_status = 0 begin
> set @.i = @.i + 1
> insert @.tab values (@.i, @.OrderId, @.OrderDate, @.ShipName)
> fetch c into @.OrderId, @.OrderDate, @.ShipName
> end
> close c
> deallocate c
> return
> end
> go
> select * from dbo.FN_ORDERS()
> ######
>
> Obviously you have to create one function per table and change your
> front end code...
> Bye.
>
> s_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0310082016.4fe9bca5@.posting.google.com>...
> > Hi,
> > The table has 25000 rows at this moment and increasing.
> > I can obviously generate a sequence in the front end, but:
> > 1) I want to do it in the back end, to check if it is possible
> > 2) I dont want to use Temp tables
> > If I accept any of these, I can solve this problem right away.
> > But I want to find an option of doing it in the backend, following
> > these constraints, and want to know if it is possible.
> > If it is not possible, then it is important for me to know that it is
> > NOT POSSIBLE.
> > The description of the table does not help much, as I would like to
> > use this idea (if I get any) in any query result from any table.
> > Thanks,
> > -surajit
> > "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> > > Surajit,
> > > > Can you tell us more about the nature of your problem? How big is your
> > > table? Isn't it possible to generate sequence numbers in your front-end
> > > application? Why don't you want to use temporary tables? Is it because of
> > > some technical or performance problem or you just prefer not to use temp
> > > tables?
> > > > Shervin
> > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > > Hi all,
> > > > > > Unfortunately none of them worked!
> > > > The serial no. column returned some big values and do not appear
> > > > serially.
> > > > > > The last one (using a temp table) is ok, but I do not want to use temp
> > > > tables.
> > > > > > Task name may not be unique, there are Task_ID and Task_UID which
> > > > forms a composite key for this table.
> > > > > > But how can that help?
> > > > > > Please help/comment.
> > > > > > -surajit
> > > > > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > > I am firing a query like:
> > > > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > > > Now the result comes as:
> > > > > > > > > > TaskName StartDate
> > > > > > --------
> > > > > > Task1 01-Aug-2003
> > > > > > Task5 10-Oct-2003
> > > > > > > > > > etc.
> > > > > > > > > > Now what I want is this:
> > > > > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > > -----------
> > > > > > 1 Task1 01-Aug-2003
> > > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > > Can I do it?
> > > > > > > > > > -surajit|||[posted and mailed, please reply in news]

Surajit Laha (s_laha@.rediffmail.com) writes:
> Thanks for youir solution.
> Although it uses the idea of inserting the data in a table, it is a good
> solution.
> So, a simple SELECT can not do the trick.

Actually it can. See the script below. However, the performance is likely
to be bad. Using a temp table with an identity column would be a lot faster.

CREATE TABLE surajit (taskid int NOT NULL,
taskuid int NOT NULL,
taskname varchar(12) NOT NULL,
startdate datetime NOT NULL,
PRIMARY KEY (taskid, taskuid))
go
INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (1, 1, 'Task 1', '20021212')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (1, 3, 'Task 1.2', '20021224')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (2, 1, 'Task 2', '20030605')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (3, 1, 'Task 3', '20010915')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (3, 2, 'Task 3', '20011015')

go
SELECT taskname, startdate,
cnt = (SELECT COUNT(*)
FROM surajit b
WHERE b.taskname < a.taskname OR
(b.taskname = a.taskname AND
b.taskid < a.taskid OR
(b.taskid = a.taskid AND
b.taskuid < a.taskuid))) + 1
FROM surajit a
ORDER BY cnt

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Friday, March 30, 2012

If IN is an OR .. what would be the equivalent AND

Monday, March 26, 2012

if condition within select query sql server 2000

Friday, February 24, 2012

Identity column in query result

Identity column

Blog Archive

About Me