help in writing sql select statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: help in writing sql select statement

  1. #1
    Join Date
    Feb 2003
    Location
    London
    Posts
    170

    help in writing sql select statement

    Hi,

    Can someone please help me write this sql statement?

    SELECT DISTINCT sss.user_name, 1,
    (SELECT COUNT (user_name)
    FROM saved_searches
    WHERE sss.user_name = 'TESTADM' AND ROWNUM = 1),[over here I need sequential nos 1,2,3 for each row]
    FROM saved_searches sss
    ORDER BY (SELECT COUNT (user_name)
    FROM saved_searches
    WHERE sss.user_name = 'Phil Watson' AND ROWNUM =1) DESC,
    sss.user_name;

    This query shouls give the result as follows:
    USER_NAME|1|SELECT COUNT (user_name)|Column that I want to add
    TEST1 1 1 1
    TEST2 1 0 2
    TEST3 1 0 3


    Thanks a lot for your help. I just need to know how to add the last column .

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483
    You could try something like this:

    SELECT * FROM (
    SELECT DISTINCT sss.user_name, 1,
    (SELECT COUNT (user_name)
    FROM saved_searches
    WHERE sss.user_name = 'TESTADM' AND ROWNUM = 1),
    RANK() OVER (ORDER BY sss.user_name, rowid) SEQ
    FROM saved_searches sss
    ORDER BY (SELECT COUNT (user_name)
    FROM saved_searches
    WHERE sss.user_name = 'Phil Watson' AND ROWNUM =1) DESC,sss.user_name)
    ;

  3. #3
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    Hi and thanks for the reply.

    I'm not getting the resultset I need. It's looping around the same thing 3 times.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483
    Maybe you have to re-think the statement because
    (SELECT COUNT (user_name)
    FROM saved_searches
    WHERE sss.user_name = 'TESTADM' AND ROWNUM = 1)
    will allways produce either 0 or 1!!!


  5. #5
    Join Date
    May 2003
    Location
    Chennai, India
    Posts
    5
    Hi,

    U could probably try this qry below.

    select res.*,rownum
    from
    (
    SELECT DISTINCT sss.user_name, 1,
    (SELECT COUNT (user_name)
    FROM saved_searches
    WHERE sss.user_name = 'TESTADM' AND ROWNUM = 1)
    --[over here I need sequential nos 1,2,3 for each row]
    FROM saved_searches sss
    ORDER BY (SELECT COUNT (user_name)
    FROM saved_searches
    WHERE sss.user_name = 'Phil Watson' AND ROWNUM =1) DESC,
    sss.user_name
    ) res

    Output of this would be (assuming 3 users TEST1, TEST2, TEST3) :

    Username 1 Cnt Rownum
    TEST1 1 1 1
    TEST2 1 0 2
    TEST3 1 0 3

    Reg,
    Aparna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width