I need help with this query.

select
sb.name,
theMin = min(cast(fb.fudmin as int)),
theMax = max(cast(fb.fudmax as int)),
cnt = count(setid),
mso.ord,
fb.chars,
sfo.ford,
sfo.cond
from
mbasic mb
inner join msord mso on (mb.id = mso.context)
inner join sbasic sb on (mso.setref = sb.id)
inner join sford sfo on (sb.id = sfo.context)
inner join sfield sf on (sfo.id = sf.context)
inner join fbasic fb on (sf.track = fb.id)
where mb.idn = 'TEST1'

group by chars, ford, cond, ord, name
order by ord, ford


the preceeding query gives me this result:

name    theMin    theMax    cnt    ord    chars    ford    cond
-------------------------------------------------------------
ACB    1    2    1    4    A    1    M
ACB    1    56    2    4    ANBS    2    M
ACB    1    30    1    4    ANBS    3    M
ACB    7    14    5    4    AN    4    M
ACB    8    8    2    4    N    4    M
ACB    1    10    1    4    ANBS    5    O
ACB    5    5    1    4    A    6    O
ACB    3    3    1    4    AN    7    O
ACB    1    10    1    4    ANBS    7    O
AVGID    2    6    2    5    AN    1    M
AVGID    2    2    1    5    N    1    M
AVGID    1    30    1    5    ANBS    2    M
AVGID    1    3    1    5    N    3    O
AVGID    1    8    1    5    ANBS    4    O


I would like to further refine the query.  Notice in the 'ford' field
there are two rows with 4, and two rows with 7, and two rows with 1?
There should not be any repeating numbers within a single name
grouping.  I need to resolve this by selecting the row with the
greatest number of characters in the 'chars' field.  Something like:
len(chars) = max(len(chars))

the result should look like this:

name    theMin    theMax    cnt    ord    chars    ford    cond
-------------------------------------------------------------
ACB    1    2    1    4    A    1    M
ACB    1    56    2    4    ANBS    2    M
ACB    1    30    1    4    ANBS    3    M
ACB    7    14    5    4    AN    4    M
ACB    1    10    1    4    ANBS    5    O
ACB    5    5    1    4    A    6    O
ACB    1    10    1    4    ANBS    7    O
AVGID    2    6    2    5    AN    1    M
AVGID    1    30    1    5    ANBS    2    M
AVGID    1    3    1    5    N    3    O
AVGID    1    8    1    5    ANBS    4    O

because AN has more characters than N, and ANBS has more characters
than AN etc.

Thank you.

Chippy,

Without seeing some data or knowing the purpose or
meaning of this query, I can't say what the best solution is,
but you should be able to select only the rows with the
longest [chars] column for each (name,ford) with something
like this:

select
  name,
  theMin,
  theMax,
  cnt,
  ord,
  chars,
  ford,
  cond
from (
  select
  sb.name,
  theMin = min(cast(fb.fudmin as int)),
  theMax = max(cast(fb.fudmax as int)),
  cnt = count(setid),
  mso.ord,
  fb.chars,
  sfo.ford,
  sfo.cond
  from
  mbasic mb
  inner join msord mso on (mb.id = mso.context)
  inner join sbasic sb on (mso.setref = sb.id)
  inner join sford sfo on (sb.id = sfo.context)
  inner join sfield sf on (sfo.id = sf.context)
  inner join fbasic fb on (sf.track = fb.id)
  where mb.idn = 'TEST1'
  group by chars, ford, cond, ord, name
) T1
where not exists (
  select * from (
    select
      sb.name,
      theMin = min(cast(fb.fudmin as int)),
      theMax = max(cast(fb.fudmax as int)),
      cnt = count(setid),
      mso.ord,
      fb.chars,
      sfo.ford,
      sfo.cond
    from
    mbasic mb
    inner join msord mso on (mb.id = mso.context)
    inner join sbasic sb on (mso.setref = sb.id)
    inner join sford sfo on (sb.id = sfo.context)
    inner join sfield sf on (sfo.id = sf.context)
    inner join fbasic fb on (sf.track = fb.id)
    where mb.idn = 'TEST1'
    group by chars, ford, cond, ord, name
    ) T2
  where T2.name = T1.name
  and T2.ford = T2.ford
  and len(T2.chars) > len(T1.chars)
)
order by ord, ford

Steve Kass
Drew University

chippy wrote:

Show quoteHide quote

Thank you for your reply.  I tried the code you submitted, and I seem
to be getting the exact same results.  It looks like you are doing a
self join here (?).  The 'ford' field could have more than 2 repeating
fields.  In other words it could look like this:
ford
-------
1
2
3
4
4
4
5
6
7
7
7
7

Thanks again for your reply.

On 4 Nov 2005 04:12:03 -0800, chippy wrote:

Hi chippy,

Here's an untested suggestion. It might work, or it might not. Your
problem is not straightforward (with 6 tables in the query, and table
and column names that give no indication of how the data is related). If
this doesn't give the expected results, please refer to the information
at www.aspfaq.com/5006 to find out what extra information we need to
help you.

Show quoteHide quoteAND NOT EXISTS
(SELECT     *
  FROM       mbasic AS mb2
  INNER JOIN msord  AS mso2
        ON   mso2.context = mb2.id
  INNER JOIN sford  AS sfo2
        ON   sfo2.context = mso2.setref
  INNER JOIN sfield AS sf2
        ON   sf2.context = sfo2.id
  INNER JOIN fbasic AS fb2
        ON   fb2.id = sf2.track
  WHERE      mb2.idn = 'TEST1'
  AND        sfo2.ford = sfo.ford
  AND        mso2.ord = mso.ord
  AND        LEN(fb2.chars) > LEN(fb.chars))
Best, Hugo

Hugo, thank you for your reply-- I am looking into it.

Steve, thank you for your reply also.  I am looking at it.

Hugo,

Will it be helpful if I post the create script for the tables?

On 7 Nov 2005 10:45:04 -0800, chippy wrote:

Hi Chippy,

Did you try my suggestion? Did it work? If not., what was the problem?
Error message? Unexpected results?

Yes, posting CREATE TABLE statements is a good idea. Even better is to
also post INSERT statements with sample data, and the expected results.
This is all described at the page I refered you to in my eaarlier
message: www.aspfaq.com/5006.

Best, Hugo

Hugo,
Thanks again for your reply.  Your query gets me the closest I have
been to the results I want.  There are a few rows where the maximum or
minimum values are not getting placed into the theMin and theMax
fields.  In other words, there are a few cases where I am not getting
the minimum or maximum values.  I wonder if has to do with the
"LEN(fb2.chars) > LEN(fb.chars) " part of the query.  With this kind of
logic, doesn't it require 2 values to work properly?  I mentioned to
Steve earlier that there are cases where the 'ford' field might have a
value repeated 3 or more times.  It could be like this:
ford
-------
1
2
3
4
4
4
5
6
7
7
7
7

In any event, here are the create scripts:

CREATE TABLE [MBASIC] (
    [ID] [float] NOT NULL ,
    [CONTEXT] [float] NULL ,
    [IDN] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    CONSTRAINT [PK_MSGBASIC] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [MSORD] (
    [ID] [float] NULL ,
    [CONTEXT] [float] NULL ,
    [SETREF] [float] NULL ,
    [ORD] [int] NULL
) ON [PRIMARY]

CREATE TABLE [SBASIC] (
    [ID] [float] NOT NULL ,
    [CONTEXT] [float] NULL ,
    [NAME] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    CONSTRAINT [PK_SETBASIC] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [SFORD] (
    [ID] [float] NOT NULL ,
    [CONTEXT] [float] NULL ,
    [FORD] [int] NULL ,
    [COND] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    CONSTRAINT [PK_SETFLDORD] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [SFIELD] (
    [ID] [float] NULL ,
    [TRACK] [float] NULL ,
    [CONTEXT] [float] NULL
) ON [PRIMARY]

CREATE TABLE [FBASIC] (
    [ID] [float] NOT NULL ,
    [CONTEXT] [float] NULL ,
    [FUDMIN] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FUDMAX] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CHARS] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    CONSTRAINT [PK_FUDBASIC] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]

I don't know if these tables do you any good without the insert scripts
to put data in them, but I have been struggling with the
sp_generate_inserts stored procedure.  When I run it I get a row of:
'-----------'.  In any event, I appreciate your help with this and I
will see if I can get the sp_generate_inserts to work.
Thanks.

On 8 Nov 2005 06:17:31 -0800, chippy wrote:

Hi Chippy,

The number of values is irrelevant. The NOT EXISTS clause I suggested to
add could be phrased in English as >> For "this" row, check that there
is no "other" row with the same ford and ord but a longer chars <<. The
LEN(fb2.chars ) refers to the "other" row and the LEN(fb.chars) to
"this" row.


Thanks for the CREATE TABLE scripts. But you're right, I do need the
INSERT scripts as well. If you can't get the sp_generate_inserts to
work, consider manually typing some INSERT statements with relefvant
test data.

However, seeing your CREATE TABLE scripts does give me some clue as to
what might be going on. I see that you're using lots of "float" columns,
and that you're even joining on them. That's a very bad idea. Since
float is used for APPROXIMATE numbers, there might be small variations,
that might cause your joins to break.

The long term solution is to review and fix your data types.

The short term solution is to ROUND your values to the best-fitting
precision, then CAST to an exact numeric type (integer, decimal, ...).
Be aware this won't do any good for your performance!

Best, Hugo

Hugo,
I'll see what I can do with the insert scripts.  Thanks for your
suggestions.  I would never create a database with float values as
keys, but I can't do anything about it in this case.  All of the key
values are in the form of: #######.0 .  This database has been chugging
along for many years and I am unaware of any adverse issues relating to
these float values.  It's still beyond me why they chose a float value,
though.
Thanks again.

On 9 Nov 2005 03:51:14 -0800, chippy wrote:

Hi Chippy,

I'm looking forward to seeing the INSERT scripts and being able to
reproduce this and find out what the problem is.

In the mean time, some more general advice:

- Consider using DECIMAL(n,m) instead of FLOAT. This will still allow
the use of a decimal portion, but without incurring the problems that
FLOAT has due to the way the data is represented internally.

- If the ###.0 above actually means that the data is whole numbers, but
has a fixed ".0" at the end, you could even move to integers. If you
want to see the .0 (even though it's meaningless), then you can use a
CAST(columnname AS float) in your SELECT clause. Of course, if the .0
above was just a placeholder and the actual data is 13.4 or 55,304.0543,
then disregard this bit.

Best, Hugo

Hugo,
I am working with someone on this query-- if it still doesn't work I
will put together the insert statements and post them here.  I should
know by Mon. or Tues. next week.  Thanks again for your help-- it's
very much appreciated.

Hugo Kornelis wrote:
Show quoteHide quoteI think I have figured out a solution to my problem.  The real hang-up
was getting the row that had the maximum number of charicters-- that
was giving me the problem.  If you do this: max(charfield), then 'ABC'
comes before 'N', so 'N' is selected as the max for that group, and I
wanted the maximum number of characters.  So I did this:
max(len(charfield) + charfield).  This put the length in front of the
characters so that you get: '3ABC' and '1N', then 3 would be before 1.
I know it's a poor solution, but it worked.

Thank you very much for your help.

On 18 Nov 2005 04:44:09 -0800, chippy wrote:

(snip)
Hi Chippy,

I didn't test it, but I think that
  MAX(LEN(charfield) + charfield)
should result in a conversion error, since SQL Server will attempt to
cast the charfield to integer. Or did you mean
  MAX(CAST(LEN(charfield) AS varchar(2)) + charfield)

Beware that this will only produce correct results if the length doesn't
exceed 9. For bigger length, the conversion to varchar means that SQL
Server will treat '9' as being "bigger" than '10'.

In your first posts, you appeared to be only interested in the row with
the largest number of characters. So why not just use
  MAX(LEN(charfield))


Best, Hugo

How to calculate comparisons of fields between records
Deleting large amount of data on sql server 2000
Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC