# what is the better way to handle.

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

1. ### bhushanvinayNew 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

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

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

Or quite simply
SELECT * FROM abc
WHERE a <> '2'
5. ### FrankKalisModerator

Or quite simply
SELECT * FROM abc
WHERE a <> '2'
[/quote]
Okay, based on the sample data this will also work []
6. ### bhushanvinayNew 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. ### FrankKalisModerator

In this case you need to explain the logic you want to apply here a bit more.
8. ### bhushanvinayNew 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. ### FrankKalisModerator

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. ### bhushanvinayNew 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. ### FrankKalisModerator

or
Select min(a) as a,b,c from table
group by b,c

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. ### bhushanvinayNew Member

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

If you're happy ...
16. ### bhushanvinayNew Member

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