Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

filtering data

sg123sg123 Member Posts: 11
Hi,
This is a simplified version of my problem but it's basically the same as what I have typed below...
I have a view which displays products & orders from my DB.
It selects from the products table and 'left outer joins' the product to the order detail table which in turn is 'inner joined' to the orders table. A pretty standard setup....

To return my records into a VB recordset I use the following statement:

select * from MY_VIEW where order_date >= start_date_var and order_date <= end_date_var

this displays all the orders within a date range fine...

Ok, now this is where I get a spot of bother. Within any results I select there may be a few instances of the same product. I basically want to filter the records I get returned to only display ONE instance of each product, basically showing only the latest sales order.

Does any one know how I can do this??
I have messed with the max/group by functions but I still get several instances of the same product....

Thanks in advance for any help you can offer.
Regards,
Simon

Comments

  • paulj59paulj59 Member Posts: 420
    : Hi,
    : This is a simplified version of my problem but it's basically the same as what I have typed below...
    : I have a view which displays products & orders from my DB.
    : It selects from the products table and 'left outer joins' the product to the order detail table which in turn is 'inner joined' to the orders table. A pretty standard setup....
    :
    : To return my records into a VB recordset I use the following statement:
    :
    : select * from MY_VIEW where order_date >= start_date_var and order_date <= end_date_var
    :
    : this displays all the orders within a date range fine...
    :
    : Ok, now this is where I get a spot of bother. Within any results I select there may be a few instances of the same product. I basically want to filter the records I get returned to only display ONE instance of each product, basically showing only the latest sales order.
    :
    : Does any one know how I can do this??
    : I have messed with the max/group by functions but I still get several instances of the same product....
    :
    : Thanks in advance for any help you can offer.
    : Regards,
    : Simon
    :

    select distinct *
    might work
  • Justin BibJustin Bib USAMember Posts: 0

    _________ | http://forcoder.org | free video tutorials and ebooks about / R Delphi MATLAB PL/SQL Perl Go Visual Basic Visual Basic .NET C# C Python Swift Java Objective-C PHP Ruby C++ Assembly JavaScript Scratch F# LabVIEW Dart Prolog VBScript FoxPro Erlang Apex SAS Bash Kotlin Transact-SQL Fortran ML Scala Julia Scheme Hack Logo D Crystal Lisp Lua Alice Clojure COBOL ABAP Awk Rust Ada / ________

Sign In or Register to comment.