SQL Server Performance

what is the better way to handle.

Discussion in 'Getting Started' started by bhushanvinay, Sep 27, 2007.

  1. bhushanvinay New Member

    i have table
    create table abc( a varchar(20), b varchar(20) ,c varchar(20));
    insert into abc values ( '1', 'abc', 'xyz');
    insert into abc values ('2', 'abc', 'xyz');
    insert into abc values ('3', 'dbc', 'zyz');
    now on the select i want 1, 'abc', xyz' and '3', 'dbc','zyz'
    how can i do this in one single query.
    Regards
    Vinay
  2. Adriaan New Member

    Ever heard of search criteria? Check out the WHERE keyword in Books Online.
  3. FrankKalis Moderator

    It seems as if you want the "first" row of identical b and c values. If so, try this
    SELECT t1.*
    FROM abc t1
    JOIN (SELECT MIN(a) AS a
    FROM abc
    GROUP BY b, c) x
    ON t1.a = x.a
  4. Adriaan New Member

    Or quite simply
    SELECT * FROM abc
    WHERE a <> '2'
  5. FrankKalis Moderator

    [quote user="Adriaan"]
    Or quite simply
    SELECT * FROM abc
    WHERE a <> '2'
    [/quote]
    Okay, based on the sample data this will also work [:p]
  6. bhushanvinay New Member

    problem is a varchar the data can be also
    Values ( 'abc', 'abc', 'xyz') i just put in 1 to make life simple but in reality it wont be numeric.
    Regards
    Vinay
  7. FrankKalis Moderator

    In this case you need to explain the logic you want to apply here a bit more.
  8. bhushanvinay New Member

    Hi Frank Kalis,
    The data in the col1 is as varchar -, we want the data for fisrt available row for a group by of 2nd and 3rd columns,
    the first column can have any junk in it. its just that i wanted the data for the uniqeness of 2nd and 3rd column with the fist availabe value for any rows under that group.
    in case of the previous example
    1 abc xyz
    2 def zyz
    3 abc xyz
    aaa abc xyz
    we still need to get the first availabe data or any availabe data for a groub by of abc.
    for ex the following will be good
    1 abc xyz
    2 def zyz
    or
    aaa abc xyz
    2 def zyz
    would be also fine.
    Regards
    Vinay
  9. FrankKalis Moderator

    If I understand correctly this should do:
    SELECT t1.*
    FROM abc t1
    JOIN (SELECT MIN(a)AS a, b, c
    FROM abc
    GROUP BY b, c) x
    ON t1.a = x.a
    AND t1.b = x.b
    AND t1.c = x.c
  10. bhushanvinay New Member

    very good thinking Frank Kalis, Thank you very much, I was looking for a clue to handle this.
    the following works for me.
    i can close this bug.declare
    @selectTable Table(Iidentity
    int identity,a
    varchar(20),b
    varchar(20),c varchar(20)); insert
    into @selectTableselect
    a,b,cfrom
    abcSELECT
    t1.*FROM @selectTable t1
    JOIN (SELECT MIN(Iidentity) as IidentityFROM @selectTable
    GROUP BY b, c) x
    ON t1.Iidentity = x.Iidentity
  11. FrankKalis Moderator

    Glad to hear that [:)]
  12. Madhivanan Moderator

    or
    Select min(a) as a,b,c from table
    group by b,c
  13. Adriaan New Member

    Vinay, your second-last posting has some confusing results, especially the last bit:
    abc abc xyz
    2 def zyz
    Do you want to show actual rows of data, or aggregate values?
  14. bhushanvinay New Member

    yes i did change it.
    to aaa from abc, this looks good now!!
  15. Adriaan New Member

    If you're happy ...
  16. bhushanvinay New Member

    i want the actual rows of data , but on a aggregation of b, c columns.
    Regards
    Vinay
  17. Adriaan New Member

    So you want to see the first row of data for any combination of (b, c), where the first row is identified by the lowest value on column a.
    Madhivanan gave you the most straightforward syntax for that (using GROUP BY in the main query) but strictly speaking this doesn't show you the first row.
    Frank's solution is easier if you want to show other columns from the table as well, or add complication (like showing the second row), or in case you need to have an updatable resultset.
    In either case, please be aware that if your a column is not of a numeric data type (or datetime) that the 'first' value may not be what you're expecting.

Share This Page