Anyone good at MS SQL queries?

Communities ColdBox HMVC
1

I have an issue that I am unable to find a solution for, I know it is possible just that I can’t find it. And I believe it will have to be a sub select.

The original query is

SELECT DISTINCT abResourceType.abResourceTypeId, abResourceType.Name

FROM abResourceType INNER JOIN

abResource ON abResourceType.abResourceTypeId = abResource.abResourceTypeId

WHERE (abResourceType.CompanyCode = ‘abd’)

ORDER BY abResourceType.Name

Now what I want and in need of here is to select the top 3 random, I know that I can substitute the order by with newid() and it will make it random, but how do I then get the top 3 without having to write a second query to do this?

So this works getting the top 3 random, but it is not unique

SELECT TOP (3) abResourceType.abResourceTypeId, abResourceType.Name

FROM abResourceType INNER JOIN

abResource ON abResourceType.abResourceTypeId = abResource.abResourceTypeId

WHERE (abResourceType.CompanyCode = ‘abd’)

ORDER BY NEWID()

Anyone have any ideas what I have to do… And yes this is MS SQL server…

Regards,

Andrew Scott

http://www.andyscott.id.au/

2

Not exactly a subselect; do a CTE (common table expression)

with cte as
(
  SELECT DISTINCT abResourceType.abResourceTypeId, abResourceType.Name
  FROM abResourceType INNER JOIN
                      abResource ON abResourceType.abResourceTypeId = abResource.abResourceTypeId
  WHERE (abResourceType.CompanyCode = 'and')
)
select top 3 * from cte order by newId()

3

Oh is that what they are, I had seen references to them and thought it might have been a storedproc.