Solved

Help with SELECT DISTINCT

Posted on 2010-11-19
7
442 Views
Last Modified: 2012-05-10
Can someone help me fix this SELECT query.  

Here's what I'm TRYING to do.

I have a table that stores several columns, one of which is a JobNumber.  There could be multiple records through the table that have the same exact JobNumber.

I need to get a Count of all the records in the table that have the same JobNumber.  However I also need to show only the distinct records from that table.

Here's what I tried to write but it doesn't provide what I'm looking for.

      SELECT DISTINCT (e.JobNumber), e.ArrivalDate, e.EventName,
                        (SELECT COUNT(*)
                         FROM Events
                         WHERE JobNumber = e.JobNumber) As NoOfRecords
      FROM         Events e
      WHERE e.ArrivalDate > @TodaysDate
      ORDER BY e.ArrivalDate

I want the results to look something like this:

JobNumber             Arrival Date        Event Name       No Of Records
1                             1/1/2011              Boss Meeting         15
2                             1/15/2011            Client Meeting         8

How can I achieve this?
0
Comment
Question by:cdemott33
7 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34176963
SELECT e.JobNumber, max(e.ArrivalDate), MAX(e.EventName), COUNT(*) as NoOfRecord
FROM Events e
WHERE e.ArrivalDate > @TodaysDate
group by e.JobNumber
ORDER BY e.ArrivalDate
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34176972
Remove the alias

SELECT JobNumber, max(ArrivalDate), MAX(EventName), COUNT(*) as 'No Of Records'
FROM Events
WHERE ArrivalDate > @TodaysDate
group by JobNumber
ORDER BY ArrivalDate
0
 

Author Comment

by:cdemott33
ID: 34176980
I'm getting this error:

Column ArrivalDate is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Expert Comment

by:DFW_Ed
ID: 34177009
order by occurs last and with it written as-is there technically isn't an "ArrivalDate" column as aggregated columns do not have names

http://www.bennadel.com/blog/70-SQL-Query-Order-of-Operations.htm

you can name a column with "arrivaldate" or choose a different "order by" column
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 34177027
SELECT JobNumber, max(ArrivalDate)  ArrivalDate, MAX(EventName), COUNT(*) as 'No Of Records'
FROM Events
WHERE ArrivalDate > @TodaysDate
group by JobNumber
ORDER BY ArrivalDate
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34177285
try this
select * from (
select *,COUNT(*) over (partition by JobNumber) cnt,
       ROW_NUMBER() over (partition by JobNumber order by ArrivalDate desc) rn
  from [Events]
 where ArrivalDate > @TodaysDate) t1
where rn = 1
order by ArrivalDate

Open in new window

0
 

Author Closing Comment

by:cdemott33
ID: 34197496
thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now