min() in sql returning multiple rows of data

Hi,

I am trying to use the min() to get the instant (datetime) when the first order was placed.

I am using the below query but getting multiple rows of data.
orders.doc
fundsfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
select order_id, sn_id, inst
from order
where order_id = (select min(order_id) from order WHERE inst BETWEEN '1/1/2012 00:00:00' AND '1/3/2012 00:00:00' AND ORDER.SN_ID = '224284840');

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fundsfAuthor Commented:
I compiled the below query to get the most recent order.

Apparently something is throwing it off. If I use your code on a stand alone basis it works great.

Below is my code. I am still unable to pin point whats wrong.

SELECT
  --these two sub-queries are used to get the most recent order
    om1.ID
   ,cs.NAME
   ,om1.SN_ID
   ,om1.ORDER_ID
   ,om1.INST      
   ,cm.NAME
 
  FROM
  ORDER_M om1
  LEFT JOIN CL_S cs ON om1.PRV_ID = cs.PRV_ID
  LEFT JOIN dbo.CL_M cm ON om1.ION_ID = cm.ION_ID
 
  WHERE
  om1.INST BETWEEN '1/1/2012 00:00:00' AND '1/3/2012 00:00:00'
  AND
  om1.SN_ID = '224284840'
  AND
  om1.ORDER_STATUS_C IS NULL OR om1.ORDER_STATUS_C NOT IN (7,9)
--not 7 Denied Approval 9 Discontinued orders
  AND
  --most recent order
  om1.ORDER_ID =
                     (
                      SELECT
                      MIN(om2.ORDER_ID)
                      FROM
                      ORDER_M om2
                      LEFT JOIN CL_S cs2 ON
                      om2.PRV_ID = cs2.PRV_ID
                      WHERE
                      (om2.ORDER_STATUS_C IS NULL OR om2
                      .ORDER_STATUS_C NOT IN (7,9))
                      AND
                      om2.INST BETWEEN '1/1/2012 00:00:00'
                      AND '1/3/2012 00:00:00'
                      AND
                      om1.SN_ID = om2.SN_ID
                      AND
                      om2.SN_ID = '224284840'
                      )
johanntagleCommented:
Any error?  It would be really hard for me to troubleshoot for you since I don't have your database.  Try checking if the sub-select works on its own.  One thing I noticed is this:

                      AND
                      om1.SN_ID = om2.SN_ID

Open in new window


I don't think you can reference a table in the parent query on your subquery that way.  Do you really need this?  I was under the impression that ORDER_ID is primary key of the table so it should have been enough to just compare by that column.    Also, if ORDER_ID is primary key, I don't think that the below code is still necessary on the parent query:

  om1.INST BETWEEN '1/1/2012 00:00:00' AND '1/3/2012 00:00:00' 
  AND
  om1.SN_ID = '224284840'
  AND
  om1.ORDER_STATUS_C IS NULL OR om1.ORDER_STATUS_C NOT IN (7,9)

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Anuradha GoliSystems Development / Support SpecialistCommented:
Simply try this code:


SELECT top 1
ORDER.ORDER_ID
,ORDER.SN_ID
,ORDER.INST
FROM      
ORDER
WHERE
ORDER.INST BETWEEN '1/1/2012 00:00:00' AND '1/3/2012 00:00:00'
AND
ORDER.SN_ID = '224284840'
order BY
ORDER.ORDER_ID  


bold underline words are changed
fundsfAuthor Commented:
Anuradhay,

The Order.SN_ID is the event id. 1 event can have multiple orders and I am trying to get the first order for that event.

Your query returns multiple orders for 1 event. not the most recent order.

Order_id           Event_id                  Inst
42228055      224284840      2012-01-02 16:50:00.000
42435702      224284840      2012-01-02 17:30:00.000
42435712      224284840      2012-01-02 20:15:00.000
fundsfAuthor Commented:
I edited the query and it ran.....thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.