Board index » delphi » PLEASE HELP ! I am new to SQL

PLEASE HELP ! I am new to SQL

I will assume a simple table.

TAB1

COL1    COL2    COL3
1            A            silver
2            A            blue
3            A            red
4            B            green
5            B            yellow
6            B            black
7            C            navy
8            C            marron

I need the result

COL1    COL2    COL3
1            A            silver
4            B            green
7            C            navy

in others words, i need a result group by COL2 and with the first ocurrency
of COL2 ordered by COL2

Sorry bad english and thanks in advance !
Bruno

 

Re:PLEASE HELP ! I am new to SQL


Quote
Bruno Lovatti <blova...@vix.zaz.com.br> wrote in message

news:39edcf9f_1@dnews...
Quote
> I will assume a simple table.

> TAB1

> COL1    COL2    COL3
> 1            A            silver
> 2            A            blue
> 3            A            red
> 4            B            green
> 5            B            yellow
> 6            B            black
> 7            C            navy
> 8            C            marron

> I need the result

> COL1    COL2    COL3
> 1            A            silver
> 4            B            green
> 7            C            navy

> in others words, i need a result group by COL2 and with the first
ocurrency
> of COL2 ordered by COL2

> Sorry bad english and thanks in advance !
> Bruno

select col1, col2, col3 from tableA t1
where exists
(select * from tableA t2
where col1 = t1.col1
and col2 = t1.col2
and col1 = (select min(col1)  from tableA
where col2 = t2.col2))
order by col2

HTH,
Ping Kam

Re:PLEASE HELP ! I am new to SQL


Thanks a lot !
Was not so simple ! for me.

"Ping Kam" <p...@quikcard.com> escreveu na mensagem
news:8skolv$jsi4@bornews.borland.com...

Quote
> Bruno Lovatti <blova...@vix.zaz.com.br> wrote in message
> news:39edcf9f_1@dnews...
> > I will assume a simple table.

> > TAB1

> > COL1    COL2    COL3
> > 1            A            silver
> > 2            A            blue
> > 3            A            red
> > 4            B            green
> > 5            B            yellow
> > 6            B            black
> > 7            C            navy
> > 8            C            marron

> > I need the result

> > COL1    COL2    COL3
> > 1            A            silver
> > 4            B            green
> > 7            C            navy

> > in others words, i need a result group by COL2 and with the first
> ocurrency
> > of COL2 ordered by COL2

> > Sorry bad english and thanks in advance !
> > Bruno

> select col1, col2, col3 from tableA t1
> where exists
> (select * from tableA t2
> where col1 = t1.col1
> and col2 = t1.col2
> and col1 = (select min(col1)  from tableA
> where col2 = t2.col2))
> order by col2

> HTH,
> Ping Kam

Other Threads