SQL Query - Where Clause Question

Posted on 2015-01-13
Medium Priority
Last Modified: 2015-01-15
I have a table that I am querying some tables from and wanting to pull the last date.  However, sometimes this date is the same.   Example, a PO that was last received on the same day for same Item.

How could I create my query that if the "Received_Date" is the same, then look at the "Want_Date" instead?

select item_id, Unit_price, Want_date, Received_date
from  PO_Line

Not sure how to tweak the above query that if "Received_Date" is the same, then look at "Want_date" and only pull the most recent.  Any ideas?
Question by:holemania
  • 2
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 40547919
SELECT [Item ID], [Unit Price], [Want_Date], [Received_Date], ...
    SELECT *, ROW_NUMBER() OVER ( PARTITION BY /*PO_Number,*/ [Item ID] ORDER BY Received_Date DESC, Want_Date DESC ) AS row_num
    FROM PO_Line
) AS derived
    row_num = 1

Expert Comment

by:Leon Kammer
ID: 40547921

Could just be that it is late, but could you clarify the question please, I am having rouble understanding what you want the query to accomplish.

if "Received_Date" is the same, then look at "Want_Date".

(If the Received_Date colum is identical to the Want_Date, or if there are multiple records returned for the item ID for that date?)



Author Comment

ID: 40549544

I'll look at what Scott suggested, but I am pulling some data off a table.  Sometimes, the "received date" is the same, and I want to pull the most recent "received date".  Since I can have 2 or more records received on same date, then I am told to use the "want date" as the next filter.  

The example above, I have 2 PO that was received on the same date.  Now I need to then look at the "want date".

Author Closing Comment

ID: 40551310
Thank you.  That worked.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

622 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