Professional Web Applications Themes

Select query - Microsoft SQL / MS SQL Server

Hi, I have a table with the following data: RowID IPXID RebateAmount AdjDesc 1 1 10 Test1 2 1 -10 Test2 My desired resultset should be IPXID RebateAmount AdjDesc 1 0 Test1, Test2 or IPXID RebateAmount AdjDesc 1 0 Test2 I have written the SQL for this to get my desired resultset but I am not happy with my solution because what I essentially did was hardcode the values of ROWID which I dont want to. My solution is: SELECT IPXID, SUM(RebateAmount)AS RebateAmount, COALESCE(MIN(CASE ROWID WHEN 1 THEN AdjDesc END),'')+','+ COALESCE(MIN(CASE ROWID WHEN 2 THEN AdjDesc END),'') AS AdjDesc FROM ...

  1. #1

    Default Select query

    Hi,

    I have a table with the following data:

    RowID IPXID RebateAmount AdjDesc
    1 1 10 Test1
    2 1 -10 Test2

    My desired resultset should be

    IPXID RebateAmount AdjDesc

    1 0 Test1, Test2

    or

    IPXID RebateAmount AdjDesc

    1 0 Test2



    I have written the SQL for this to get my desired resultset but I am not
    happy with my solution because what I essentially did was hardcode the
    values of ROWID which I dont want to.

    My solution is:

    SELECT IPXID, SUM(RebateAmount)AS RebateAmount,
    COALESCE(MIN(CASE ROWID WHEN 1 THEN AdjDesc END),'')+','+
    COALESCE(MIN(CASE ROWID WHEN 2 THEN AdjDesc END),'')
    AS AdjDesc
    FROM tbltest
    GROUP BY IPXID

    Can anyone write a better SQL without hardcoding the rowID values (so that
    it works for any rowID and not just 1 and 2) and get me the desired
    resultset I want.?.

    Any help is truly appreciated.

    The DDL is given below:

    Create table tbltest
    (RowID INT,
    IPXID INT,
    RebateAmount Money,
    AdjDesc Varchar(20)
    )
    Insert into tbltest Values (1,1,10,'Test1')
    Insert into tbltest Values (2,1,-10,'Test2')

    Select * from tbltest

    Drop table tbltest.

    Thanks

    Meher







    Meher Guest

  2. #2

    Default Re: Select query

    Perhaps this helps. It requires only that (ipxid, rowid) is unique.

    SELECT T.ipxid, SUM(rebateamount) AS rebateamount,
    COALESCE(MIN(CASE seq WHEN 1 THEN adjdesc END),'')+','+
    COALESCE(MIN(CASE seq WHEN 2 THEN adjdesc END),'')
    AS AdjDesc
    FROM tbltest AS T
    JOIN
    (SELECT T1.ipxid, T1.rowid, COUNT(*) AS seq
    FROM tbltest AS T1
    LEFT JOIN tbltest AS T2
    ON T2.rowid <= T1.rowid
    GROUP BY T1.ipxid, T1.rowid) AS Q
    ON T.ipxid = Q.ipxid AND T.rowid = Q.rowid
    GROUP BY T.ipxid

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: Select query

    The first query is a crosstab.

    Refer to:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574
    http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_04j7.asp
    http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608
    http://www.sqlteam.com/item.asp?ItemID=2955
    Also:
    http://www.rac4sql.net

    The second can be simplified to

    select IPXID,sum(RebateAmount) as amt, max(AdjDesc) as Adj
    from tb
    group by IPXID

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Meher Malakapalli" <com> wrote in message
    news:phx.gbl... 


    oj Guest

  4. #4

    Default Re: Select query

    Thanks oj but what I say is Choosing the max is alphabetical, what I would
    want is the one entered last. Subtle difference, but definitely a more
    difficult query I presume. would that be possible?

    Thanks again.

    M

    "oj" <com> wrote in message
    news:phx.gbl... [/ref]
    that 
    >
    >[/ref]


    Meher Guest

  5. #5

    Default Select query

    Hi,

    I have a table with following columns:

    col1, col2, col3

    I need to fetch rows for distinct values of col1 but I need to fetch all 3
    columns. I am currently doing:

    SELECT DISTINCT(col1), col2, col3
    FROM myTable
    WHERE col3 = something

    but this returns distinct values for all 3 columns instead of returning only
    distinct values of first column. This means that if I have following data:

    col1 col2 col3
    A 2 1
    B 2 1
    A 2 2

    , I'll get 3 rows returned because they are distinct. What I want is to get
    only 2 rows since I am interested in distinct values for col1 only.

    Can somebody help me with this?

    Thank you,

    --
    Dino Buljubasic
    Software Developer
    http://rivusglobal.com


    Dino Guest

  6. #6

    Default Re: Select query

    --col3 is unique
    select *
    from tb t1
    where col3=(select max(col3)
    from tb t2
    where t2.col1=t1.col1)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Dino M. Buljubasic" <com> wrote in message
    news:G_dYa.39715$telusplanet.net... 
    only 
    data: 
    get 


    oj Guest

  7. #7

    Default select query

    Hi Guys,
    I have a question, an immediate help is requested.

    create table try (id int, status int, dob datetime)
    combination of all 3 fields forms a primary key.

    data is as follows:

    ID criteria dob
    1 30 2003-01-01 00:00:00.000
    1 60 2003-01-10 00:00:00.000
    2 60 2003-01-10 00:00:00.000
    2 45 2003-01-20 00:00:00.000
    3 60 2003-03-20 00:00:00.000
    3 40 2003-03-17 00:00:00.000



    I want ot display only those ID where criteria is 60, and
    within that It should check for max(dob) for a particular
    ID.

    e.g

    there are 3 subset here
    set 1:
    1 30 2003-01-01 00:00:00.000
    1 60 2003-01-10 00:00:00.000

    ID 1 should appear as dob for 60 is the highest among
    this subset.

    set 2:

    2 60 2003-01-10 00:00:00.000
    2 45 2003-01-20 00:00:00.000

    will not appear as 60 does not have highest value of dob.

    similarly for third set it should appear.


    so my answare should be ID = 1,3

    An immmediate help is greatly appreciated.

    priya
    priya Guest

  8. #8

    Default select query

    sorry for the ignorance

    structure is :
    create table try (id int, criteria int, dob datetime)



    priya


     
    and 
    particular 
    priya Guest

  9. #9

    Default select query


    Try

    select id
    from try a
    where dob = ( select max(dob) from try b where a.id = b.id)
    and a.criteria = 60

    HTH,
    Srinivas Sampangi
     
    >and 
    >particular 
    >.
    >[/ref]
    sampangi Guest

  10. #10

    Default select query

    Thanks a lot.


     
    priya Guest

  11. #11

    Default Re: select query

    Hello Priya !

    The Simplest way to write this query. WIth indexes on Name it works perfect.

    Jens Süßmeyer.


    "priya" <com> schrieb im Newsbeitrag
    news:0cbb01c367f2$baa99b90$gbl... 


    Jens Guest

Similar Threads

  1. help with a SELECT query
    By halex2000 in forum MySQL
    Replies: 2
    Last Post: July 30th, 08:34 PM
  2. Query of Query to select a title first letter
    By Conti in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 18th, 11:42 PM
  3. select query help
    By Abul Hasan Lakhani in forum MySQL
    Replies: 8
    Last Post: October 15th, 07:04 PM
  4. Select query
    By Ad Bec in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 10th, 06:00 AM
  5. Using < and > in a select query
    By Grynch in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 18th, 03:55 AM

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