Database

Jörgen Andersson29-Mar-09 8:48

Jörgen Andersson

29-Mar-09 8:48

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

Eytukan29-Mar-09 19:18

Eytukan

29-Mar-09 19:18

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

anup keshari30-Mar-09 5:04

30-Mar-09 5:04

select count(*),userid,username from template
where username ='Bob'and score > 20
group by userid,username

Eytukan30-Mar-09 5:08

Eytukan

30-Mar-09 5:08

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

anup keshari30-Mar-09 5:28

30-Mar-09 5:28

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

Query (find max from two table resultant row should be single in sql 2005)

i.j.russell30-Mar-09 10:48

i.j.russell

30-Mar-09 10:48

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

azad yadav28-Mar-09 21:43

azad yadav

28-Mar-09 21:43

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

Re: Query (find max from two table resultant row should be single in sql 2005)

Reza Raad28-Mar-09 21:48

Re: Query (find max from two table resultant row should be single in sql 2005)

28-Mar-09 21:48

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.

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

anup keshari30-Mar-09 5:11

Storing and retriving unicode characters in Oracle

30-Mar-09 5:11

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

Saleem Tahiri28-Mar-09 21:25

Saleem Tahiri

28-Mar-09 21:25

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

Re: Storing and retriving unicode characters in Oracle

Jörgen Andersson29-Mar-09 7:59

Jörgen Andersson

29-Mar-09 7:59

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

Backing up data from a Sql Server Table

xbiplav28-Mar-09 16:20

xbiplav

28-Mar-09 16:20

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?

Reza Raad28-Mar-09 21:53

28-Mar-09 21:53

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

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

xbiplav29-Mar-09 6:59

xbiplav

29-Mar-09 6:59

I have to do that programatically and i am using Vb.Net or may be C# will do.

Reza Raad29-Mar-09 10:34

29-Mar-09 10:34

You can create your own SSIS package and then run it with your .NET application

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

How to Reattached database in SQL 2000 server after upgrading with SQL 2005

Lambin6-Apr-09 18:59

Lambin

6-Apr-09 18:59

Hello ,

You can use SMO ( SQL Server Management Objects ) to do that but it might be complicated for a beginner ( in SMO )

azad yadav28-Mar-09 3:02

azad yadav

28-Mar-09 3:02

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

Re: How to Reattached database in SQL 2000 server after upgrading with SQL 2005

Mycroft Holmes28-Mar-09 16:00

Mycroft Holmes

28-Mar-09 16:00

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

Re: How to Reattached database in SQL 2000 server after upgrading with SQL 2005

Reza Raad28-Mar-09 23:25

Re: How to Reattached database in SQL 2000 server after upgrading with SQL 2005

28-Mar-09 23:25

use Integration Service and transfer your database from 2005 to 2000.because you can not restore 2005 on 2000 on usual ways

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

Lambin6-Apr-09 18:56

Lambin

6-Apr-09 18:56

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 )

Recoverying deleted record from SQL server table

Saleem Tahiri28-Mar-09 0:34

Saleem Tahiri

28-Mar-09 0:34

I have deleted some of the records from my SQL server Database table

How can I recover those records?
Rose | [Rose]

Re: Recoverying deleted record from SQL server table

scottgp28-Mar-09 1:42

scottgp

28-Mar-09 1:42

By restoring from a backup? You could restore the backup to a new database and copy the rows over.
Scott

Sql server

anup keshari27-Mar-09 21:42

27-Mar-09 21:42

how i can create pyramid in sql server 2005.

Re: Sql server

Blue_Boy28-Mar-09 0:27

Blue_Boy

28-Mar-09 0:27

Which pyramid you want to create by SQL Server 2005?!

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Sql server

anup keshari28-Mar-09 0:35