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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
johanntagleConnect With a Mentor Commented:
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');
0
 
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'
                      )
0
 
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

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
0
 
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
0
 
fundsfAuthor Commented:
I edited the query and it ran.....thanks
0
All Courses

From novice to tech pro — start learning today.