| RAJNISH |
Posted: Sep 01, 2008 07:08:27 AM |
Total Post: 92
Joined: Jan, 2007
|
Hi ALl,
select deptno,count(*) from dept
group by deptno
above showing me output like
deptno count
10 1
20 1
30 1
40 1
but i want show only first three recds i.e 10,20,30 WITHOUT USING WHERE CLAUSE IN THIS QUERY?
--rajnish |
|
|
 vino |
| Posted: Sep 01, 2008 07:20:44 AM |  |
|
Total Post: 357
Joined: Aug, 2008
|
using LIMIT function will satisfy your need..
|
|
|
|
|
| RAJNISH |
| Posted: Sep 01, 2008 07:22:11 AM | |
|
Total Post: 92
Joined: Jan, 2007
|
Hi Vino,
please give me your reply with some example i am very happy to see your instant reply.
|
|
|
|
|
| RAJNISH |
| Posted: Sep 01, 2008 07:30:20 AM | |
|
Total Post: 92
Joined: Jan, 2007
|
HI,
LIMIT function is not working with this query.
what should i do?
|
|
|
|
|
| vino |
| Posted: Sep 01, 2008 07:33:47 AM | |
|
Total Post: 357
Joined: Aug, 2008
|
Here is an example,
SELECT * FROM emp LIMIT 0,3;
The above query will display only first three records in that table..
Hope this helps.
|
|
|
|
|
| RAJNISH |
| Posted: Sep 01, 2008 07:39:19 AM | |
|
Total Post: 92
Joined: Jan, 2007
|
hi vino but i want use limit in above query but after goroup clause its not working.
|
|
|
|
|
| vino |
| Posted: Sep 01, 2008 07:45:27 AM | |
|
Total Post: 357
Joined: Aug, 2008
|
Try this,
>select ... from tablename group by deptno desc limit 0,3;
|
|
|
|
|
| RAJNISH |
| Posted: Sep 01, 2008 07:48:08 AM | |
|
Total Post: 92
Joined: Jan, 2007
|
Hi Vino,
i am using with my query see below.
select deptno,count(*) from dept
group by deptno
desc limit 0,3
ITS NOT WORKING DEAR?
|
|
|
|
|
| vino |
| Posted: Sep 01, 2008 08:05:38 AM | |
|
Total Post: 357
Joined: Aug, 2008
|
What the error you are getting, paste it..
|
|
|
|
|
| vino |
| Posted: Sep 01, 2008 09:34:37 AM | |
|
Total Post: 357
Joined: Aug, 2008
|
Try this,
select deptno,count(*) from dept order by deptno desc limit 3;
bcoz, Order by is recommended that if we gonna use limit function.
|
|
|
|
|
| vino |
| Posted: Sep 01, 2008 09:35:00 AM | |
|
Total Post: 357
Joined: Aug, 2008
|
Try this,
select deptno,count(*) from dept order by deptno desc limit 3;
bcoz, Order by is recommended that if we gonna use limit function.
|
|
|
|
|
| RAJNISH |
| Posted: Sep 01, 2008 10:31:47 AM | |
|
Total Post: 92
Joined: Jan, 2007
|
HI VINO,
STILL ITS NOT WORKING DEAR YOU JUST TRY THIS QRY FROM YOUR SIDE.
select deptno,count(*) from dept
group by deptno
order by deptno desc limit 3
|
|
|
|
|
| shake |
| Posted: Sep 02, 2008 01:34:09 AM | |
|
Total Post: 85
Joined: Jun, 2008
|
LIMIT clause works fine in MySQL and PostgreSQL. This is similar to TOP clause in Microsoft SQL Server. OFFSET keyword is used in PostgreSQL to provide the offset(no. of rows) value.
But in Oracle, we use ROWNUM along with WHERE clause to retrieve the records of a particular range.
SELECT * FROM emp WHERE ROWNUM <= 3; -> to get the first 3 records.
SELECT * FROM emp WHERE ROWNUM <= 8
MINUS
SELECT * FROM emp WHERE ROWNUM < 3; -> to get 3-8 records.
|
|
|
|
|
| RAJNISH |
| Posted: Sep 02, 2008 05:11:42 AM | |
|
Total Post: 92
Joined: Jan, 2007
|
Hi Vino / Shake
finally i have made it...
SELECT * FROM (SELECT DEPTno, COUNT(*) cnt FROM DEPT
GROUP BY deptno ORDER BY cnt DESC)
WHERE ROWNUM < 4
it will show you first 3 recds.
|
|
|
|
|
| vino |
| Posted: Sep 03, 2008 08:14:16 AM | |
|
Total Post: 357
Joined: Aug, 2008
|
Hi Rajinish,
Thanks for your reply, I too come across rumnum just now.. Would like to know if you have any queries..
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
