asked on

Help with SELECT DISTINCT

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?

Ephraim Wangoya

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

Ephraim Wangoya

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

cdemott33

ASKER

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.

DFW_Ed

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

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

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

cdemott33

ASKER

thanks