Solved

min() in sql returning multiple rows of data

Posted on 2012-04-10
6
390 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37830974
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
ID: 37831046
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
ID: 37831068
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
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 12

Expert Comment

by:Anuradha Goli
ID: 37831601
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
ID: 37833075
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
ID: 37834493
I edited the query and it ran.....thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

628 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