SELECT DISTINCT...

Question

SELECT DISTINCT...

Surjit Madiwalar

Mr or Mrs. 500

Points: 553
More actions
August 4, 2003 at 2:58 am

#159533

Hi Everone
select name from user_table where id_user in (5,25,5)
when i run the above query in query analayser in SQL Server 2000 i get the result for 2 records...i am expecting 3 records....but i think by default its taking as select distinct.Should i change some configurations in Enterprise manager to get 3 records.....how should i do that..can any one pls help me out...
I am running SQL Server 2000 on Windows 2000 Pro...
Rgds
Surjit

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Surjit,

quote:
select name from user_table where id_user in (5,25,5)
when i run the above query in query analayser in SQL Server 2000 i get the result for 2 records...i am expecting 3 records....but i think by default its taking as select distinct.Should i change some configurations in Enterprise manager to get 3 records.....how should i do that..can any one pls help me out...

maybe I'm missing something, but for id_user = 5, id_user=25 and again id_user = 5, so I think it's quite correct to return only two instead of three.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 2

Hi Frank,

Thats right when u select distinct...when i query only select...then it should display all records..i need all records for my application which tracks history...so i think by default Sql Server is setting to select distinct

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Surjit,

quote:
Thats right when u select distinct...when i query only select...then it should display all records..i need all records for my application which tracks history...so i think by default Sql Server is setting to select distinct

that has nothing to do with DISTINCT. It is simply identical data!

Try this



select name from user_table where id_user=5 or id_user=25 or id_user=5

Should also return only two records

You want to return a row twice that only exists once in you db.

I think that this won't work.

Why do you want to do this?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 4

Hi Frank,

Thats true....i need to change my business logic.

Thanks a lot for ur quick responses.

Rgds

Surjit

xgbnow SSC Veteran Points: 205 More actions · Answer 5

quote:
Hi Everone
select name from user_table where id_user in (5,25,5)
when i run the above query in query analayser in SQL Server 2000 i get the result for 2 records...i am expecting 3 records....but i think by default its taking as select distinct.Should i change some configurations in Enterprise manager to get 3 records.....how should i do that..can any one pls help me out...
I am running SQL Server 2000 on Windows 2000 Pro...
Rgds
Surjit

Perhaps, I am missing something here as well. But if you want to return ALL of the records then that is simple.

SELECT name FROM user_table

It is not that SQL server defaults to DISTINCT you have limited the results of your query using the WHERE clause.

Why would you include the same condition twice anyways?

WHERE id_user IN (5,25,5)

or

WHERE id_user = 5 OR id_user = 25 OR id_user = 5

Duplicating the condition is not going to yield any more results it is redundant for no reason.

I would also look at why you have duplicate data in the table and see of you cannot find a way to eliminate duplicate data. It kind of goes against the whole idea of having relational database and the theroy of normalization.

Let me know if I am way off or maybe you could provide some more information.

Larry

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 6

Hi all,

Actually i have 2 tables :tb_user and tb_reports.Now whenever anybody modifies the report i update in tb_reports table with user id which is Primary key in tb_user. Table tb_reports has modified_by as one of the columns.Whenever anybody updates reports their user id is stored as for e.g 5, and further 5,25,5...i am storing these values as varchar in modified_by column. So when i display history for the report i should be able to diaplay all the users whoever has modified even if user is repeated

Let me know if there is better solution

Surjit

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hi Surjit,

quote:
Actually i have 2 tables :tb_user and tb_reports.Now whenever anybody modifies the report i update in tb_reports table with user id which is Primary key in tb_user. Table tb_reports has modified_by as one of the columns.Whenever anybody updates reports their user id is stored as for e.g 5, and further 5,25,5...i am storing these values as varchar in modified_by column. So when i display history for the report i should be able to diaplay all the users whoever has modified even if user is repeated

so, when you have a parent table user and a child table report, you can use an INNER JOIN on your PrimaryKey to show all record that mathc on this PrimaryKey. Something like

SELECT * FROM reports a INNER JOIN user b ON a.<PrimaryKey> = b.<PrimaryKey>

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 8

Hi Frank,

When i run ur query i get number of records posted by particular user.But i want list of all users who has edited the report something like below

Monthly Report posted on Date Aug 1,2003 Time 08:45:05 By Mr Bush

Edited by

* Bill gates Date Aug 6,2003 Time 09:30:34

* Larry Elison Date Aug 5,2003 Time 10:10:10

* Bill gates Date Aug 3,2003 Time 11:11:34

Now i am storing user ids in modified_by column as varchar like 5,25,5 in tb_reports table, so when i try to display users 5,25,5 I should be able to dispaly as above

Thanks,

Surjit

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
When i run ur query i get number of records posted by particular user.But i want list of all users who has edited the report something like below
Monthly Report posted on Date Aug 1,2003 Time 08:45:05 By Mr Bush
Edited by
* Bill gates Date Aug 6,2003 Time 09:30:34
* Larry Elison Date Aug 5,2003 Time 10:10:10
* Bill gates Date Aug 3,2003 Time 11:11:34

wooh, interesting users you have

Would you mind posting your table structures and query string(s)?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 10

tb_user table

_____________

id_user(PK) auto_increment

firstname varchar(50)

last_name varchar(50)

email varchar(50)

....

tb_report table

________________

id_report(PK) auto_increment

posted_by ==id_user which is PK of tb_user

title varchar(100)

description text

modified_by varcgar(100) which gets updated as [previous value of modified_by + new value and then ,]5,25,5,.....

modified_date datetime

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

Hi Surjit,

quote:
modified_by varcgar(100) which gets updated as [previous value of modified_by + new value and then ,]5,25,5,.....

while trying to follow your example, one thing pops up first.

Why do use modified_by in the way you've described?

I understand it, that you have one row per user in you user table, one row per report in your report table and in this table you update modified_by each time a modification happens? Am I right?

I don't think you can create a monthly report in the way you want. I mean it might be better to create a completely new row for every modification to the report. When doing so, the modified_date suddenly makes sense. Otherwise it would contain only one date (I assume the one of last modification).

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 12

Values in the tables

table tb_user

###############

id_user;name;email

1; abc; abc@abc.com

2; xyz; xyz@xyz.com

3; pqr; pqr@pqr.com

table tb_report

###############

id_report;posted_by;title;description;modified_by;modified_date

1; 1; test; testing; 2,1,; 8/2/2003,8/3/2003,

2; 2; test; testing; 1,2,1,; 8/1/2003,8/2/2003,8/3/2003,

In table tb_report each value of modified_by column corresponds to modified_date values.Using Sever side script ASP we can split the values of both modified_by and modified_date values.So when we split modified_by column value we get in variable as for e.g 1,2,1.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

Hi Surjit,

quote:
Values in the tables
table tb_user
###############
id_user;name;email
1; abc; abc@abc.com
2; xyz; xyz@xyz.com
3; pqr; pqr@pqr.com
table tb_report
###############
id_report;posted_by;title;description;modified_by;modified_date
1; 1; test; testing; 2,1,; 8/2/2003,8/3/2003,
2; 2; test; testing; 1,2,1,; 8/1/2003,8/2/2003,8/3/2003,
In table tb_report each value of modified_by column corresponds to modified_date values.Using Sever side script ASP we can split the values of both modified_by and modified_date values.So when we split modified_by column value we get in variable as for e.g 1,2,1.

sure you can split the value into whatever you need, but....

- what if there are more modifications than the max length of your fields allow to be entered?

- you don't take advantage of the relational logic between parent-child relations in a 1:n relationship.

I strongly believe you should add a single row in your child table for each modification. You then can relate parent and child and get very easily you desired monthly report, without having to perform additional string operations.

I think, you're building up problem that need not to be there

Cheers,

Frank

Edited by - a5xo3z1 on 08/07/2003 03:29:37 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 14

Hi Frank,

So ur suggesting to have new row in tb_reports table for each modification so that querying will be easy.I will try that.

Thanks & Rgds,

Surjit