|
Something like ?
select * from
(select userid,username from temptable where userid=1 group by userid,username)as a
inner join
(select userid, count as cnt from temptable where score>21
group by userid)as b on a.userid=b.userid
But this doesn't work too
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Got it almost!
select * from
(select userid,username from temptable where userid=1
group by userid,username)as a
left join (select userid, count(*)as cnt from temptable
where score>21 group by userid)as b on a.userid=b.userid
But this returns "NULL" instead of 0. How can I make it 0 ?
Can I use "CASE" on the count? Like:
CASE cnt when NULL then 0 END
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
How about:
SELECT DISTINCT userid
,username
,(SELECT Count(*) FROM temptable t2 WHERE t1.userid=t2.userid AND score > 20) as topscorecount
FROM temptable t1
WHERE userid = 1
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
select count(*),userid,username from template
where username ='Bob'and score > 20
group by userid,username
|
|
|
|
|
Check here[^]
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
select count(*),userid,username from temptable
where username ='Bob' and score > 20
group by userid,username
union
select 0,userid,username from temptable
where username ='Bob'and score < 20
group by userid,username
|
|
|
|
|
set nocount on
create table tempuser (userid int, username varchar(10))
create table tempscore(userid int, score int)
insert into tempuser (userid, username) values (1, 'Bob')
insert into tempscore (userid, score) values(1, 17)
insert into tempscore (userid, score) values(1, 21)
select u.userid, u.username,
coalesce(s.total, 0) as total
from tempuser u
left outer join
(
select userid, count(*) as total
from tempscore
where score > 20
group by userid
) s on s.userid = u.userid
drop table tempscore
drop table tempuser
|
|
|
|
|
Dear friend
I want to write a query that produced single row find the max form two table and also
query that delete all redundant data from table in sql 2005
Thanks and regards
Azad Yadav
|
|
|
|
|
azad yadav wrote: write a query that produced single row find the max form two table
you can get max for each table and then find max from two result . same as this code:
select max(twotable.fieldmatch) from
(
select max(field1) as fieldmatch from table1
UNION
select max(field1) as fieldmatch from table2
)twotable
azad yadav wrote: query that delete all redundant data from table in sql 2005
This is a general question , you must specify what you mean exactly.
|
|
|
|
|
one thing between two table should have delete cascading on master table .
then u can use
delete from mastertable where id = select top 1 id from (select max(id) as id from master a join detail b on a.id=b.id )as a
|
|
|
|
|
I created a table in scott schema and stored some unicode charaters through iSQL*PLus in internet explorer , I can check the data in this invironment correctly while I am checking that through Oracle SQL*Plus it is displaying as ?????? when I installed the oracle I select the required language package as well (Arabic), I can see my data only Interent explore (iSQL*Plus), I can not see the data in Forms , or in Reports
|
|
|
|
|
If you use unicode characters I recommend using nvarchar2 instead of varchar2, then you won't have conversion problems.
It still won't work in SQL plus though as it does not support unicode characters as far as I know.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
Is there anyway that we can extract or backup data from a sql server table to anything, and later importing them?
Is there any popular tool or technique to do so?
|
|
|
|
|
you can generate sql script for you table structure and also data which exist in your table.
if you have sql server 2008 follow this instructions:
Open Sql Server Management Studio
Right Click on your database
go to : Tasks
then: generate scripts...
wizard will shown then
try this wizard you can find it very simple
|
|
|
|
|
I have to do that programatically and i am using Vb.Net or may be C# will do.
|
|
|
|
|
You can create your own SSIS package and then run it with your .NET application
|
|
|
|
|
Hello ,
You can use SMO ( SQL Server Management Objects ) to do that but it might be complicated for a beginner ( in SMO )
|
|
|
|
|
Dear Friends,
I attached sql 2000 database in sql 2005 server and deattaching from 2005 again try to attached with sql 2000 then give error (could not find sysindexes for database id 9 Run dbcc chack table on sysindexes)
Please help me
Thanks and Regards
Azad Yadav
|
|
|
|
|
I will be astonished if you can do this. The idea that you can take a 05 db back to 2000 seems unlikely in the extreme.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
use Integration Service and transfer your database from 2005 to 2000.because you can not restore 2005 on 2000 on usual ways
|
|
|
|
|
Hello,
If you have modified the structure of your database or introduced features specific to SQL Server 2005 like PIVOT in a view or stored procedure, bad luck.
Try to reattach your database to your SQL Server 2005 , check if the compatibility level of your database is always 80 ( SQL Server 2000 ). If yes, bad luck, if it is 90, try to modify it to 80.
Detach it properly and try to reattach it to your SQL Server 2000. You have a little hope of success ( except if you have introduced features specific to 2005 )
|
|
|
|
|
I have deleted some of the records from my SQL server Database table
How can I recover those records?
|
|
|
|
|
By restoring from a backup? You could restore the backup to a new database and copy the rows over.
Scott
|
|
|
|
|
how i can create pyramid in sql server 2005.
|
|
|
|