|
Hi
I have table with three columns
col1, col2, col3
i need distinct values of col1 with any values in col2 and col3, eg if data is
1, A, A
1, B, B
2, C, C
i need my query to bring
1, A, A or 1, B, B (only one not both)
2, C, C
i need query for this. i cannot use select dintinct col1, col2, col3 from table due to 2nd row. i am using sql server.
Regards
Shajeel
|
|
|
|
|
Shajeel wrote: I have table with three columns
col1, col2, col3
i need distinct values of col1 with any values in col2 and col3, eg if data is
1, A, A
1, B, B
2, C, C
i need my query to bring
1, A, A or 1, B, B (only one not both)
2, C, C
Don't know which version of SQL Server you are using. If 2000 then below written sql will solve the problem. If you are using 2005 then you can use ROW_NUMBER() instead of IDENTITY() and there is no need for #TempTable. If you are using 2000 and table has a Identity column then also there is no need for #TempTable.
Identity Column or the higher version (2005) will help you to do in single sql stmt...
select <br />
IDENTITY(INT, 1, 1)AS KeyCol, Col1, col2, col3 into #TempTable from tbldistinct<br />
<br />
select col1,col2,col3 from #TempTable B<br />
where (Select Count(*) from #TempTable A where A.col1 = B.col1 and A.KeyCol <= B.KeyCol) =1<br />
<br />
Drop Table #TempTable
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
|
Use a groupby would be the easiest why without DISTINCT
<br />
select col1, col3, col3<br />
from table<br />
ORDER BY col1, col3, col3<br />
|
|
|
|
|
Does someone knows where I find the SQL Ansi specs?
For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.(John 3:16) ![Badger | [badger,badger,badger,badger...]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/badger.gif)
|
|
|
|
|
|
Hi,
Here is my query.
Select * from Table1
Result
Department NewYork Chicago Washington LA
DepartmentA 1 5 4 0
DepartMentB 2 0 0 3
DepartMentC 0 1 1 1
What I need to do is to get the Top 3 City. ( here Chicago,WashingTon,LA)
Can i filter out the columne with the query ? When I use TOP, it can show top 3 rows.
But, that's not what I want.
Can anybody help me, please?
|
|
|
|
|
RichardBlare wrote: Select * from Table1
I suppose this is you query...and My doubt is
RichardBlare wrote: Department NewYork Chicago Washington LA
Is this your table columns?
RichardBlare wrote: DepartmentA 1 5 4 0
DepartMentB 2 0 0 3
DepartMentC 0 1 1 1
Is this your output?
RichardBlare wrote: Chicago,WashingTon,LA
Is this you want?
RichardBlare wrote: Can anybody help me, please?
If I'm not wrong please tell...Do you need a output that gives you the list of Column names that has maximum values?
If I'm wrong Please explain more clearly what you want...
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Dear John,
I'm sorry that my question confuse you. Actually, I just need the result with the Column names that has maximum values.
Let me try to be clear.
Here is the result of my query.
Department City1 City2 City3 City4 City5 City6
Operations 4 2 6 NULL 1 NULL
Risk Management 4 5 1 NULL 2 NULL
Technology 2 3 5 NULL 1 NULL
I want to remove the column with NULL values.
That's exactly what I want.
Thanks ,
RB
|
|
|
|
|
I think I know what you want
<br />
SELECT * from TABLE<br />
ORDER BY City DESC<br />
ORDER BY WITH SUM
<br />
SELECT *, SUM(City1 + City2 + City3 + City4 + City5 + City6) as Total<br />
from TABLE<br />
ORDER BY Total DESC<br />
|
|
|
|
|
Hi,
How can i list the queues of the Service Broker and clear them?
thank you a lot.
|
|
|
|
|
i faced problem during reading xml file by sql function, xml file is comm separeated.
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
I can imagine you have a problem with a xml-file when it is comma seperated.
Wout Louwers
|
|
|
|
|
how t o call a stored procedure inside a stored procedure
|
|
|
|
|
|
I want stored procedure A to call stored procedure B, which returns a set of records from a table. Then, I want stored procedure A to perform work on these records. How do I call stored procedure B from within stored procedure A?
|
|
|
|
|
didn't you try the link???
ok try this... this sample has a parent stored proc that calls a child stored proc by passing parameter values and then does some insert operation...
Google the Google...[^]
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
yeah i got that one john,
one more thing is...
i have one field in my table which i want to update after inserting some values in the table.
i have written one stored procedure for inserting values,
i want to update one field depending upon the inserting values.
can u help me...
|
|
|
|
|
you can do both Insert and Update in single procedure. I'm not sure about why you want to do in 2 procs...ok... you can go through the below sample.
CREATE PROCEDURE sp_Update
(
@col1 varchar(20)
@ValuePassed varchar(50),
)
AS
declare @Id int
EXEC sp_Insert @ValuePassed, @Id OUTPUT
Update SomeTable
(col1, col2)
VALUES
(@col1, @Id)
RETURN
GO
CREATE PROCEDURE sp_Insert
(
@ValuePassedIn varchar(50),
@Id smallint OUTPUT
)
AS
INSERT INTO LookUpTable(column1)
VALUES(@ValuePassedIn)
SET @Id = Scope_Identity()
END
GO
Regards
J O H N ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
The thing is,
i have one one registration page.
in that if user chooses his own question for security(while signup in yahoo also we will get security question like that) then the table field must b updated and question flag should be "yes"
if i take the custom question option else not.
how we will make that?
|
|
|
|
|
bhattiprolu wrote: i have one one registration page.
in that if user chooses his own question for security(while signup in yahoo also we will get security question like that) then the table field must b updated and question flag should be "yes"
if i take the custom question option else not.
with this information it's quite difficult for me to assume the table structure, their relationships and giving you a stored procedure in working condition.
In the previous post there is a sample procedure. I guess if you understand that you can solve your problem. First do an insert in whichever table you want and then read the newly inserted records PK_ID value using SCOPE_IDENTITY(). with primary key value of the newly inserted record you can perform the subsequent update in whichever table you want...
Hope this helps....
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Hi ! I am writting a Dictionary online website. I want to insert,update,delete data from gridview but I have some trouble.so I want to ask how to do that ? Thanks so much !
|
|
|
|
|
Tuanhung189 wrote: I have some trouble.so I want to ask how to do that ?
How to do what???????? You mean how to write stored proc to insert, update and delete...
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
you can take the celles from grid view after that you can set data in
sqlcommand.commandtext="insert into tablename(col1,col2,col3)values(cell1,cell2,cell3)"
|
|
|
|
|
Hi. I'm hosting on a SQL server 2005 some encrypted data which could be stored as binary (Hex) or base64 (varchar).
What datatype would be best to use varbinary for the hex or varchar for the base64?
Is there any difference between them in memory usage?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
