#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0

    assist'ce with 'ORDER BY' clause


    how can I list the various PERFORMERS which have the same CONCERTDATE AND CONCERTVENUE. So pretty much PERFORMERS performing at the same location on the same day.

    SQL> desc futureconcerts;
    Name Null? Type
    ----------------------------------------- -------- -------------
    CONCERTID NOT NULL NUMBER
    CONCERTVENUE VARCHAR2(20)
    CONCERTDATE DATE
    PERFORMERS VARCHAR2(3)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    select concertdate, concertvenue, performers
    from futureconcerts
    order by 1,2;

    This is so damn easy that I'm sure I missed something in your question...
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    That what I did too but is not correct. Lemme see if i can explain it better.

    I need to pull out performers who are going to perform at the same venue on any particular/given date. so date and venue's in common of performers...and to only list exactly that.

    Table data:

    CONCERTVENUE CONCERTDATE PERFID
    -------------------- --------------- -------
    va2 23/NOV/03 p25
    va21 22/NOV/03 p25
    va21 22/NOV/03 p27
    va21 22/NOV/03 p26
    va22 22/NOV/03 p10
    va24 24/NOV/03 p25
    va29 25/NOV/03 p25
    va30 28/NOV/03 p35
    va30 28/NOV/03 p35
    va30 28/NOV/03 p45
    va30 28/NOV/03 p46

    -------------------------------------------------------------------

    Desired Outcome:
    va21 22/NOV/03 p25
    va21 22/NOV/03 p27
    va21 22/NOV/03 p26
    va30 28/NOV/03 p35
    va30 28/NOV/03 p35
    va30 28/NOV/03 p45
    va30 28/NOV/03 p46
    Last edited by ratumeli; November 21st, 2003 at 06:49 PM.
  6. #4
  7. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    My guess is:

    SELECT CONCERTVENUE, CONCERTDATE, PERFORMERS
    FROM FUTURECONCERTS, (select CONCERTVENUE,count(*)
    from FUTURECONCERTS
    group by CONCERTVENUE
    having count(*)>1) a
    WHERE CONCERTVENUE = a.CONCERTVENUE
    /
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    Thanks for the effort Shafique but no go =(
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    I knew I missed something in the original post.

    Nice solution shafique!

    @ratumeli: works for me, although I had to supply a table alias for the futureconcerts table in the FROM clause and qualify the columns in the select list (but I tested it with Postgres, not Oracle, but that shouldn't matter)
    Code:
    SELECT f1.concertvenue,
           f1.concertdate,
           f1.performers
    FROM futureconcerts f1,
         (SELECT concertvenue,
                 COUNT(*) 
          FROM futureconcerts
          GROUP BY concertvenue
          HAVING COUNT (*) > 1
          ) a
    WHERE f1.concertvenue = a.concertvenue
    order by 2
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    All I'd like to say Shammat is BULLSEYE !

    Gave me exactly what I required.

    Thanks for your time and effort Shammat and you too Shafique, i tried to mod your code to my liking but I must have wondered off somewhere.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    The credit goes to shafique. It's basically the same statement!

IMN logo majestic logo threadwatch logo seochat tools logo