none
sql query select max date

    Question

  • I am trying to get this Max(ORDERDATE) SQL query to work.  any help is greatly appreciated.

    What I have so far:

    Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where O.ORDERDATE = 
    (Select MAX(O.ORDERDATE)
    From ORDERS
    Where sl.loc like '29A01%')
    Order by SL.LOC, O.ORDERDATE DESC

    here is the result set I get: notice multiple records

    and here is what I really want: notice one record with max(oderdate)

    Wednesday, September 19, 2012 4:29 PM

All replies

  • Try (SQL 2005+)

    ;with cte as (Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE,
    ROW_NUMBER() OVER (PARTITION BY SL.SKU, SL.LOC ORDER BY O.OrderDate DESC) as RN
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where sl.loc like '29A01%')
    
    SELECT * FROM cte WHERE RN = 1
    Order by LOC, ORDERDATE DESC


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by SQL Novice 01 Wednesday, September 19, 2012 4:37 PM
    • Unproposed as answer by jimbrown.ws Wednesday, September 19, 2012 4:54 PM
    Wednesday, September 19, 2012 4:35 PM
    Moderator
  • wow thanks Naomi for the quick post!  I get an error,  FYI I am writting a VB.NET app connecting to Oracle.  sorry to throw a wrinkle in the mix. 

    Error 9/19/2012 11:37:13 AM 0:00:00.203 <link> - Oracle Database Error: ORA-00933: SQL command not properly ended 11 281

    Wednesday, September 19, 2012 4:40 PM
  • If you're using Oracle, you could have better luck posting in Oracle forum as T-SQL syntax is for SQL Server only. CTE & ROW_NUMBER() are also SQL Server specific.

    In Oracle you may want to do:

    Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where sl.loc like '29A01%'
    and O.OrderID = (SELECT TOP 1 OrderId
    from Orders O1 where O1.SKU = SL.SKU
    ORDER BY OrderDate DESC)
    I can not guarantee it will work in Oracle, but worth a quick try.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 4:45 PM
    Moderator
  • Sorry, Error I  don't think it likes the (Select Top 1

    Error 9/19/2012 11:51:17 AM 0:00:00.187 <link> - Oracle Database Error: ORA-00923: FROM keyword not found where expected 11 225

    thanks for trying!!!!

    Wednesday, September 19, 2012 4:52 PM
  • Oracle doesn't recognize Top 1 syntax.  A quick google search leads me to believe that Oracle's equivalent is:

    SELECT	*
    FROM	TABLENAME
    WHERE	ROWNUM<2

    So, using Naomi's query, try:

    Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where sl.loc like '29A01%'
    and O.OrderID = (SELECT OrderId
    from Orders O1 where O1.SKU = SL.SKU and ROWNUM<2)


    • Edited by dgjohnson Wednesday, September 19, 2012 4:59 PM
    Wednesday, September 19, 2012 4:58 PM
  • We still need ORDER BY OrderDate DESC, otherwise it'll pick up the random row.

    If there is only one max date (no orders on the same date), we can try:

    Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where sl.loc like '29A01%'
    and O.OrderDate = (SELECT Max(OrderDate)
    from Orders O1 where O1.SKU = SL.SKU) -- don't know if we need to also join with skuxloc to filter locations in subquery


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 5:03 PM
    Moderator
  • We still need ORDER BY OrderDate DESC, otherwise it'll pick up the random row.

    Yeah, my edit was to delete the "Top 1", because I forgot.  Apparently, I forgot to remove that because instead I removed the Order By.  Oops.
    • Edited by dgjohnson Wednesday, September 19, 2012 5:07 PM
    Wednesday, September 19, 2012 5:07 PM
  • this SQL throws an error: does not like and O.OrderID

    Error 9/19/2012 12:14:19 PM 0:00:00.249 <link> - Oracle Database Error: ORA-00904: "O"."ORDERID": invalid identifier 11 201

    Wednesday, September 19, 2012 5:18 PM
  • OrderId is supposed to be the primary key in the Orders table. If it's named differently, use the correct column's name instead of this made up name.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 5:19 PM
    Moderator
  • Fixed O.OrderKey  next error is does not like where O1.SKU

    Error 9/19/2012 12:24:49 PM 0:00:00.203 <link> - Oracle Database Error: ORA-00904: "O1"."SKU": invalid identifier 11 252

    Thanks sooooo much for your help


    • Edited by jimbrown.ws Wednesday, September 19, 2012 5:24 PM
    Wednesday, September 19, 2012 5:23 PM
  • I see, SKU comes from OrderDetails, not from the Orders table. It means you need to JOIN again in subquery with OrderDetails, e.g.

    Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where sl.loc like '29A01%'
    and O.OrderKey = (SELECT O1.OrderKey
    from Orders O1 
    INNER JOIN OrderDetail OD1 on O1.OrderKey = OD1.OrderKey
    where OD1.SKU = SL.SKU and O1.ROWNUM<2)
    This is just a guess work, as I am not versed in Oracle.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 5:28 PM
    Moderator
  • Error with O1.ROWNUM

    Error 9/19/2012 12:30:20 PM 0:00:00.171 <link> - Oracle Database Error: ORA-01747: invalid user.table.column, table.column, or column specification 11 337

    Wednesday, September 19, 2012 5:32 PM
  • Take a look here http://stackoverflow.com/questions/3451534/how-to-do-top-1-in-oracle

    Apparently, you can not use RowNum as a column name and the samples don't show what to do when you select from multiple tables.

    Also the comments link to ROW_NUMBER() in Oracle.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 5:37 PM
    Moderator
  • Thanks,  I will try.  it's a little over my head.  especially since we are not using max, not that I was using it correctly. thanks again for all your help!!! 
    Wednesday, September 19, 2012 5:47 PM
  • I searched a few more sites and I think you may adapt the last solution in this thread

    http://forums.manageengine.com/topic/equivalent-to-select-top-1-in-a-subselect-with-oracle


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 6:06 PM
    Moderator
  • thanks,  can you translate to my SQL?  I asume the below SQL I will work on it.  thanks again Naomi, you have gone above and beyond. 

    SELECT A.LASTNAME, (SELECT T.X FROM (SELECT FIRSTNAME X, PARENT P, RANK() OVER (PARTITION BY IQID ORDER BY AGE) R FROM PERSON) T WHERE T.P = A.ID AND R = 1) FROM PERSON

    Wednesday, September 19, 2012 6:18 PM
  • How about trying the original version slightly modified:

    SELECT * FROM (Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE,
    RANK() OVER (PARTITION BY SL.SKU, SL.LOC ORDER BY O.OrderDate DESC) as RN
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where sl.loc like '29A01%') X WHERE Rn = 1


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 6:21 PM
    Moderator
  • Thanks that runs but I still get multiple loc records ;-(

    Wednesday, September 19, 2012 6:27 PM
  • Do you need MAX per SKU or MAX per what? Use correct column names in the PARTITION BY clause in the RANK function.

    Also, try DENSE_RANK if you have the same max date for different orders.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Wednesday, September 19, 2012 6:43 PM
    Moderator
  • Max OrderDate Per Loc ie: 

    here is the original result set I get: notice multiple records

    and here is what I really want: notice one loc record with max(oderdate)

    the way I got the below example is in Excel "Advance Filter" Loc and Orderdate Unique Records Only

    Wednesday, September 19, 2012 6:52 PM
  • This also did not work:

    SELECT * FROM (Select SL.SKU, SL.LOC, O.CONSIGNEEKEY, O.C_EMAIL1, SL.QTY, O.ORDERDATE,
    DENSE_RANK() OVER (PARTITION BY SL.LOC ORDER BY O.OrderDate DESC) as RN
    FROM ORDERS O 
    Join ORDERDETAIL OD  ON OD.ORDERKEY = O.ORDERKEY
    Join SKUXLOC SL ON SL.SKU = OD.SKU
    Where sl.loc like '29A01%') X WHERE Rn = 1

    Wednesday, September 19, 2012 6:56 PM