How to select distinct records from the table?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to select distinct records from the table??

Author

Topic

learner_study
Starting Member

9 Posts

Posted - 2005-11-06 : 22:24:42

Hi there,

Can anyone help me out??

There is a table master_view in the database and it consists of all the records regarding the employee's entry and exit

The problem is, in the form i need to show, all the rooms (when user clicks on the button), so I used this query

select distinct room_name from master_view where room_name<>'' order by room_name

As I thought, I should only get distinct room names, which are not empty, but I found that in the list of rooms, it displays one empty field,(no room name) and also the room names are repeated for eg, fl-24 is repeated twice, and also fl-2-45 (this is only for showing out the example)

How can I remodify the query ? (I thought that distinct works better..)

I am using sql 8.0

Your help is very much appreciated.

Its a kind of urgent!!!]

Thanks and Regards

Kristen
Test

22859 Posts

Posted - 2005-11-07 : 00:53:45

Hi learner_study, Welcome to SQL Team!

Maybe the blank one has a value of NULL rather than an empty string?

Maybe the "duplicates" have trailing [and/or leading] spaces?

Does this work any better?


select distinct LTrim(RTrim(room_name)) AS room_name 
from master_view 
where     room_name<>'' 
      AND room_name IS NOT NULL
order by room_name

Kristen

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 00:58:40

Hi Kristen,
Thanks for your valuable advice,
but unfortunately, it did not work, and i am still getting the same list.

I am pasting a few of my fields for your reference:
(the first one below this is blank)

01/2005
10/2004
A1-01
A1-01
B1-14
B1-14

Thanks a lot once again,
Just incase, you got something, could you pls help me through this??

Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 01:29:06

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 01:33:49

If you are getting the records out of one column then you can try out this

Select Room_Name From Master_View
Group By Room_Name

Hope this helps

Complicated things can be done by simple thinking

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 01:49:14

Hi,

Thanks for your posts (replies)..

Let me be more clear about my problem and solution:
I am having a table master_view where all the details of employees entry and exit time and date are noted along with the room number he entered in

For showing the report, i have a form where
---there is one button upon clicking which a pop up is opened where I display all the rooms for the user to select once and upon the value a report is generated.
This is the scenario.

Now----
--- When the popup is opened, then I have to show all the room names
----------that is all the distinct rooms should be shown
my sql query for that is
"select distinct room_name from master_view
where room_name <>'' order by room_name'
So my output should be
(assume we have different rooms in that table)
Room1
rOOM2
rOOM3
rOOM4
rOOM5
but my output is shown as
Room1
rOOM2

rOOM3
rOOM3
rOOM4
rOOM5
rOOM5
The query is not taking distinct records!!
Could you pls helpme out, i am in a kind of urgent, because my deadline ends today !!! :(

Thanks very much in advance
Regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 02:01:47

well for this kind of Query i have created the function as posted below.. well you can try out if its work for you..

CREATE FUNCTION IsEmptyVarChar (@CheckVariable Varchar(8000))
RETURNS varchar(8000) AS
BEGIN
IF LEN(LTRIM(RTRIM(@CheckVariable)))= 0
BEGIN
SET @CheckVariable = NULL
END
RETURN @CheckVariable
END

Select Room_Name From Master_View
Where (Dbo.IsEmpTyVarchar(Room_name)) Is Not Null

Hope this helps..

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 02:05:04

What is the datatype of room_name?
Does it have leading or trailing spaces?

See if this works

select distinct ltrim(rtrim(room_name)) from master_view
where room_name <>''

Madhivanan

Failing to plan is Planning to fail

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 02:16:08

Hi

thanks very much for your reply and your valuable time
I tried using both the suggestions,
but none worked..

My brain is drained out!!!
no more new ideas coming in, I thought using distinct on one particular column will always gives back distinct values..
but realied its not now

The datatype of room_name is varchar(20)

Thanks and regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 02:19:16

See this example


declare @t table(room_name varchar(20))
insert into @t Select 'rOOM1' union all Select 'rOOM1' union all Select 'rOOM1' union all 
Select 'rOOM2' union all Select 'rOOM2' union all Select 'rOOM3' union all Select 'rOOM4' 

Select room_name from @t 
Select distinct room_name from @t

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 02:23:54

Are you sure in the select statements you are passing only one Column_Name i.e Room_Name .. if you are passing more the one column_Name then the output would have change????

Complicated things can be done by simple thinking

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 02:37:29

Hi madhivanan,
May I know how to apply that code,
As I look at it, you hardcoded the room name
what if i have 350 different rooms??
Chirag, Yes, I am selected only one column that is room_name and nothing more that that
my sql query is

"select distinct room_name from master_view where room_name <>''
order by room_name"

Thanks and regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 02:40:47

oh oks..

did you tired the query using the group by clause.

Select Room_Name From Master_View
Where Room_Name <> ''
Group By Room_Name

???

Complicated things can be done by simple thinking

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 02:51:21

Hi Chirage,
Using group by does not have any effect on the output
Thanks and Regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 02:56:08

Can you try out this query .. and post first 5 -6 rows.. output..

Select Room_Name, Count(1) From Master_View Where Room_Name <>''
Group By Room_Name

???

Complicated things can be done by simple thinking

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 03:04:31

Hi Chirage,

I am posting the first few output rows:

157
02/2005 39
03/2005 138
05/2005 114
06/2005 24
09/2004 29
09/2005 132
10/2004 10
10/2005 56
11/2004 60
A1-01 2
A1-01 5608
A1-02 887
A1-08 1371
A2-03 3240

Thanks and Regards

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 03:06:53

By the way, what does count(1) returns back?? I saw a lot of numbers, but not able to figure them out, Chirag, could you explain me ??

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 03:12:19

quote:
Originally posted by learner_study

Hi Chirage,

I am posting the first few output rows:

157
02/2005 39
03/2005 138
05/2005 114
06/2005 24
09/2004 29
09/2005 132
10/2004 10
10/2005 56
11/2004 60
A1-01 2
A1-01 5608
A1-02 887
A1-08 1371
A2-03 3240

Thanks and Regards

Are you sure 2 records are same.. means there might be some spaces between A1 - 02.. or some data entry problem ..

becuase RTrim and LTrim will remove the space from the start and end but in between spaces it wont be able to remove.. ??

And about the count(1) its just counts the number of records..

its similar to Count(Room_Name) i just specifed the ordinal position..

Complicated things can be done by simple thinking

learner_study
Starting Member

9 Posts

Posted - 2005-11-07 : 03:33:15

Hi Chirag,
I tried using Len to findout the length of the room_name in this case
for the first room A1-01 i got as 6 and in the second A1-01 i got as 5
i used ltrim and rtrim also, also saw that there are no spaces in between them also
in this case, how can do the distinct??
i am still not able to find a solution??

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 03:46:35

after doing lTrim and Rtrim.. what did you .. ???

means where their len same or still different.. ???

Well. try out self join ..

Select Distinct M1.Room_Name From
Master_View M1 Inner Join Master_View M2
On M1.Room_Name Like '%' + M2.Room_Name + '%'

???

Hope this works for you..

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 04:32:33

For your output, it seems there are no duplicates.

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources