New to SQL - Query Help

Transact-SQL
1

I am new to SQL and need to get certain data out of a database and i am struggling.

I have a table with a field called MAC (random digits) and a Location field (int) among other things. I need to get all MAC that appear at multiple locations within a certain period of time.

Can someone help, if you need anymore information please let me know.

2 
SELECT
	MAC 
FROM 
	YourTable
WHERE
	-- this is your date filter ("within a certain period of time")
	-- This filters for all of July 2015.
	YourDateColumn >= '20150701'
	AND YourDateColumn < '20150801'
GROUP BY
	MAC
HAVING
	-- this is filtering for multiple locations.
	COUNT(DISTINCT LocationID) > 1;
1 Like
3

Thanks JamesK, will get my head round it soon.

If i want to see other details for each MAC (Time, LocationID, Date) do i just add these to the select statement?

Thanks

4

I would now like to remove the date criteria and just list the 10 MACS that have been detected most recently at multiple locations, and also count the number of different locations a specific MAC has been detected, is this all possible within the same query?

Thanks in advance.

5 
;WITH cte AS
(
	SELECT
		MAC, 
		COUNT(DISTINCT LocationID) AS LocationCount, 
		MAX(YourDateColumn) AS LatestDetection
	FROM 
		YourTable
	GROUP BY
		MAC
	HAVING
		COUNT(DISTINCT LocationID) > 1
)
SELECT TOP 10 * FROM cte 
ORDER BY LatestDetection DESC;
6

You can list additional columns in the SELECT clause, but if you do, you will need to include them in the group by clause as well. But that may not be exactly what you want. If you include Time column for example, which value of time column do you want, given that there is more than one row with the same value for MAC?