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
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
[quote user="Adriaan"] Or quite simply SELECT * FROM abc WHERE a <> '2' [/quote] Okay, based on the sample data this will also work []
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
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
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
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
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?
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.
