SQL Query (maybe simple)

Question

Hi folks,

So i'm having this problem.

I have two tables (Oracle), one is called Destination and the other one Reserve. Reserve has a foreign key to the id of Destination (Because on reserve has one destination). And reserve tuples means all the reserves that all the users have done. I need a way to check the top 3 most visited Destinations (based on the foreign key in the table Reserve).

How can I do that with SQL in Oracle. I know that I need to search within the Reserve tables for the 3 most repeated Destinations ID's, and later join that with the Destination table to get the details of the top 3 destinations.

Any help is valid. Thank you very much.

SCHEMA:

--------------------------------------------------------
--  File created - martes-septiembre-15-2009   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table DESTINO
--------------------------------------------------------

  CREATE TABLE "S2501A29"."DESTINO" 
   (    "PK_ID_DESTINO" NUMBER(10,0), 
    "FK_COD_UBICACION_GEOGRAFICA" NUMBER(10,0), 
    "NOMBRE" VARCHAR2(10), 
    "FOTO" VARCHAR2(30), 
    "DESCRIPCION" VARCHAR2(50)
   ) ;
--------------------------------------------------------
--  DDL for Table LUGAR_ESTADIA
--------------------------------------------------------

  CREATE TABLE "S2501A29"."LUGAR_ESTADIA" 
   (    "PK_ID_ESTADIA" NUMBER(10,0), 
    "NOMBRE" VARCHAR2(10), 
    "TIPO" VARCHAR2(10), 
    "DESCRIPCION" VARCHAR2(50), 
    "COSTO_SERVICIOS" NUMBER, 
    "DESCRIPCION_ALOJAMIENTO" VARCHAR2(100), 
    "DESCRIPCION_ALIMENTACION" VARCHAR2(100)
   ) ;
--------------------------------------------------------
--  DDL for Table OPCION_TRANSPORTE
--------------------------------------------------------

  CREATE TABLE "S2501A29"."OPCION_TRANSPORTE" 
   (    "PK_ID_VIAJE" NUMBER(10,0), 
    "MEDIO_TRANSPORTE" VARCHAR2(10), 
    "RESPONSABLE" VARCHAR2(10), 
    "CIUDAD_ORIGEN" VARCHAR2(10), 
    "CIUDAD_DESTINO" VARCHAR2(10), 
    "COSTO" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table RESERVA
--------------------------------------------------------

  CREATE TABLE "S2501A29"."RESERVA" 
   (    "PK_ID_RESERVA" NUMBER(10,0), 
    "FK_COD_DESTINO" NUMBER(10,0), 
    "FK_COD_ESTADIA" NUMBER(10,0), 
    "FK_COD_VIAJE" NUMBER(10,0), 
    "TARJETA_CREDITO" VARCHAR2(12), 
    "FECHA_SALIDA" DATE, 
    "FECHA_REGRESO" DATE, 
    "NOMBRE_USUARIO" VARCHAR2(50)
   ) ;
--------------------------------------------------------
--  DDL for Table UBICACION_GEOGRAFICA
--------------------------------------------------------

  CREATE TABLE "S2501A29"."UBICACION_GEOGRAFICA" 
   (    "PK_ID_UBICACION" NUMBER(10,0), 
    "CIUDAD" VARCHAR2(10), 
    "PAIS" VARCHAR2(10), 
    "CONTINENTE" VARCHAR2(10)
   ) ;

Just a note:

RESERVE is Reserva DESTINATION is Destino

Because the DB is in spanish. Thanks!

Answer 1

Maybe I'm missing something, but what about grouping by foreign key and sorting by the resulting values?

Edit: Something like:

select FK_COD_DESTINO, count(*) as qty from RESERVA group by FK_COD_DESTINO order by qty desc limit 3

Answer 2

How about:

WITH CountsByDestination
AS
(
  SELECT FK_COD_DESTINO, 
         COUNT(*) CNT
  FROM Reservations
  GROUP BY DestinationId
),
RankingByDestination
AS
(
  SELECT FK_COD_DESTINO,
         CNT,
         RANK() OVER (ORDER BY CNT) RNK
  FROM CountsByDestination
)
SELECT *
FROM RankingByDestination R
INNER JOIN Destinations D ON D.PK_ID_DESTINO = R.FK_COD_DESTINO
WHERE R.RNK BETWEEN 1 AND 3
ORDER BY R.RNK

This retains ties, so if two or more destinations share the same counts they should appear as part of the result set.

Answer 3

Oracle doesn't support limit. This should work for you.

select A.FK_COD_DESTINO
     , A.COUNT
     , A.RANK
     , B.*
  from (select FK_COD_DESTINO
             , count(*) as COUNT
             , rank() over (order by count(*) desc) as RANK
          from RESERVA
         group by FK_COD_DESTINO) A
  join DESTINO B on B.PK_ID_DESTINO = A.FK_COD_DESTINO
 where A.RANK <= 3

Answer 4

you can try HAVING rownum <=3