Very Computer

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

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

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

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

top and distinct keyword did not work together