D
David Lozzi
Howdy,
Below is my SELECT statement. The returned list is suppose to return the TOP
12 when sorted by MIN(dtDate). It sorts the records correctly and only
displays 12 records, but to meet the 12 record criteria, it removes a record
that should be #6. If I change the 12 to a 13, that missing record appears,
however now its 13 and not 12. The UNION is to add a blank record at the end
so i can capture the last record in my DataList to display something
special, which works great. If I remove the UNION statement and keep it at
TOP 12, it works properly, it selects the top 12. Is there something I'm
missing on this UNION???
Thanks!!
(SELECT TOP 12
dbo.tblEvents.dtDisplayUntil, dbo.tblEvents.ID, MIN(dbo.tblDates.dtDate) as
dtDate, dbo.tblEvents.strTitle,
strDescription AS Description,
dbo.tblEvents.strLocation, dbo.tblEvents.strCost
FROM dbo.tblEvents LEFT OUTER JOIN
dbo.tblDates ON dbo.tblEvents.ID = dbo.tblDates.CID
GROUP BY dbo.tblEvents.ID, dbo.tblEvents.dtDisplayUntil,
dbo.tblEvents.strTitle, dbo.tblEvents.strDescription,
dbo.tblEvents.strLocation, dbo.tblEvents.strCost
HAVING (dbo.tblEvents.dtDisplayUntil >= GETDATE()))
UNION
SELECT GETDATE() + 360 as dtDisplayUntil, 0 as ID, GETDATE() + 360 as
dtDate, '' as strTitle, '' as description, '' as strLocation, '' as strcost
ORDER BY MIN(dbo.tblDates.dtDate)
Below is my SELECT statement. The returned list is suppose to return the TOP
12 when sorted by MIN(dtDate). It sorts the records correctly and only
displays 12 records, but to meet the 12 record criteria, it removes a record
that should be #6. If I change the 12 to a 13, that missing record appears,
however now its 13 and not 12. The UNION is to add a blank record at the end
so i can capture the last record in my DataList to display something
special, which works great. If I remove the UNION statement and keep it at
TOP 12, it works properly, it selects the top 12. Is there something I'm
missing on this UNION???
Thanks!!
(SELECT TOP 12
dbo.tblEvents.dtDisplayUntil, dbo.tblEvents.ID, MIN(dbo.tblDates.dtDate) as
dtDate, dbo.tblEvents.strTitle,
strDescription AS Description,
dbo.tblEvents.strLocation, dbo.tblEvents.strCost
FROM dbo.tblEvents LEFT OUTER JOIN
dbo.tblDates ON dbo.tblEvents.ID = dbo.tblDates.CID
GROUP BY dbo.tblEvents.ID, dbo.tblEvents.dtDisplayUntil,
dbo.tblEvents.strTitle, dbo.tblEvents.strDescription,
dbo.tblEvents.strLocation, dbo.tblEvents.strCost
HAVING (dbo.tblEvents.dtDisplayUntil >= GETDATE()))
UNION
SELECT GETDATE() + 360 as dtDisplayUntil, 0 as ID, GETDATE() + 360 as
dtDate, '' as strTitle, '' as description, '' as strLocation, '' as strcost
ORDER BY MIN(dbo.tblDates.dtDate)