Solved

min() in sql returning multiple rows of data

Posted on 2012-04-10
6
372 Views
Last Modified: 2012-04-11
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
0
Comment
Question by:fundsf
  • 3
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
Comment Utility
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
 

Author Comment

by:fundsf
Comment Utility
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
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 12

Expert Comment

by:Anuradha Goli
Comment Utility
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
 

Author Comment

by:fundsf
Comment Utility
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
 

Author Comment

by:fundsf
Comment Utility
I edited the query and it ran.....thanks
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now