Distinct Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distinct Help

Author

Topic

scripter
Starting Member

49 Posts

Posted - 2006-12-07 : 17:31:47

Ok as you can see here this SQL is not actually for MSSQL but instead for MySQL. Now before I get the third Degree on asking questions about MYSQL if someone can provide me the answer in T-SQL I can always convert it fairly easy.

The below script needs to pull one picurl and username per user and only be a total of 5 returned. but as you can see from the results below the script it is not working can someone provide me with details?

SELECT DISTINCT p.picurl, m.username
FROM pictures p
INNER JOIN members m ON p.userp = m.id
WHERE p.pictype = 'P'
ORDER BY p.userp
LIMIT 5

picurl username
scott_9162006536.JPG scripter
me1_8242005457.jpg marymiller
me2_8242005457.jpg marymiller
me4_8242005457.jpg marymiller
n88000485_17049_61720061355.jpg k_lott

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 17:39:51

[code]SELECT DISTINCT TOP 5
p.picurl,
m.username
FROM pictures p
INNER JOIN members m ON p.userp = m.id
WHERE p.pictype = 'P'
ORDER BY p.userp[/code]

Peter Larsson
Helsingborg, Sweden

scripter
Starting Member

49 Posts

Posted - 2006-12-07 : 17:48:05

Ok the only thing you changed there is LIMIT 5 to TOP 5 in MySQL Limit 5 is their version of TOP 5 so that has nothing to do with why it is not working with returning only one username and picurl per user but on the other hand I did this and even though it is not fully tested It is working as of now

SELECT p.picurl, m.username
FROM pictures p
INNER JOIN members m ON p.userp = m.id
WHERE p.pictype = 'P'
AND p.userp = m.id
GROUP BY m.username
ORDER BY p.userp
LIMIT 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 17:51:46

That is because any username can have many picurl's!
And the combination of username and picurl is distinct.

SELECT		MAX(p.picurl) picurl,
		m.username
FROM		pictures p
INNER JOIN	members m ON p.userp = m.id
WHERE		p.pictype = 'P'
GROUP BY	m.username
ORDER BY	p.userp
LIMIT 5

Peter Larsson
Helsingborg, Sweden

scripter
Starting Member

49 Posts

Posted - 2006-12-07 : 18:00:58

Ok that seems to work but can you explain something to me because I am a bit lost with this.

What is it that MAX is doing in this script?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 18:17:02

Read about aggregate function in Books Online.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 18:18:35