SQL Server select top 100 by group?

The following code gets me the top 100 tours and reservations

How can I make that into one select so that I get 200 records... 100 or each "group"

SELECT   TOP 100 *
FROM     ewAppointments
WHERE    AppointmentTitle LIKE  'Reservation%'
ORDER BY AppointmentID DESC

SELECT   TOP 100 *
FROM     ewAppointments
WHERE    AppointmentTitle LIKE  'Tour%'
ORDER BY AppointmentID DESC;

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
For just two groups, easiest way is probably to just UNION ALL the two results:

SELECT *
FROM (
    SELECT   TOP 100 *
    FROM     ewAppointments
    WHERE    AppointmentTitle LIKE  'Reservation%'
    ORDER BY AppointmentID DESC
) AS result1
UNION ALL
SELECT *
FROM (
    SELECT   TOP 100 *
    FROM     ewAppointments
    WHERE    AppointmentTitle LIKE  'Tour%'
    ORDER BY AppointmentID DESC
) AS result2
0
 
Larry Bristersr. DeveloperAuthor Commented:
Hey Scott,
 To get what I wanted the way I wanted to see it I modified yours to what I have below.

Do you see any fundamental issues with this?

SELECT   source.*
FROM     (   SELECT   TOP 100 a.*
             FROM     ewAppointments a WITH (NOLOCK)
			 JOIN SalesSites s WITH (NOLOCK) ON a.SalesSiteID = s.SalesSiteID
             WHERE    AppointmentTitle LIKE 'Reservation%'
                      AND AppointmentDate BETWEEN '2018-01-01' AND '2018-01-31'
					  AND s.BUsinessID = 104302
             ORDER BY AppointmentID DESC
             UNION ALL
             SELECT   TOP 100 a.*
             FROM     ewAppointments a WITH (NOLOCK)
			 JOIN SalesSites s WITH (NOLOCK) ON a.SalesSiteID = s.SalesSiteID
             WHERE    AppointmentTitle LIKE 'Tour%'
                      AND AppointmentDate BETWEEN '2018-01-01' AND '2018-01-31'
					  AND s.BUsinessID = 104302
             ORDER BY AppointmentID DESC ) source
ORDER BY source.AppointmentDate DESC;

Open in new window

0
 
Scott PletcherSenior DBACommented:
Nah, that should work fine.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
 
Scott PletcherSenior DBACommented:
As a side note, I strongly oppose using BETWEEN for dates or times, so I would prefer this instead:

AND AppointmentDate >= '20180101' AND AppointmentDate < '20180201'

If a time-of-day is somehow added to that column later, even if it goes to datetime2 type, the >= with < method continues to work.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.