Professional Web Applications Themes

return distinct - Microsoft SQL / MS SQL Server

Is this what you are looking for.(check the group by clause) SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code, dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered, dbo.tbl_purchase_orders.date_expected FROM dbo.tbl_purchase_order_lines INNER JOIN dbo.tbl_purchase_orders ON dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1) group by dbo.tbl_purchase_order_lines.stock_code, dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered, dbo.tbl_purchase_orders.date_expected ORDER BY dbo.tbl_purchase_order_lines.stock_code, dbo.tbl_purchase_orders.date_expected -- -Vishal "Chris Dangerfield" <webmasterplanetmicro.co.uk> wrote in message news:41iNa.216$CO4.66news-binary.blueyonder.co.uk... > How do i make this return only distinct records... > > > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code, > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered, > dbo.tbl_purchase_orders.date_expected > FROM dbo.tbl_purchase_order_lines INNER JOIN > dbo.tbl_purchase_orders ON > dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id > WHERE (dbo.tbl_purchase_orders.delivery_complete = ...

  1. #1

    Default Re: return distinct

    Is this what you are looking for.(check the group by clause)

    SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
    dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    dbo.tbl_purchase_orders.date_expected
    FROM dbo.tbl_purchase_order_lines INNER JOIN
    dbo.tbl_purchase_orders ON
    dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id
    WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
    (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
    group by dbo.tbl_purchase_order_lines.stock_code,
    dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    dbo.tbl_purchase_orders.date_expected
    ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    dbo.tbl_purchase_orders.date_expected

    --
    -Vishal
    "Chris Dangerfield" <webmasterplanetmicro.co.uk> wrote in message
    news:41iNa.216$CO4.66news-binary.blueyonder.co.uk...
    > How do i make this return only distinct records...
    >
    >
    >
    > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
    > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    > dbo.tbl_purchase_orders.date_expected
    > FROM dbo.tbl_purchase_order_lines INNER JOIN
    > dbo.tbl_purchase_orders ON
    > dbo.tbl_purchase_order_lines.purchase_order_id =
    dbo.tbl_purchase_orders.id
    > WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
    > (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
    > ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    > dbo.tbl_purchase_orders.date_expected
    >
    >
    > help appreciated!!!!
    > chris
    >
    >

    Vishal Parkar Guest

  2. #2

    Default Re: return distinct

    it returns an error, saying error in group by...

    basically the query is returning purchase order info.

    if i have a stock item which has 2 open purchase orders i get 2 results, i
    just want to get one instance of the product.

    so i would normally go, distinct stockcode, but this does not work for me...
    why ???

    am stumped..

    chris


    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:#7qHPtkQDHA.2312TK2MSFTNGP12.phx.gbl...
    > Is this what you are looking for.(check the group by clause)
    >
    > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
    > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    > dbo.tbl_purchase_orders.date_expected
    > FROM dbo.tbl_purchase_order_lines INNER JOIN
    > dbo.tbl_purchase_orders ON
    > dbo.tbl_purchase_order_lines.purchase_order_id =
    dbo.tbl_purchase_orders.id
    > WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
    > (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
    > group by dbo.tbl_purchase_order_lines.stock_code,
    > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    > dbo.tbl_purchase_orders.date_expected
    > ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    > dbo.tbl_purchase_orders.date_expected
    >
    > --
    > -Vishal
    > "Chris Dangerfield" <webmasterplanetmicro.co.uk> wrote in message
    > news:41iNa.216$CO4.66news-binary.blueyonder.co.uk...
    > > How do i make this return only distinct records...
    > >
    > >
    > >
    > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
    > > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    > > dbo.tbl_purchase_orders.date_expected
    > > FROM dbo.tbl_purchase_order_lines INNER JOIN
    > > dbo.tbl_purchase_orders ON
    > > dbo.tbl_purchase_order_lines.purchase_order_id =
    > dbo.tbl_purchase_orders.id
    > > WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
    > > (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
    > > ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    > > dbo.tbl_purchase_orders.date_expected
    > >
    > >
    > > help appreciated!!!!
    > > chris
    > >
    > >
    >
    >

    Chris Dangerfield Guest

  3. #3

    Default Re: return distinct

    change group by cluase as follows.

    group by dbo.tbl_purchase_order_lines.stock_code,
    dbo.tbl_purchase_order_lines.qty_ordered ,
    dbo.tbl_purchase_orders.date_expected
    >if i have a stock item which has 2 open purchase orders i get 2 results, i
    >just want to get one instance of the product.
    you have to make sure out of these 2 purchase orders which row needs to be
    returned. there has to be clear cut logic that will return the row
    for example a purchase order may contain 2 rows in a table with different
    prices. in this case if you want to return a single row which has maximum
    price you can use max aggregate function.
    hence your query may look like this.

    select p_order,max(price)
    from table
    group by p_order.

    Therefore you need to have some logic behind returning single row for a
    purchase order.

    --
    -Vishal
    "Chris Dangerfield" <webmasterplanetmicro.co.uk> wrote in message
    news:FeiNa.328$CO4.138news-binary.blueyonder.co.uk...
    > it returns an error, saying error in group by...
    >
    > basically the query is returning purchase order info.
    >
    > if i have a stock item which has 2 open purchase orders i get 2 results, i
    > just want to get one instance of the product.
    >
    > so i would normally go, distinct stockcode, but this does not work for
    me...
    > why ???
    >
    > am stumped..
    >
    > chris
    >
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:#7qHPtkQDHA.2312TK2MSFTNGP12.phx.gbl...
    > > Is this what you are looking for.(check the group by clause)
    > >
    > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
    > > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    > > dbo.tbl_purchase_orders.date_expected
    > > FROM dbo.tbl_purchase_order_lines INNER JOIN
    > > dbo.tbl_purchase_orders ON
    > > dbo.tbl_purchase_order_lines.purchase_order_id =
    > dbo.tbl_purchase_orders.id
    > > WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
    > > (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
    > > group by dbo.tbl_purchase_order_lines.stock_code,
    > > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    > > dbo.tbl_purchase_orders.date_expected
    > > ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    > > dbo.tbl_purchase_orders.date_expected
    > >
    > > --
    > > -Vishal
    > > "Chris Dangerfield" <webmasterplanetmicro.co.uk> wrote in message
    > > news:41iNa.216$CO4.66news-binary.blueyonder.co.uk...
    > > > How do i make this return only distinct records...
    > > >
    > > >
    > > >
    > > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
    > > > dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    > > > dbo.tbl_purchase_orders.date_expected
    > > > FROM dbo.tbl_purchase_order_lines INNER JOIN
    > > > dbo.tbl_purchase_orders ON
    > > > dbo.tbl_purchase_order_lines.purchase_order_id =
    > > dbo.tbl_purchase_orders.id
    > > > WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
    > > > (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
    > > > ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    > > > dbo.tbl_purchase_orders.date_expected
    > > >
    > > >
    > > > help appreciated!!!!
    > > > chris
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

  4. #4

    Default return distinct

    I am not 100% sure, but try those 2 solutions:

    1-Will sql allow you to put the distinct keyword before
    your top 100 percent. If not take solution 2 below

    2-Group by on all fields in the select. This way you will
    get all distinct records:

    SELECT TOP 100 PERCENT
    dbo.tbl_purchase_order_lines.stock_code,
    dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    dbo.tbl_purchase_orders.date_expected

    FROM dbo.tbl_purchase_order_lines INNER JOIN
    dbo.tbl_purchase_orders ON
    dbo.tbl_purchase_order_lines.purchase_order_id =
    bo.tbl_purchase_orders.id

    WHERE (dbo.tbl_purchase_orders.delivery_complete = 0)
    AND
    (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)

    ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    dbo.tbl_purchase_orders.date_expected

    GROUP BY
    dbo.tbl_purchase_order_lines.stock_code,
    dbo.tbl_purchase_orders.date_expected,
    dbo.tbl_purchase_order_lines.qty_ordered

    Sorry, but I can't test this at the moment I don't have a
    running copy of SQL.

    Let me know if it worked
    >-----Original Message-----
    >How do i make this return only distinct records...
    >
    >
    >
    >SELECT TOP 100 PERCENT
    dbo.tbl_purchase_order_lines.stock_code,
    >dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
    >
    dbo.tbl_purchase_orders.date_expected
    >FROM dbo.tbl_purchase_order_lines INNER JOIN
    > dbo.tbl_purchase_orders ON
    >dbo.tbl_purchase_order_lines.purchase_order_id =
    dbo.tbl_purchase_orders.id
    >WHERE (dbo.tbl_purchase_orders.delivery_complete = 0)
    AND
    >(dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
    >ORDER BY dbo.tbl_purchase_order_lines.stock_code,
    >dbo.tbl_purchase_orders.date_expected
    >
    >
    >help appreciated!!!!
    >chris
    >
    >
    >.
    >
    Laurent Lemire Guest

Similar Threads

  1. SQL Distinct
    By DDhillon in forum Coldfusion Database Access
    Replies: 8
    Last Post: July 27th, 03:44 AM
  2. need help on DISTINCT
    By Krechting in forum ASP Database
    Replies: 1
    Last Post: January 12th, 01:07 AM
  3. Replies: 3
    Last Post: April 18th, 12:52 PM
  4. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139