How to only return only one record per group/id

[Former Member]

Hi,

Forgive my terminology if I get it wrong as I'm just teaching myself SQL. I have the following query:

SELECT ROOMS.ROOMNUMBER AS 'Room Number',
       ROOMS.ROOMSTATUS AS 'Status of Room',
       FOLIOHD.CHECKIN AS 'Current C/In Date',
       FOLIOHD.CHECKOUT AS 'Current C/Out Date',
       ROOMS.LASTOCCDATE AS 'Last C/Out Date',
       FOLIOHD.HOUSENOTE AS 'Housekeeping Note',
       ROOMBOOK.FROMDATE AS 'Next C/In Date',
       ROOMBOOK.TODATE AS 'Next C/Out Date'

FROM ROOMS
LEFT OUTER JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER
LEFT OUTER JOIN HISTHD ON ROOMS.LASTFOLIO = HISTHD.NUMBER
JOIN ROOMBOOK ON ROOMS.ROOMNUMBER = ROOMBOOK.ROOMNUMBER

WHERE ROOMS.ROOMNUMBER <> '103' AND  ROOMS.ROOMNUMBER <> '110' AND  ROOMS.ROOMNUMBER <> '210' AND  ROOMS.ROOMNUMBER <> '227' AND  ROOMS.ROOMNUMBER <> '228'

which is working except it returns a separate row for each value in ROOMBOOK.FROMDATE, so I get something like this:

Room Number Status of Room  Current C/In Date   Current C/Out Date  Last C/Out Date Housekeeping Note   Next C/In Date  Next C/Out Date
104 O   26/12/12    27/12/12    26/12/12        27/12/12    01/01/13
104 O   26/12/12    27/12/12    26/12/12        02/01/13    04/01/13
104 O   26/12/12    27/12/12    26/12/12        04/01/13    05/01/13
104 O   26/12/12    27/12/12    26/12/12        05/01/13    19/01/13
104 O   26/12/12    27/12/12    26/12/12        19/01/13    06/04/13
104 O   26/12/12    27/12/12    26/12/12        06/04/13    13/04/13
104 O   26/12/12    27/12/12    26/12/12        26/04/13    28/04/13
104 O   26/12/12    27/12/12    26/12/12        19/07/13    20/07/13
104 O   26/12/12    27/12/12    26/12/12        11/08/13    12/08/13
105 V           24/12/12        26/12/12    28/12/12
105 V           24/12/12        28/12/12    29/12/12
105 V           24/12/12        29/12/12    30/12/12
105 V           24/12/12        30/12/12    01/01/13
105 V           24/12/12        02/01/13    04/01/13
105 V           24/12/12        05/01/13    19/01/13
105 V           24/12/12        19/01/13    06/04/13
105 V           24/12/12        06/04/13    13/04/13
105 V           24/12/12        26/04/13    28/04/13
105 V           24/12/12        19/07/13    20/07/13
105 V           24/12/12        11/08/13    12/08/13
106 V           26/12/12        26/12/12    27/12/12
106 V           26/12/12        27/12/12    01/01/13
106 V           26/12/12        02/01/13    04/01/13
106 V           26/12/12        11/01/13    19/01/13
106 V           26/12/12        19/01/13    06/04/13
106 V           26/12/12        06/04/13    13/04/13

I only want it to return the earliest value for ROOMBOOK.FROMDATE (ie: the next check/in, not all future checkins). How do I filter for this?

Thanks,

[thomas_duemesnil]

You can partition the result set by room number

SELECT ROOMS.ROOMNUMBER AS 'Room Number',
       ROOMS.ROOMSTATUS AS 'Status of Room',
       FOLIOHD.CHECKIN AS 'Current C/In Date',
       FOLIOHD.CHECKOUT AS 'Current C/Out Date',
       ROOMS.LASTOCCDATE AS 'Last C/Out Date',
       FOLIOHD.HOUSENOTE AS 'Housekeeping Note',
       ROOMBOOK.FROMDATE AS 'Next C/In Date',
           min( ROOMBOOK.FROMDATE ) over (ROOMS.ROOMNUMBER) AS minDate,
       ROOMBOOK.TODATE AS 'Next C/Out Date'

FROM ROOMS
LEFT OUTER JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER
LEFT OUTER JOIN HISTHD ON ROOMS.LASTFOLIO = HISTHD.NUMBER
JOIN ROOMBOOK ON ROOMS.ROOMNUMBER = ROOMBOOK.ROOMNUMBER

WHERE ROOMS.ROOMNUMBER <> '103' AND  ...
AND ROOMBOOK.FROMDATE = mindate

Check the help file for WINDOW functions. There you can find some ideas.

Hope This Helps

[Former Member]

If everything else is working how about adding MIN and CURRENT DATE?

SELECT ROOMS.ROOMNUMBER AS 'Room Number',
   ROOMS.ROOMSTATUS AS 'Status of Room',
   FOLIOHD.CHECKIN AS 'Current C/In Date',
   FOLIOHD.CHECKOUT AS 'Current C/Out Date',
   ROOMS.LASTOCCDATE AS 'Last C/Out Date',
   FOLIOHD.HOUSENOTE AS 'Housekeeping Note',
   MIN(ROOMBOOK.FROMDATE) AS 'Next C/In Date',
   ROOMBOOK.TODATE AS 'Next C/Out Date'
FROM ROOMS
LEFT OUTER JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER
LEFT OUTER JOIN HISTHD ON ROOMS.LASTFOLIO = HISTHD.NUMBER
JOIN ROOMBOOK ON ROOMS.ROOMNUMBER = ROOMBOOK.ROOMNUMBER
WHERE ROOMS.ROOMNUMBER <> '103' AND  ROOMS.ROOMNUMBER <> '110' AND  ROOMS.ROOMNUMBER <> '210' AND       
ROOMS.ROOMNUMBER <> '227' AND  ROOMS.ROOMNUMBER <> '228' AND ROOMBOOK.FROMDATE >= CURRENT DATE

[Breck_Carter]

Without a GROUP BY, there is only one group (all the resulting rows) so MIN(ROOMBOOK.FROMDATE) will have the same value for all the rows.