CODEWITHSUNDEEP
sqlsql-servert-sqlgreatest-n-per-group
NoviceToDotNet
NoviceToDotNet

Reputation: 10805

What is wrong with this query? I am not getting correct results

What is wrong with this query, please let me know. From the inner query I want to select top 1 group by on the basis of EventID.

Please suggest what to do. 

SELECT TOP 1 * FROM
    (
    SELECT E.EventID, ETD.EventName, ED.EventDate FROM 
    [3rdi_EventDates] AS ED inner join [3rdi_Events] as E on ED.EventID=E.EventID 
    inner join [3rdi_EventTypeDetails] as ETD on E.EventTypeId=ETD.EventTypeId
    WHERE E.EventID in
    (
    select EventId from [3rdi_Events] WHERE
    EventID IN (select distinct EventId from [3rdi_EventDates] where EventDate Between '2/9/2011' and '3/11/2012')
    ) ORDER BY ETD.EventName, ED.EventDate  
    ) AS temp Group by EventID

Upvotes: 0

Views: 424

Answers (2)

Bhavik Goyal
Bhavik Goyal

Reputation: 2796

Use this.

SELECT TOP 1 * FROM  (
    SELECT E.EventID, ETD.EventName, ED.EventDate FROM 
    [3rdi_EventDates] AS ED inner join [3rdi_Events] as E on ED.EventID=E.EventID 
    inner join [3rdi_EventTypeDetails] as ETD on E.EventTypeId=ETD.EventTypeId
    WHERE ED.EventDate > Convert(Datetime,'2/9/2011') and ED.EventDate < Convert(Datetime,'3/11/2012')
    ORDER BY ETD.EventName, ED.EventDate)

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

You want one record per EventID, in which case, use Row_Number() and partitioning.

Your two levels of subquery are also not needed, the inner joins involve both tables in the subqueries so you can filter directly in the main query.

The last point to make is that date literals are best written using YYYYMMDD to be robust against any regional or dateformat settings.

SELECT EventID, EventName, EventDate
FROM (
    SELECT E.EventID, ETD.EventName, ED.EventDate,
        ROW_NUMBER() over (
            partition by E.EventID 
            ORDER BY ETD.EventName, ED.EventDate) RowNum
    FROM [3rdi_EventDates] AS ED
    inner join [3rdi_Events] as E on ED.EventID=E.EventID 
    inner join [3rdi_EventTypeDetails] as ETD on E.EventTypeId=ETD.EventTypeId
    where ED.EventDate Between '20110209' and '20120311'
    ) AS temp
WHERE RowNum = 1

Upvotes: 1

Related Questions