Solved

min() in sql returning multiple rows of data

Posted on 2012-04-10
6
375 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Saving position of jquery sortable portlet to database 11 58
Selecting specific rows 3 51
xampp tool 12 28
ebay table structure 2 14
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

867 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

12 Experts available now in Live!

Get 1:1 Help Now