|
USE [TempDB]
GO
CREATE TABLE #Tracker(
[Id] [int] NOT NULL,
[Ticker] [nvarchar](4) NOT NULL,
[SlopeOfLine] [int] NOT NULL,
)
GO
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'MSFT', 5)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'MSFT', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'MSFT', 0)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'MSFT', -3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'DELL', -2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'DELL', -1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'DELL', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'DELL', 4)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'DELL', 3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'DELL', 4)
GO
SELECT Id, Ticker, SlopeOfLIne
FROM #Tracker t
WHERE Id = (SELECT TOP 1 Id
FROM #Tracker
WHERE Ticker = t.Ticker
AND SlopeOfLine < 0
ORDER BY Id DESC)
GO
DROP TABLE #Tracker
GO
|
|
|
|
|
This works too! Thanks Russell.
|
|
|
|
|
CREATE TABLE #Tracker(
[Id] [int] NOT NULL,
[Ticker] [nvarchar](4) NOT NULL,
[SlopeOfLine] [int] NOT NULL,
)
GO
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'MSFT', 5)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'MSFT', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'MSFT', 0)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'MSFT', -3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'DELL', -2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'DELL', -1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'DELL', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'DELL', 4)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'DELL', 3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'DELL', 4)
GO
SELECT T1.Id, T1.Ticker, T1.SlopeOfLIne
FROM #Tracker t1
LEFT OUTER JOIN (SELECT * FROM #Tracker WHERE SlopeOfLine<0) T2 ON
T2.Ticker = t1.Ticker
AND T2.Id > t1.Id
WHERE T2.Id IS NULL
AND t1.SlopeOfLine < 0
GO
This may or may not be faster than the subquery method used above.
|
|
|
|
|
Yes it works! Thanks a lot.
|
|
|
|
|
Thanks for all the help guys. I'll try each one of them soon! 
|
|
|
|
|
Even this works
SELECT X.Id,X.Ticker,Y.SlopeOfLine FROM
(SELECT MAX(Id)Id,Ticker
FROM @Tracker
WHERE SlopeOfLine < 0
GROUP BY Ticker)X JOIN @Tracker Y ON X.Id = Y.Id AND X.Ticker = Y.Ticker
Niladri Biswas
|
|
|
|
|
Hi all, I have a question about avg function.
How do I query from 6 different tables and avegrage them.
All of the tables have same number and type of coloumn.
thanks in advance.
|
|
|
|
|
I'm not sure if these work, but they should worth a try:
Select (t1.col1, t2.col1,t3.col1, t4.col1, t5.col1, t6.col1)/6
From table1 as t1, table2 as t2, table3 as t3, table4 as t4, table5 as t5, table6 as t6
or
use nested Select statement (use the sql AVG function on a grand total, and in the nested select statement you can define that the grand total is sum of individual columns).
or
use User-Defined-Function (you can create a function that takes in 6 parameters and return an average).
|
|
|
|
|
Hi James Shao, Thanks for your answer, I tryied the first one it seems like not wroking with sqlite.
I am not sure how to implement the second statement since I am (sorry to say) not good at sql yet.
this is the first time I am working with sql. reading books and getting help form online this is how
I am getting my work done. May be after this app I will have some knowledge about sql.
if second statement does not wrok then third option is the one I will have to do.
thanks very much for your feedback and help.
modified on Wednesday, January 13, 2010 11:19 AM
|
|
|
|
|
You may want to use a sub-query to solve this problem. Try something like this:
Suppose you have six tables t1, t2, ..., t6, with a column called c1 you want to average.
select avg(c1) from
(
select c1 from t1 where ...<put your condition here>
union all
select c1 from t2 where ...<put your condition here>
union all
select c1 from t3 where ...<put your condition here>
union all
select c1 from t4 where ...<put your condition here>
union all
select c1 from t5 where ...<put your condition here>
union all
select c1 from t6 where ...<put your condition here>
)
That will return the average of the column from all six tables.
|
|
|
|
|
Try this
Assuming the tables have the following structure with data
Tbl1
ID1 NUM1
1 10
2 20
Tbl2
ID2 NUM2
1 11
2 22
Tbl3
ID3 NUM3
1 1
2 2
3 3
Tbl4
ID4 NUM4
1 41
Tbl5
ID5 NUM5
1 109
Tbl6
ID6 NUM6
1 61
2 62
Query:
SELECT AvgNum = AVG(N)FROM(
SELECT N=NUM1 FROM Tbl1 UNION ALL
SELECT NUM2 FROM Tbl2 UNION ALL
SELECT NUM3 FROM Tbl3 UNION ALL
SELECT NUM4 FROM Tbl4 UNION ALL
SELECT NUM5 FROM Tbl5 UNION ALL
SELECT NUM6 FROM Tbl6
)X
Output:
AvgNum
31
Niladri Biswas
|
|
|
|
|
I have written a small Scalar UDF that excepts a VarChar value and check to see if the value is NULL.
If the value is null an empty string should be returned else the value passed to the UDF should be returned.
If the value is NULL an empty string is returned, so that bit works fine, but is I do something like this:-
SELECT dbo.NullToString('Hello world')
The value returned by the UDF is 'H', where I would expect 'Hello world'
Here's the UDF code, hopefully someone can enlighten me to my mistake.
CREATE FUNCTION NullToString
(
@value VarChar(255)
)
RETURNS VarChar
AS
BEGIN
DECLARE @ReturnValue VarChar(255)
IF(@value Is NULL)
BEGIN
SET @ReturnValue = ''
END
ELSE
BEGIN
SET @ReturnValue = @value
END
RETURN @ReturnValue
END
GO
Thanks
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Sounds like a ANSI->UTF problem.
Try to use NVarChar(255) istead of VarChar(255).
Greetings
Covean
|
|
|
|
|
Im guessing that the default length of a VarChar is 1
RETURNS VarChar
should possibly be
RETURNS VarChar(255)
BTW, you know there is a built-in function for this:
ISNULL(@someVariable,'')
IsNull Function (T-SQL)[^]
|
|
|
|
|
Thanks for the quick reply, and no I did not know there was a build-in function, so thanks for pointing that out to me.
You learn something new everyday
Thanks again
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
There is also the coalesce function, which returns the first non-null value from a list
select coalesce(col1val,col2val,'')
from table1
I cannot remember how many values you can have in the list, but its quite a few. I find it useful instead of nesting isnull's
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
Ashfield wrote: There is also the coalesce function, which returns the first non-null value from a list
That's interesting, I wish I'd known about that one 12 months ago.
I shell be making use of that.
Thanks Bob
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Pleased to have been of help
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
I have a update query, But need with Order by Clause.
I cannot identify the error....
update sales_master set un_rate = (select qtnmaster.un_rate where qtnmaster.itemcode=sales_master.itemcode order by (0+qtnmaster.Qtnno))
Thanks For The Ideas...
|
|
|
|
|
Why on earth would you need an order by in an update clause (illegal for a start) you are identifying the individual records to update!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can use an ORDER BY clause in an UPDATE statement as long as it is in a subquery.
|
|
|
|
|
Have a look at CROSS APPLY.
|
|
|
|
|
There is no problem with the order by clause. However, your subquery may return multiple values, which makes the assignment not valid.
You need to refine your subquery so that only the desired value is returned. For example, when you use order by clause and use "top 1", then the "un_rate" of the record with the smallest "Qtnno" value is returned. Without a clear knowledge of your original intension, I don't have a clear answer to your question.
modified on Tuesday, January 12, 2010 11:02 AM
|
|
|
|
|
In T-SQL
Update sales_master
set un_rate = qtnmaster.un_rate
from sales_master
inner join qtnmaster
ON sales_master.itemcode = qtnmaster.itemcode
|
|
|
|
|
This will only work if there are no more than 1 record in qtnmaster per itemcode.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
