Need to filter data on condition

Question:

I have sales data. the orders get sales codes, like if the order came from web chat. We are dealing with orders which had sales rep involved, not automatic online order.

Each such order will get a code of 001. then also will get for example 'BCS'

so we would have data as

order#   Ord.date  ord.time  Amt etc.  sales code

12345     05/18/15  090000    100.00       001
12345     05/18/15  100000    100.00        BCS

so this is fine, the problem I am having in my crystal report is when the enter a 3rd code. The app is not stopping. but so when we have a 3rd row

12345     05/18/15   140000    100.00        BCQ

so this 3rd row is throwing off my CR totals.

I need to create a view on this data that will return all 001 and then only the first of a non 001 code, meaning according to the lower date/time. note that is entirely possible for there to be various dates, but we would want the earlier one only.

I am not sure how to make such a condition in SQL Query. So in our example we want the 001 row and the BCS because the time and date is earlier than the 3rd row.

Try something like this:

Select * from 
(
    Select *
    , row_number() over (partition by [OrderNo]
                       order by case SalesCode when '001' then 1 else 2 end
                       , Ord.Date, Ord.Time
                       )
                    as RowN
    from MyTable
) a
where a.RowN in (1,2)

If the row with SalesCode 001 is always the first row created for an order, then you can skip the case statement and just order by Date and time, like this, which will return the first two rows for every Order, by date and time:

Select * from 
(
    Select *
    , row_number() over (partition by [OrderNo]
                       order by Ord.Date, Ord.Time
                       )
                    as RowN
    from MyTable
) a
where a.RowN in (1,2) 

Note that both of these assume that each Order has only one 001 row. If they could have multiple 001 rows followed by a non-001 row, and you want to return all 001 rows, a different solution will be needed.

Related:

Foreign key in C#

SQL: overcoming no ORDER BY in nested query

Is there a better way to write this query involving a self select?

Inner Join 3 tables pl/sql

Fastest way to add a grouping column which divides the result per 4 rows

Using Sum in If in Mysql

timestamp SQL to Excel

Select count Columns group by No

Improving work with SQL DataTime

oracle sql error Case When Then Else

How do I find records in one table that do exist in another but based on a date?

Title search in SQL With replacement of noice words [on hold]

Can't output Guid Hashcode

SQL Customized search with special characters

How can I create missing date records for each employee based off a limited calendar?

SQL result table, match in second table SET type

MySQL: Select several rows based on several keys on a given column

Looping distinct values from one table through another without a join

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

Convert AWK command to sqlite query

Retrieve data from one table and insert into another table

SQL Multiple LIKE Statements

Default the year based on month value

Take thousand value in SQL

How to delete only the table relationed

SQL varchar variable inserts question mark

Pull information from SQL database and getting login errors

Query how often an event occurred at a given time

Select Statement on Two different views

How to select next row after select in SQL Server?

Retrieve Values As Column

copy table and drop it

SQL only one cell from column can have X value

Recursive Lag Column Calculation in SQL

Select all none null values from all columns in a table

SQL Group By multiple categories

Purging Database - Count purged/not-purged tables

Is it possible to index views in Apache Solr

T-SQL Ordering a Recursive Query - Parent/Child Structure

Entity Framework code-first: querying a view with no primary key

Get unique row by single column where duplicates exist

Can someone explain to me how this statement is an exclude?

How to join result of a Group by Query to another table

Merging two tables into new table by ID and date

SQL Repeated Condition in Two Tables

mysql_real_escape_string creates \ in server only not in local

left join table, find both null and match value

Matplotlib: Plot the result of an SQL query

for-loop add columns using SQL in MS Access

Add 1 to datediff result if time pass 14:30 or 2:30 PM