top and distinct keyword did not work together

top and distinct keyword did not work together

Post by raj » Wed, 19 Jun 2002 10:53:18



Hi,

I have to fetch the first record from a search query as follows.

"Select distinct(stuId), stuName from student where age > 10"

Some times the query returns a large volume of data and load the memory. So
I can't use front end.

If I put top in front of distinct then the query returns error.

How to get top record in a query which uses distinct keyword.

TIA

regards
Raj

 
 
 

top and distinct keyword did not work together

Post by Cienki Bole » Wed, 19 Jun 2002 12:49:07


Try the following:
select top 1 * from (Select distinct(stuId), stuName from student where age
Quote:> 10) x


 
 
 

top and distinct keyword did not work together

Post by raj » Wed, 19 Jun 2002 13:17:33


Thanks Cienki Bolek,

It is working, Thanks for your help.

But one more thing. I am having order by in the end. It is giving following
error.

"The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified."

what to do.


Quote:> Try the following:
> select top 1 * from (Select distinct(stuId), stuName from student where
age
> > 10) x

 
 
 

top and distinct keyword did not work together

Post by Andrew J. Kell » Wed, 19 Jun 2002 14:25:14


Try TOP 1 SELECT StuID, StuName
    FROM Student
        GROUP BY StuID,StuName
            ORDER BY StuID,StuName
--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


> Thanks Cienki Bolek,

> It is working, Thanks for your help.

> But one more thing. I am having order by in the end. It is giving
following
> error.

> "The ORDER BY clause is invalid in views, inline functions, derived
tables,
> and subqueries, unless TOP is also specified."

> what to do.



> > Try the following:
> > select top 1 * from (Select distinct(stuId), stuName from student where
> age
> > > 10) x

 
 
 

1. DISTINCT keyword not working.

I need help getting the DISTINCT keyword to work.

I'm trying to return records with a unique subID value.  I set the DISTINCT
keyword before the subID value but it still returns multiple subID's.  Any
help would be great.  Thanks.

Here is my stored procedure:


As



 select
 DISTINCT S.subID,
 S.forumID,
 S.topicID,
 S.memberID,
 S.Active,
 S.goInbox,
 S.goEmail,
 T.Topic_ID,
 M.M_Name,
 F.F_Name,
 SUBSTRING(T.T_Subject,0,50) as T_Subject,
 T.T_Originator,
 T.T_Date,
 T.T_Last_Post
 from subscriptions S
 join Topics T on (T.Topic_ID = S.topicID or T.T_Originator = S.MemberID or
T.Forum_ID = S.forumID)
 left outer join Members M on Member_ID = T.T_Originator
 left outer join Forum F on F.Forum_ID = T.forum_ID

 ORDER BY T.T_Last_Post DESC,T.T_Date DESC
 return

2. PL/SQL Question

3. Not happy together: COUNT(*) and COUNT(DISTINCT ...)

4. Stream.Write generates error

5. MDX: Linkmember and Sum function not working together

6. Weird Tabbing Seq and ACCEPT key

7. Trouble with SPROC and VIEW not working together.

8. TestBase 2.0 - Free DEMO

9. Q:Transactions and Tbatch components not working together ?

10. HELP: ADO, RDS, and Sybase not working together

11. keyword "NAME" not reserved keyword

12. Why is this distinct not working

13. urgent : query distinct not work