Help with SQL statement

Thread View: borland.public.cppbuilder.database.ado

4 messages

[T] Back to Threads [V] Tree View [G] Group Articles

4 total messages Started by Vladimir Stefanovic Mon, 20 Jun 2005 19:20

#850

Author: Vladimir Stefanovic
Date: Mon, 20 Jun 2005 19:20

36 lines
1511 bytes

I have a single table with this imaginary format:

ID     TEXT
------------
2      AAAA
5      BBBBB
4      CCCC
3      AAAA
1      BBBBB

ID is uniq
TEXT is not unique

I want to extract into NEW Table

   AAAA
   BBBB
   CCCC

somehow like

      "INSERT INTO TblTmp (Text)\n"
      "SELECT Text\n"
      "FROM TblSource\n"
      "GROUP BY Text";

...but I want them ALSO to be sorted by ID, and I doesn't
know how to involve ORDER into this...





-- 
Best regards,
Vladimir Stefanovic

Re: Help with SQL statement

#851

Author: Viatcheslav V. Vassiliev
Date: Tue, 21 Jun 2005 07:36

59 lines
2419 bytes

This depends on DBMS you are using. On MS SQL Server you may write

insert into TblTemp (text)
select text from TblSource
group by text
order by max(id)

Note max(id) - there may be some id's for unique text, so you should use 
aggregate function - max, min, avg etc.

This may or may not work on DBMS other than MS SQL Server.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + 
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)

"Vladimir Stefanovic" <antivari@po.sbb.co.yu> �������/�������� � �������� 
���������: news:42b71709@newsgroups.borland.com...
>I have a single table with this imaginary format:
>
> ID     TEXT
> ------------
> 2      AAAA
> 5      BBBBB
> 4      CCCC
> 3      AAAA
> 1      BBBBB
>
> ID is uniq
> TEXT is not unique
>
> I want to extract into NEW Table
>
>   AAAA
>   BBBB
>   CCCC
>
> somehow like
>
>      "INSERT INTO TblTmp (Text)\n"
>      "SELECT Text\n"
>      "FROM TblSource\n"
>      "GROUP BY Text";
>
> ...but I want them ALSO to be sorted by ID, and I doesn't
> know how to involve ORDER into this...
>
>
>
>
>
> -- 
> Best regards,
> Vladimir Stefanovic
>

Re: Help with SQL statement

#852

Author: Vladimir Stefanovic
Date: Tue, 21 Jun 2005 08:41

22 lines
1535 bytes

>This depends on DBMS you are using. On MS SQL Server you may write
>
>insert into TblTemp (text)
>select text from TblSource
>group by text
>order by max(id)

Thank you!
This worked on MS ACCESS / MDB ...

>Note max(id) - there may be some id's for unique text, so you should use 
>aggregate function - max, min, avg etc.
>
>This may or may not work on DBMS other than MS SQL Server.

Now, I'll try if it will work with my ADOQuery->ExecSQL()



Best regards,
Vladimir Stefanovic

Re: Help with SQL statement

#853

Author: Vladimir Stefanovic
Date: Tue, 21 Jun 2005 09:10

11 lines
1112 bytes

>This may or may not work on DBMS other than MS SQL Server.

I have just tested your SQL with BCB6/TADOQuery/MDB and it
worked perfectly!




Best regards,
Vladimir Stefanovic

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads