Select Distinct Records

[BiJae]

I'm not from A Microsoft SQL server world but have recently made that transition. I am drawing a blank on how to get what I need out of the database.

I have a single table with a subquery to get an id from another table within it.

SELECT     PeripheralMapID, MaintID, ScheduleID, MaintenanceFlag, MaintenanceTypeID,
                          (SELECT     Schedule.ScheduleOcccurenceID
                            FROM          Schedule
                            WHERE      Maintenance.ScheduleID = Schedule.ScheduleID) AS occur_id, MaintenanceStartTime AS StartTime, MaintenanceEndTime AS EndTime, 
                      DayOfWeek, ModifiedBy, DateModified
FROM         Maintenance
WHERE     (MaintenanceStartTime > GETDATE())

This is working great and returns every value that is within the date range:

Hoffman Estates ASR08 Nov 2 2007 8:00PM Nov 3 2007 7:00AM Nov 2 2007 4:30PM sa
Hoffman Estates ASR08 Nov 5 2007 8:00PM Nov 6 2007 7:00AM Nov 2 2007 4:30PM sa
Hoffman Estates ASR08 Nov 6 2007 8:00PM Nov 7 2007 7:00AM Nov 2 2007 4:30PM sa
Hoffman Estates ASR08 Nov 7 2007 8:00PM Nov 8 2007 7:00AM Nov 2 2007 4:30PM sa
Troy ASR20 Nov 3 2007 9:00PM Dec 4 2007 11:59AM Nov 2 2007 4:31PM sa
Troy ASR20 Dec 1 2007 9:00PM Dec 2 2007 11:59AM Nov 2 2007 4:31PM sa
.........

The problem is this is a scheduling program that allows for recurring scheduling. The program throws all the scheduled items in the table at the time the user makes the schedule. I have a separate table that holds the scheduling pattern.

What I would like to show one record for each distinct PeripheralMapID (which I use to get the name in a different query). I have tried SELECT DISTINCT, however cannot get it to return a distinct value, even when I reduce the query to

SELECT DISTINCT PeripheralMapID from Maintenance

I'm very new at MSSQL and my brain is fried from the full week I've had already. If some one can show me the correct syntax I would be very grateful. I'm sure it's something simple. I've tried to find the answer on the net and developer forums but cannot find. So I'm asking you for help!

Thank you!

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[philhege]

When you issue the simple statement

SELECT DISTINCT PeripheralMapID from Maintenance

do you not receive a recordset of distinct values? That's weird, and sounds like you may have hidden characters in the field.

Based on your query results above (which puzzle me because I don't see a date as the last field), you should receive one record for "Hoffman Estates" and one record for "Troy". This is what the DISTINCT keyword is designed to do.

What are the relationships between the two tables? Do you have multiple schedule entries per maintenance record?

Provide some DDL and a sample recordset and we can solve this puzzle.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[BiJae]

Thank you, Phil!

I have been puzzled by this as well. I come from a MySql environment and have gotten this to work before.

When I run:

SELECT DISTINCT PeripheralMapId from Maintenance

I get the result:

87
87
87
87
87
92
92
92
92
92
etc...

The relationship between the two tables is a one to one relationship, One Maintenance ID to One Schedule ID.

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[gmmastros]

Wow. You have data integrity issues.

What data type are you using for the PeripheralMapId column?

Select Data_Type
From   Information_Schema.Columns
Where  Table_Name = 'Maintenance'
       And Column_Name = 'PeripheralMapId'

The above query will show you the data type for that column. I suspect it's one of the four following data types: char, nchar, varchar, nvarchar.

The only possible way to get distinct data that appears to be a duplicate is if there are 'hidden' characters. By hidden characters, I am really referring to symbols that are not printable, like spaces, tabs, carriage returns, line feeds, etc...

Given that your data appears to hold integers, I would suggest that you change the data type of the column.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BiJae]

Thank you for the suggestion. I hadn't looked at data type.

I ran the above query and got the return "Data_type = decimal".

Shouldn't decimal support select distinct?

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[gmmastros]

All data types support 'Select Distinct'.

The values must be distinct, but I must admit this is a bit strange. Originally, I was expecting you to respond with... Data_Type = varchar. It's pretty easy to have values that appear to be distinct but are actually different. For example, <space>1 is not the same as <space><space>1. Since you cannot have spaces (or other hidden characters like tab & carriage returns) in decimal columns, the only thing I can think of is....

There must be a fractional component to the numbers, but when you display the results of the query, you are only showing the whole number portion of the value. I encourage you to double-check that.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom