Query problem

CREATE TABLE [Table1] (
[abc] [int] ,
[xyz] [char] (10)
)

insert into abc (1,'z')
insert into abc (2,'y')
insert into abc (3,'z')
select * from table1 where abc in (3,2)
i want the output as follows
3 z
2 y
not

2 y
3 z
please help me out
You should use an ORDER BY:
SELECT * FROM table1 WHERE abc IN (3,2) ORDER BY abc DESC
|||it seems i haven't posted the question properly,
it's not the case of 3 and 2
it may be
SELECT * FROM table1 WHERE abc IN (3,2,8,4,1,7)
then it won't work
i hope i made more clear the Q
thanks

|||No, I'm sorry it's not clear. I really have no idea what you arelooking for. Maybe you can explain with more examples.
|||Do you just want to order it in descending rather than ascending order?

Query problem

Hi all,

I am trying to write a query to do the following in SQL server but am
struggling:

I have a table with a int number field in it and I want to find out
the lowest unused number in the table. Unfortunately, the numbers are
not necesarily sequential (due to deletes). So I may have the
following records:

num
--
1
2
3
4
6
8
9
10

I want a query to return me the value 5 - i.e. the lowest unused int
in the table (assuming counting starts at 1). I know I could do this
using a temporary table containing a full sequence of possible numbers
(given some ceiling) using something like:
select min(num) from temptable where num not in (select num from
maintable)
This would however mean creating a temporary table which I am trying
to avoid. I could also use a cursor to itterate through the records
above to find the lowest unused number but it seems overkill.

I'm sure there must be a way of doing this using a simple (possibly
nested) query. Any ideas?

Thanks in advance

--JamesSELECT COALESCE(MIN(T1.num)+1,1)
FROM YourTable AS T1
LEFT JOIN YourTable AS T2
ON T1.num = T2.num - 1
WHERE T2.num IS NULL

--
David Portas
SQL Server MVP
--|||David,
Just what I needed - cheers - nice bit of SQL too!

Regards

--James|||SELECT MIN(F1.num + 1)
FROM Foobar AS F1
UNION
VALUE (0) -- needs to be SELECT 0 in dialect
WHERE (T1.num +1)
NOT IN (SELECT num FROM Foobar);

This will give you an answer on the high end if the sequence is
complete.sql

query problem

Hi,
we have a problem with query and data can be reproduced
as :
create table dd1(dd int,dd1 int)
create table dd(dd int,dd1 int)
insert into dd values(1,10)
insert into dd values(2,20)
insert into dd1 values(2,20)
insert into dd1 values(3,20)
THIS STATEMENT WORKS
--
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd
union all
select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd
THIS STATEMENT IS GIVING ERROR AT GROUP BY CLAUSE
----
select dd,sum(dd1),sum(dd2)
from
((
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd)
union all
(select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd)
)
group by dd
How to rewrite this query?
Thanks
--HarvinderHarvinder,
select dd,sum(dd1),sum(dd2)
from
((
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd)
union all
(select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd)
) a
group by dd
the derived table needs an alias.
Quentin
"harvinder" <hs@.metratech.com> wrote in message
news:02b301c33f3b$1a33ab40$a301280a@.phx.gbl...
> Hi,
> we have a problem with query and data can be reproduced
> as :
> create table dd1(dd int,dd1 int)
> create table dd(dd int,dd1 int)
> insert into dd values(1,10)
> insert into dd values(2,20)
> insert into dd1 values(2,20)
> insert into dd1 values(3,20)
> THIS STATEMENT WORKS
> --
> select dd,sum(dd1) dd1,count(*) dd2 from dd
> group by dd
> union all
> select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
> group by dd
> THIS STATEMENT IS GIVING ERROR AT GROUP BY CLAUSE
> ----
> select dd,sum(dd1),sum(dd2)
> from
> ((
> select dd,sum(dd1) dd1,count(*) dd2 from dd
> group by dd)
> union all
> (select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
> group by dd)
> )
> group by dd
> How to rewrite this query?
> Thanks
> --Harvinder
>sql

Query problem

I have a table called PriceName having schema
CREATE TABLE PriceName (PriceNameID INT IDENTITY(1,1), Price
DECIMAL(9,2), PriceName VARCHAR(100))
In this table the data is populated in the Price field. Now i want to
update this table to update the PriceName column, that shows the range
in which that price lies.
Ex is shown below
PriceNameID Price PriceName
1 200 0-200
2 300 200-300
3 350 300-350
.....
....
99 1500 1400-1500
100 NULL 1500 & Above
Can someone suggest me the update query for this '
RegardsUPDATE PriceName p1
SET PriceName = ISNULL(CONVERT(VARCHAR(9), (SELECT MAX(p2.Price) FROM
PriceName p2 WHERE p2.Price < p1.Price)), '0') +
ISNULL(' - ' + CONVERT(VARCHAR(9), Price), ' & Above')
Jacco Schalkwijk
SQL Server MVP
"Prashant Thakwani" <thakwani@.rediffmail.com> wrote in message
news:bf0d42bf.0403120517.3815a063@.posting.google.com...
> I have a table called PriceName having schema
>
> CREATE TABLE PriceName (PriceNameID INT IDENTITY(1,1), Price
> DECIMAL(9,2), PriceName VARCHAR(100))
>
> In this table the data is populated in the Price field. Now i want to
> update this table to update the PriceName column, that shows the range
> in which that price lies.
> Ex is shown below
> PriceNameID Price PriceName
> 1 200 0-200
> 2 300 200-300
> 3 350 300-350
> .....
> ....
> 99 1500 1400-1500
> 100 NULL 1500 & Above
>
>
> Can someone suggest me the update query for this '
>
> Regards|||Have u run that script '
I am getting the following error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'p1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.
Pls take care of this
Regards
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||UPDATE PriceName
SET PriceName = ISNULL(CONVERT(VARCHAR(9), (SELECT MAX(p2.Price) FROM
PriceName p2 WHERE (p2.Price < PriceName.Price) OR PriceName.Price IS
NULL)), '0') +
ISNULL(' - ' + CONVERT(VARCHAR(9), Price), ' & Above')
Jacco Schalkwijk
SQL Server MVP
"Prashant Thakwani" <thakwani@.rediffmail.com> wrote in message
news:OJxZIkDCEHA.624@.TK2MSFTNGP10.phx.gbl...
> Have u run that script '
> I am getting the following error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'p1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near ')'.
>
> Pls take care of this
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Query Performance

I have two tables.
Employee
EmployeeCode int Primary Key

Employee_Stock
EmployeeCode int
StockCode varchar(10)
Primay key on (Employeecode, StockCode)..

There is no foreign key relation between these 2 tables.
Now my question is which query give more performance. and why?
1. Select * from
Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode

2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.

Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
Now if we add foreignkey, is it going to improve the performance or not?

Thanks
RameshForeign keys enforce business rules and relational integrity, but in themselves do not increase performance. In some cases they may actually decrease performance while the database maintains relational integrity during inserts, updates, and deletes. You should still establish them to save you all sorts of headaches later when you find that your data does not make sense.

If you have already have indexes on your joined columns then I don't think your query is about as efficient as it is going to get. Show a query plan and verify that the indexes are being used.

Query of counts

I have a table with three fields: AcctNo INT, Code CHAR(1), Amount MONEY

The Code has three values: 'A', 'B', or 'C'.

Each AcctNo has 1, 2, or all 3 of the Codes assigned to it (ie. AcctNo is not unique)

I need to know how many AcctNos have one value assigned to the Code field, how many have two, and how many have three and I need to know the sum of the Amount for each group.

Can this be done in one statement or do I need three statements?

Fred

The query below returns the results as requested. Is this what you're looking for?

Chris

DECLARE @.Values TABLE (AcctNo INT, Code CHAR(1), Amount MONEY)

INSERT INTO @.Values(AcctNo, Code, Amount)

SELECT 1, 'A', 1.00 UNION

SELECT 1, 'B', 2.50 UNION

SELECT 2, 'C', 1.25 UNION

SELECT 3, 'C', 1.43 UNION

SELECT 3, 'A', 1.96 UNION

SELECT 3, 'B', 2.00 UNION

SELECT 4, 'C', 1.43 UNION

SELECT 4, 'A', 1.96 UNION

SELECT 4, 'B', 2.10 UNION

SELECT 5, 'B', 0.92 UNION

SELECT 5, 'A', 1.24 UNION

SELECT 6, 'C', 0.02 UNION

SELECT 7, 'B', 0.11

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

|||

Yes, that is what I wanted first.

But I need to add another level of complexity. There are duplicates of the AccountNo and Code, only the Amount is different. I need to count duplicates as one. (or anything more than one; there were some with three times and four)

Thanks,

Fred

|||

All you should need is an extra DISTINCT, see below.

Chris

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(DISTINCT Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

Query needed

Hi All,
I have a table
Task(
idTask int primary key,
sTask varchar(60)
)
with the following data.
idTask sName
--
1 B
2 A
3 C
4 B
5 D
6 E
7 B
8 D
Now i want a query which return the data in the following format
2 A
1,4,7 B
3 C
5,8 D
TIA
Satyahttp://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Satya" wrote:

> Hi All,
> I have a table
> Task(
> idTask int primary key,
> sTask varchar(60)
> )
> with the following data.
> idTask sName
> --
> 1 B
> 2 A
> 3 C
> 4 B
> 5 D
> 6 E
> 7 B
> 8 D
> Now i want a query which return the data in the following format
> 2 A
> 1,4,7 B
> 3 C
> 5,8 D
> TIA
> Satya
>|||This is called violationof First Normal Form and good SQL programmers
do not do it. Display is handled in the front end in a tiered
architecture. This is more fundamental than just SQL, so you might
want to take a class in software engineering.|||No sql coding necessary with the RAC utility for S2k.
See the @.concatenate operator in the Help file.
RAC and QALite @.
www.rac4sql.net|||Yes this is not relational approach.
It should be handled in presentation layer.
I am missing basics :(
Thanks for the post
Satya
"--CELKO--" wrote:

> This is called violationof First Normal Form and good SQL programmers
> do not do it. Display is handled in the front end in a tiered
> architecture. This is more fundamental than just SQL, so you might
> want to take a class in software engineering.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111762443.359407.260920@.f14g2000cwb.googlegroups.com...
>..........'good SQL programmers'
A contradiction in terms? What do you think C.Date would say about this:)