Home » SQL & PL/SQL » SQL & PL/SQL » SQL
SQL [message #252246] Wed, 18 July 2007 05:34 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Hi All,


I need return 5 row for each records even not 5 entries in table.

Ex-
Input

Custid Name
1 Arvind
2 Mohan
3 Arvind
4 Arvind
5 Arvind
6 Arvind

OUTPUT
NAME
Mohan
NULL
NULL
NULL
NULL
Arvind
Arvind
Arvind
Arvind
Arvind

Thanks & Regards,
Arvind
Re: SQL [message #252260 is a reply to message #252246] Wed, 18 July 2007 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with 
  data as (select distinct name, count(*) over(partition by name) nb from mytable),
  lines as (select level line from dual connect by level <=5)
select decode(sign(line-nb),1,null,name) name
from data, lines
/

Regards
Michel

[Updated on: Wed, 18 July 2007 07:31]

Report message to a moderator

Re: SQL [message #252265 is a reply to message #252260] Wed, 18 July 2007 07:31 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
not execute properly it show error invalid SQL pleae help

for example table name is EMP


Re: SQL [message #252267 is a reply to message #252265] Wed, 18 July 2007 07:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A slightly modified version of Michel's query:
SQL> set NULL '<NULL>'
SQL> WITH yourtable AS
  2  (
  3  SELECT 1 custid, 'Arvind' custname FROM dual UNION ALL
  4  SELECT 2 custid, 'Mohan'  custname FROM dual UNION ALL
  5  SELECT 3 custid, 'Arvind' custname FROM dual UNION ALL
  6  SELECT 4 custid, 'Arvind' custname FROM dual UNION ALL
  7  SELECT 5 custid, 'Arvind' custname FROM dual UNION ALL
  8  SELECT 6 custid, 'Arvind' custname FROM dual
  9  )
 10  SELECT DECODE(SIGN(line-nb),1,NULL,custname) cname
 11  FROM  ( SELECT custname
 12               , count(*) nb
 13          FROM   yourtable
 14          GROUP BY custname
 15        ) data
 16     ,  ( SELECT level line
 17          FROM   dual
 18          CONNECT BY LEVEL <=5
 19        ) lines
 20  ORDER BY custname, cname
 21  /

CNAME
------
Arvind
Arvind
Arvind
Arvind
Arvind
Mohan
<NULL>
<NULL>
<NULL>
<NULL>

10 rows selected.

SQL> set NULL ''
Michel, analytic functions + DISTINCT? Wouldn't a group function be better? I didn't test it, but wonder...

MHE
Re: SQL [message #252269 is a reply to message #252267] Wed, 18 July 2007 07:41 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
records must be dyanamic not hard code like (arvind and mohan), so plz provide other option
Re: SQL [message #252274 is a reply to message #252265] Wed, 18 July 2007 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case with create table and insert statements and I will test my query.

Regards
Michel
Re: SQL [message #252275 is a reply to message #252267] Wed, 18 July 2007 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten,

You're right.
I wrote it on the fly without thinking too much.
But of course every time you have "distinct + analytics" this means that "group" have to be used.

Regards
Michel
Re: SQL [message #252279 is a reply to message #252269] Wed, 18 July 2007 08:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
kr_arvind wrote on Wed, 18 July 2007 14:41
records must be dyanamic not hard code like (arvind and mohan), so plz provide other option

the "WITH clause" is to create a test with your data. YOU didn't provide a proper test case, so I was forced to do so. It has nothing to do with hard coded values. If you look at the 'select part' you get only this:
SELECT DECODE(SIGN(line-nb),1,NULL,custname) cname
FROM  ( SELECT custname
             , count(*) nb
        FROM   yourtable
        GROUP BY custname
      ) data
   ,  ( SELECT level line
        FROM   dual
        CONNECT BY LEVEL <=5
      ) lines
ORDER BY custname, cname
/
Try it with your table and if you have problems with it, come back with a proper test case.

MHE

[Updated on: Wed, 18 July 2007 08:25]

Report message to a moderator

Re: SQL [message #252290 is a reply to message #252246] Wed, 18 July 2007 08:53 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Congratulations on another excellent title for you question. Very informative.
Previous Topic: acquired nowait
Next Topic: pl/sql
Goto Forum:
  


Current Time: Thu Dec 08 08:13:38 CST 2016

Total time taken to generate the page: 0.15900 seconds