Distinct from Multiple columns

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 2005 Forums

Transact-SQL (2005)

Distinct from Multiple columns

Author

Topic

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-16 : 06:05:38

I hope you can help, I am trying to return rows joining 2 tables in a Stored Procedure, but I need unique distinct company names.

** First, I am creating a temp table @Result


declare @Result TABLE 
(listname varchar(200), pmid uniqueidentifier, company varchar(200))

Then I am selecting and inserting a 100 of the last added records into it.


INSERT INTO @Result (listname,  pmid, company) 
	SELECT TOP (100) List.ListName, List.pmid, Company.CompanyName
	FROM   List INNER JOIN
		Company ON List.CompanyId = Company.CompanyId
	WHERE list.notes like '%UK%'
	ORDER BY List.DateAdded DESC

Finally I am choosing to return 5 of them randomly.


SELECT top (5) listname, pmid, company FROM @Result order by newid();

What I would like is to return 5 rows each row from a unqiue company. So I get 5 rows from unique companies.

Can you assist me or point me in the right direction.

Many thanks

Peter

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-16 : 06:30:07

quote:
Originally posted by pOrmsby

I hope you can help, I am trying to return rows joining 2 tables in a Stored Procedure, but I need unique distinct company names.

** First, I am creating a temp table @Result

declare @Result TABLE
(listname varchar(200), pmid uniqueidentifier, company varchar(200))

Then I am selecting and inserting a 100 of the last added records into it.

INSERT INTO @Result (listname, pmid, company)
SELECT TOP (100) List.ListName, List.pmid, Company.CompanyName
FROM List INNER JOIN
Company ON List.CompanyId = Company.CompanyId
WHERE list.notes like '%UK%'
ORDER BY List.DateAdded DESC

Finally I am choosing to return 5 of them randomly.

SELECT top (5) listname, pmid, company FROM @Result order by newid();

What I would like is to return 5 rows each row from a unqiue company. So I get 5 rows from unique companies.

Can you assist me or point me in the right direction.

Many thanks

Peter

Does this work ?

SELECT 
DISTINCT TOP 5 
	Y.COMPANY , 
	(SELECT TOP 1 PMID,COMPANY FROM @RESULT X WHERE X.COMPANY=Y.COMPANY)
FROM @RESULT Y
order by newid();

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-16 : 06:42:06

hi, many many thanks for your quick reply.

I am now getting the following error:

Msg 116, Level 16, State 1, Line 12
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 145, Level 15, State 1, Line 12
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

(line 12 begins your select statement)

I also need the listname to be returned in the query.

Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 06:57:36

[code]
SELECT DISTINCT TOP 5
Y.COMPANY, X.PMID,X.listname
FROM @RESULT Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT X WHERE X.COMPANY=Y.COMPANY)X
order by newid();
[/code]

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-16 : 11:50:21

quote:

Originally posted by visakh16


SELECT DISTINCT TOP 5 
	Y.COMPANY, X.PMID,X.listname 
FROM @RESULT Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT X WHERE X.COMPANY=Y.COMPANY)X
order by newid();

Thanks for your reply, your code produces error:

Msg 145, Level 15, State 1, Line 12
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 11:56:33

[code]
SELECT Y.COMPANY, X.PMID,X.listname
FROM (SELECT DISTINCT TOP 5
COMPANY FROM @RESULT) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT WHERE COMPANY=Y.COMPANY)X
order by newid();
[/code]

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-16 : 12:21:23

quote:

Originally posted by visakh16


SELECT Y.COMPANY, X.PMID,X.listname 
FROM    (SELECT DISTINCT TOP 5 
	COMPANY FROM @RESULT) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT  WHERE COMPANY=Y.COMPANY)X
order by newid();

You have cracked it - thank you.

Except I forgot 1 thing, I wanted to select randomly from the 100, the code above selects the first 5 companies every time.

Sorry, my mistake, I should have explained this further.

Can you help again?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 12:55:29

[code]SELECT Y.COMPANY, X.PMID,X.listname
FROM (SELECT DISTINCT TOP 5
COMPANY FROM @RESULT
order by newid()) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT WHERE COMPANY=Y.COMPANY)X
[/code]

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-16 : 17:45:39

quote:

Originally posted by visakh16

SELECT Y.COMPANY, X.PMID,X.listname 
FROM    (SELECT DISTINCT TOP 5 
	COMPANY FROM @RESULT
        order by newid()) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT  WHERE COMPANY=Y.COMPANY)X

thanks again for helping

- however getting error on your last code post


Msg 145, Level 15, State 1, Line 14
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 23:38:20

[code]
SELECT Y.COMPANY, X.PMID,X.listname
FROM (SELECT DISTINCT TOP 5
COMPANY FROM @RESULT
order by newid(),COMPANY) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT WHERE COMPANY=Y.COMPANY)X
[/code]

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-17 : 09:12:21

Thanks. However still getting error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Full code looks like this:


declare @Result TABLE (listname varchar(200), pmid uniqueidentifier, company varchar(200))

INSERT INTO @Result (listname,  pmid, company) 
	SELECT TOP (200) List.ListName, List.pmid, Company.CompanyName	
	FROM   List INNER JOIN
		Company ON List.CompanyId = Company.CompanyId
	WHERE list.notes like '%UK%' and list.liststatuscode is null
	ORDER BY List.DateAdded DESC


SELECT Y.COMPANY, X.PMID,X.listname 
FROM    (SELECT DISTINCT TOP 5 
	COMPANY FROM @RESULT
        order by newid(),COMPANY) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT  WHERE COMPANY=Y.COMPANY)X

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 10:39:10

[code]SELECT Y.COMPANY, X.PMID,X.listname
FROM (SELECT DISTINCT TOP 5
newid(),COMPANY FROM @RESULT
order by newid(),COMPANY) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT WHERE COMPANY=Y.COMPANY)X
[/code]

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-17 : 11:00:33

error is now:

No column was specified for column 1 of 'Y'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 11:04:16

[code]SELECT Y.COMPANY, X.PMID,X.listname
FROM (SELECT DISTINCT TOP 5
newid() AS ID,COMPANY FROM @RESULT
order by newid(),COMPANY) Y
CROSS APPLY (SELECT TOP 1 PMID,listname FROM @RESULT WHERE COMPANY=Y.COMPANY)X
[/code]

pOrmsby
Starting Member

12 Posts

Posted - 2008-12-17 : 11:16:11

You star. I'm so impressed.

Not only for solving it, but sticking with me.

Thank you so much for your help

Its a shame we in the UK cannot play cricket as well as you can code SQL!!

Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 11:22:50

Cheers

shijobaby
Starting Member

44 Posts

Posted - 2009-08-19 : 05:39:29

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources