Table layout:

CREATE TABLE t_order  (id INT, custId INT, order DATE)

I am searching for a SQL command to choose no more than one row per order (the client who is the owner of an order is recognized with a area named custId).

I wish to choose ONE from the customer's orders (does not matter which, say sorted by id) if there's no order date given for the rows.

I wish to retrieve a clear Resultset for that customerId, if there's already an archive with given order date.

Here's a good example. Per customer there must be one order for the most part (one with no date given). Orders which have already to start dating ? value shouldn't appear whatsoever.


  +---------------------------------------------------------+

  id        custId         date                          

  +---------------------------------------------------------+

   1              10         NULL                          

   2              11         2008-11-11                    

   3              12         2008-10-23                    

   4              11         NULL                          

   5              13         NULL                          

   6              13         NULL                          

  +---------------------------------------------------------+

                           

                           

                                 Result

                            /

                            /

  +---------------------------------------------------------+

  id        custId         date                          

  +---------------------------------------------------------+

   1              10         NULL                          

                                                           

                                                           

                                                           

   5              13         NULL                          

                                                           

  +---------------------------------------------------------+

                                powered be JavE

Edit: I have choosen glavić's answer as the most appropriate one, since it provides the right result with slightly modified data:


  +---------------------------------------------------------+

  id        custId         date                          

  +---------------------------------------------------------+

   1              10         NULL                          

   2              11         2008-11-11                    

   3              12         2008-10-23                    

   4              11         NULL                          

   5              13         NULL                          

   6              13         NULL                          

   7              11         NULL                          

  +---------------------------------------------------------+

Sfossen's answer won't work when clients appear a lot more than two times due to its where clause constraint a.id != b.id.

Quassnoi's answer doesn't work for me personally, when i run server version 4..24 which yields the next error: alt text

For any specific customer it's:

SELECT *
FROM t_order
WHERE date IS NULL AND custId=? LIMIT 1

For those clients its:

SELECT a.*
FROM t_order a 
    LEFT JOIN t_order b ON a.custId=b.custID and a.id != b.id
WHERE a.date IS NULL AND b.date IS NULL
GROUP BY custId;

Do this:

SELECT to1.*
FROM t_order AS to1
WHERE
    to1.date IS NULL AND 
    to1.custId NOT IN (
    	SELECT to2.custId
    	FROM t_order AS to2
    	WHERE to2.date IS NOT NULL
    	GROUP BY to2.custId
    )
GROUP BY to1.custId

For MySQL 4:

SELECT to1.*
FROM t_order AS to1
LEFT JOIN t_order AS to2 ON
    to2.custId = to1.custId AND
    to2.date IS NOT NULL
WHERE
    to1.date IS NULL AND 
    to2.id IS NULL
GROUP BY to1.custId