[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select Distinct Rows Sql Server

Posted on 2010-08-31
8
Medium Priority
?
762 Views
Last Modified: 2012-06-22
I need to select distinct rows based on 2 columns. If both columns have the same data, then it should only select one. Here is my select statement without distinct:

"SELECT TeacherID, InstrumentID, ScheduleID, right(convert(varchar(19), EventStart, 100),6) As Time, EventStart, EventEnd FROM Schedule WHERE InstrumentID = @InstrumentID and TeacherID = @TeacherID and DatePart(dw, EventStart) = @DayofWeekID"

The 2 columns that it should check for distinct values are:
1 - right(convert(varchar(19), EventStart, 100),6) As Time
2 - DatePart(dw, EventStart)

Basically the only column is "EventStart", but I am extracting the Time from EventStart, and I'm extracting the DayOfWeek from EventStart, which is a datetime field. So if the Day of Week and the Time are the same, I only want unique rows.

Thank you.
0
Comment
Question by:mlg101
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 8

Accepted Solution

by:
Mikkk earned 1600 total points
ID: 33574176
SELECT TeacherID, InstrumentID, ScheduleID, right(convert(varchar(19), EventStart, 100),6) As Time, EventStart, EventEnd
FROM Schedule
WHERE ID IN ("SELECT MAX(ID) FROM Schedule WHERE InstrumentID = @InstrumentID and TeacherID = @TeacherID and DatePart(dw, EventStart) = @DayofWeekID
Group by right(convert(varchar(19), EventStart, 100),6) As Time, DatePart(dw, EventStart) )


// ID is the primary key of the table
0
 
LVL 7

Expert Comment

by:jhp333
ID: 33574181
Just put "DISTINCT" after the "SELECT":
SELECT DISTINCT TeacherID, InstrumentID, ScheduleID....
0
 
LVL 8

Assisted Solution

by:paololabe
paololabe earned 400 total points
ID: 33574205
only a correction on Mikk answer

SELECT TeacherID, InstrumentID, ScheduleID, right(convert(varchar(19), EventStart, 100),6) As Time, EventStart, EventEnd
FROM Schedule
WHERE  ID IN ("SELECT MAX(ID) FROM Schedule WHERE InstrumentID = @InstrumentID  and TeacherID = @TeacherID and DatePart(dw, EventStart) = @DayofWeekID
Group by right(convert(varchar(19), EventStart, 100),6) , DatePart(dw, EventStart) )

as is not allowed in group by statement
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Mikkk
ID: 33576180
:_(
0
 
LVL 1

Author Comment

by:mlg101
ID: 33577740
Mikkk, in my rush, I thought it was you that corrected your own answer. I should have awarded The points to you because I had it figured out before poalobe posted that. I figured out it didn't need the "as" as soon as I tried running the SQL statement the first time. Again, I thought it was you. I will try to figure out how to change it to accept your answer.
0
 
LVL 8

Expert Comment

by:Mikkk
ID: 33577923
mlg101, don't worry!

See you!
0
 
LVL 1

Author Closing Comment

by:mlg101
ID: 33605107
Changed the award because I accidently awarded all points to the wrong person, although he did help.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question