topic-26844

Board index FiveWin for Harbour/xHarbour SQL-query with max lenght

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

SQL-query with max lenght
Posted: Thu Jul 25, 2013 05:38 PM

Hi,

I know this is not the right forum for SQL-query's, bet mayme someone know the result.

I have a SQL-table (tagnames) like

Code (fw): Select all Collapse

PARAMETER  INHOUD
---------  ------
TAGNAME    AI123
PTDESC     THIS IS A TEST
KEYWORD    A
TAGNAME    AI1231
PTDESC     THIS IS AN OTHER TEST
KEYWORD    ABBB
TAGNAME    AI123456
PTDESC     THIS IS A TEST
KEYWORD    AQ

I would like to return an unique record per PARAMETER and the maximum lenght of field INHOUD
It sould be something like

Code (fw): Select all Collapse

PARAMETER  INHOUD
---------  ------
TAGNAME    8
PTDESC     21
KEYWORD    4

I already have tried with

Code (fw): Select all Collapse

SELECT parameter,inhoud,length(inhoud) as vlen  from tagnames group by parameter

But this give me the unique PARAMETERS, but not the maximum of the field-lengt of INHOUD

The same with

Code (fw): Select all Collapse

SELECT parameter,inhoud,max(length(inhoud)) as vlen  from tagnames group by parameter

How can I do this?
Thanks,

Enrico Maria Giordano

Posts: 9022

Joined: Thu Oct 06, 2005 08:17 PM

Re: SQL-query with max lenght
Posted: Thu Jul 25, 2013 08:49 PM

Marc,

Marc Vanzegbroeck wrote:
Code (fw): Select all Collapse
SELECT parameter,inhoud,length(inhoud) as vlen  from tagnames group by parameter

The query below works fine with Access:

Code (fw): Select all Collapse

SELECT Parameter, Max( Len( Inhoud ) ) AS VLen FROM TagNames GROUP BY Parameter

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: SQL-query with max lenght
Posted: Fri Jul 26, 2013 06:02 AM

Enrico Maria Giordano wrote:Marc,
Marc Vanzegbroeck wrote:
Code (fw): Select all Collapse
SELECT parameter,inhoud,length(inhoud) as vlen  from tagnames group by parameter
The query below works fine with Access:
Code (fw): Select all Collapse
SELECT Parameter, Max( Len( Inhoud ) ) AS VLen FROM TagNames GROUP BY Parameter
EMG

Enrico,

You are correct

Code (fw): Select all Collapse

SELECT parameter,imax(length(inhoud)) as vlen  from tagnames group by parameter

works fine also with SQLite.
I think I have made a mistake while testing, I used instead of GROUP BY, ORDER BY because i was thinking to order it on length from high to low and than Group them, but GROUP BY and ORDER BY doesn't work in the same select..

FiveTech Support Forums

Continue the discussion