Professional Web Applications Themes

SELECT TOP 2 - Microsoft SQL / MS SQL Server

I have table product: productID quantity date -------------------------------- 1 2 2003-07-21 1 3 2003-07-20 1 5 2003-07-19 2 4 2003-07-21 2 5 2003-07-20 2 2 2003-07-19 ...... ...... Now I would like to get the result: productId quantity1 quantity2 1 2 3 2 4 5 where quantity1 is the last record for the product1 and quantity2 is the previous record of product1 and so on. Something like top2 for every product, and then first is quantity1 and second is quantity2 order by date desc. Does anybody know the answer? Thank you, Simon...

  1. #1

    Default SELECT TOP 2

    I have table product:

    productID quantity date
    --------------------------------
    1 2 2003-07-21
    1 3 2003-07-20
    1 5 2003-07-19
    2 4 2003-07-21
    2 5 2003-07-20
    2 2 2003-07-19
    ......
    ......

    Now I would like to get the result:

    productId quantity1 quantity2
    1 2 3
    2 4 5

    where quantity1 is the last record for the product1
    and quantity2 is the previous record of product1
    and so on.

    Something like top2 for every product, and then first is quantity1 and
    second is quantity2 order by date desc.

    Does anybody know the answer?

    Thank you,
    Simon


    Simon Guest

  2. #2

    Default Re: SELECT TOP 2

    CREATE TABLE Product(productID INT, quantity INT, [date] datetime)
    GO
    INSERT INTO Product values(1 , 2 , '20030721')
    INSERT INTO Product values(1 , 3 , '20030720')
    INSERT INTO Product values(1 , 5 , '20030719')
    INSERT INTO Product values(2 , 4 , '20030721')
    INSERT INTO Product values(2 , 5 , '20030720')
    INSERT INTO Product values(2 , 2 , '20030719')
    INSERT INTO Product values(3 , 1 , '20030719')

    GO
    SELECT DISTINCT p.productID,
    (SELECT TOP 1 quantity FROM Product pq1 WHERE p.productID =
    pq1.productID
    ORDER BY [date] DESC) AS quantity1,
    COALESCE((SELECT TOP 1 quantity FROM Product pq2 WHERE p.productID =
    pq2.productID
    AND date < (SELECT MAX(date) FROM Product pq3 WHERE pq3.productID =
    p.productID)
    ORDER BY [date] DESC) ,0) AS quantity2
    FROM Product p
    GO
    DROP TABLE product




    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Simon" <si> wrote in message
    news:s5.net... 
    >
    >[/ref]


    Jacco Guest

  3. #3

    Default Re: SELECT TOP 2

    Thank you for your answer.

    what if I have also price column:

    productID quantity date price
    -----------------------------------------------
    1 2 2003-07-21 100
    1 3 2003-07-20 110
    1 5 2003-07-19 120
    2 4 2003-07-21 200
    2 5 2003-07-20 210
    2 2 2003-07-19 220
    3 1 2003-07-19 300
    ......
    ......

    AND the result:

    productId quantity1 quantity2 price1 price2
    1 2 3 100 110
    2 4 5 200 210
    3 1 0 300 0

    Thank you,
    Simon


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:eqbkZ$phx.gbl... 

    > >
    > >[/ref]
    >
    >[/ref]


    Simon Guest

  4. #4

    Default Re: SELECT TOP 2

    Here is the solution that utilizes CASE statement:

    create table ProductQuantity
    (productID int
    , quantity int
    , date datetime
    , price money)

    insert into ProductQuantity
    select 1, 2, '2003-07-21', 100
    union
    select 1 , 3 , '2003-07-20', 110
    union
    select 1 , 5 , '2003-07-19', 120
    union
    select 2 , 4 , '2003-07-21', 200
    union
    select 2 , 5 , '2003-07-20', 210
    union
    select 2 , 2 , '2003-07-19', 220
    union
    select 3 , 1 , '2003-07-19', 300

    select
    productID = pq1.productID
    , Date1 = max(pq1.date)
    , Date2 = max(pq2.date)
    , Quantity1 = (case
    when max(pq2.date) is null then max(pq1.Quantity)
    else max(case
    when pq2.productID is not null then pq1.Quantity
    else 0
    end)
    end)
    , Quantity2 = max(case
    when pq2.productID is not null then pq2.Quantity
    else 0
    end)
    , Price1 = (case
    when max(pq2.date) is null then max(pq1.Price)
    else max(case
    when pq2.productID is not null then pq1.Price
    else 0
    end)
    end)
    , Price2 = max(case
    when pq2.productID is not null then pq2.Price
    else 0
    end)

    from ProductQuantity pq1
    left join ProductQuantity pq2 on pq1.date > pq2.date
    and pq1.productID = pq2.productID
    and (select count(*) from ProductQuantity cpq where cpq.date > pq2.date and cpq.productId = pq2.productId) = 1
    group by pq1.productID


    drop table ProductQuantity

    --
    Dean Savovic
    www.teched.hr


    "Simon" <si> wrote in message news:3f1d0694$s5.net... 
    > = 
    > >
    > >[/ref]
    >
    >[/ref]


    Dean Guest

  5. #5

    Default Re: SELECT TOP 2

    Thank you for your answers

    regards
    Simon

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 


    Product [/ref][/ref]
    = [/ref]
    > pq3.productID [/ref]
    > and 
    > >
    > >[/ref]
    >
    >[/ref]


    Simon Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Replies: 0
    Last Post: September 24th, 03:24 AM
  3. Replies: 0
    Last Post: September 11th, 11:26 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  5. 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