SQL statement 
Author Message
 SQL statement

Hi,
  I need help solving a problem forming a sql statement.
I am giving the problem description:

Link IDs are based on a six digit prefix and a two digit suffix. The two digit
suffix is used to delineate a 'family' of designs for confirmed links. With the
<ost Recent Link Designs selected, only the highest suffix amongst a family of
designs is selected, For eg. if a query returns the following links:
90014000
90014001
90014002

Only the link 90014002 will be displayed. Note the common prefix 900140, and the
highest suffix of 02.

I have tried to use the following Sql statement:

select distinct max(l.link_id),l.band_designator, rtrim(a,site_id),
rtrim(b.site_id),rtrim(a.site_name),rtrim(b.site_name)
from link l,link_enda a, link_endb b
where a.link_id = l.link_id AND
      b.link_id = l.link_id AND
      l.link_type = 'R' AND
      convert (char(12),l.link_id) like '900%'
group by (substring (convert (char(12),l.link_id)1,6))

This stmt doesnt work... I am getting a whole bunch of records whereas
i should get only one..Could someone please tell me what i am doing wrong here
and how i can fix this. I would appreciate it if someone could please reply and
let me know about this ASAP. Thanks a lot.

-Rupali Bains



Thu, 02 Mar 2000 03:00:00 GMT
 SQL statement



Quote:
> Hi,
>   I need help solving a problem forming a sql statement.
> I am giving the problem description:

> Link IDs are based on a six digit prefix and a two digit suffix. The two
digit
> suffix is used to delineate a 'family' of designs for confirmed links.
With the
> <ost Recent Link Designs selected, only the highest suffix amongst a
family of
> designs is selected, For eg. if a query returns the following links:
> 90014000
> 90014001
> 90014002

> Only the link 90014002 will be displayed. Note the common prefix 900140,
and the
> highest suffix of 02.

> I have tried to use the following Sql statement:

> select distinct max(l.link_id),l.band_designator, rtrim(a,site_id),
> rtrim(b.site_id),rtrim(a.site_name),rtrim(b.site_name)
> from link l,link_enda a, link_endb b
> where a.link_id = l.link_id AND
>       b.link_id = l.link_id AND
>       l.link_type = 'R' AND
>       convert (char(12),l.link_id) like '900%'
> group by (substring (convert (char(12),l.link_id)1,6))

> This stmt doesnt work... I am getting a whole bunch of records whereas
> i should get only one..Could someone please tell me what i am doing wrong
here
> and how i can fix this. I would appreciate it if someone could please
reply and
> let me know about this ASAP. Thanks a lot.

> -Rupali Bains


I would try using a Sub-Select.  I am not sure if this will work, but it
might give you some ideas on things to try.

select l.link_id,l.band_designator, rtrim(a,site_id),
 rtrim(b.site_id),rtrim(a.site_name),rtrim(b.site_name)
 from link l,link_enda a, link_endb b
 where a.link_id = l.link_id AND
       b.link_id = l.link_id AND
       l.link_type = 'R' AND
       l.link_id = (
        select max(l.link_id)
        from link l
        where      convert (char(12),l.link_id) like '900%'
        group by (substring (convert (char(12),l.link_id)1,6)) )



Thu, 02 Mar 2000 03:00:00 GMT
 SQL statement

The distinct and MAX qualifiers only apply to possible multiple selctions
with the same data.  Use the MAX in a where clause and you should have
better results.  Otherwise use an ORDER BY and pick the first row.



Quote:
> I have tried to use the following Sql statement:

> select distinct max(l.link_id),l.band_designator, rtrim(a,site_id),
> rtrim(b.site_id),rtrim(a.site_name),rtrim(b.site_name)
> from link l,link_enda a, link_endb b
> where a.link_id = l.link_id AND
>       b.link_id = l.link_id AND
>       l.link_type = 'R' AND
>       convert (char(12),l.link_id) like '900%'
> group by (substring (convert (char(12),l.link_id)1,6))

> This stmt doesnt work... I am getting a whole bunch of records whereas
> i should get only one..Could someone please tell me what i am doing wrong

here


Thu, 02 Mar 2000 03:00:00 GMT
 SQL statement

For just one table, you can use:

select id
from   table
group by id/100
having id=max(id)

You should then be able to generalise from this.

I'd be careful about check the performance of this however,
as I don't know exactly how Sybase treats group by's like this
(even though id/100 is pretty similar to id).

Trevor

Quote:

> Hi,
>   I need help solving a problem forming a sql statement.
> I am giving the problem description:

> Link IDs are based on a six digit prefix and a two digit suffix. The two digit
> suffix is used to delineate a 'family' of designs for confirmed links. With the
> <ost Recent Link Designs selected, only the highest suffix amongst a family of
> designs is selected, For eg. if a query returns the following links:
> 90014000
> 90014001
> 90014002

> Only the link 90014002 will be displayed. Note the common prefix 900140, and the
> highest suffix of 02.

> I have tried to use the following Sql statement:

> select distinct max(l.link_id),l.band_designator, rtrim(a,site_id),
> rtrim(b.site_id),rtrim(a.site_name),rtrim(b.site_name)
> from link l,link_enda a, link_endb b
> where a.link_id = l.link_id AND
>       b.link_id = l.link_id AND
>       l.link_type = 'R' AND
>       convert (char(12),l.link_id) like '900%'
> group by (substring (convert (char(12),l.link_id)1,6))

> This stmt doesnt work... I am getting a whole bunch of records whereas
> i should get only one..Could someone please tell me what i am doing wrong here
> and how i can fix this. I would appreciate it if someone could please reply and
> let me know about this ASAP. Thanks a lot.

> -Rupali Bains




Fri, 03 Mar 2000 03:00:00 GMT
 SQL statement



Quote:
>Hi,
>  I need help solving a problem forming a sql statement.
>I am giving the problem description:

>Link IDs are based on a six digit prefix and a two digit suffix. The two digit
>suffix is used to delineate a 'family' of designs for confirmed links. With the
><ost Recent Link Designs selected, only the highest suffix amongst a family of
>designs is selected, For eg. if a query returns the following links:
>90014000
>90014001
>90014002

>Only the link 90014002 will be displayed. Note the common prefix 900140, and the
>highest suffix of 02.

BTW, it appears to me that you have two data elements within a single
column: "design family" (the prefix) and "design id" (the suffix). If
you can, I'd recommend storing these in separate columns, rather than
trying to manipulate substrings of a single column.

--------------------------------------------------------------------------
David Mullen              *** Any opinions expressed are mine, and not ***
Emjay Corporation         *** necessarily those of Emjay Corporation.  ***
Milwaukee, WI

Fight spam by joining CAUCE - Coalition Against Unsolicited Commercial Email
                http://www.cauce.org/



Sat, 04 Mar 2000 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. HOW TO create cursor from dynamic SQL statement (EXECUTE(@Statement))

2. Problem with SQL statement using wildcards and Like statement

3. SQL7 bug-2nd query in a T-SQL statement with a compute statement loses headers

4. if statement and SQL statement in Access

5. SQL Statement Differ From SQL Server and MS Access

6. SQL and Oracle sources in same SQL statement

7. Paradox Inline SQL statements and MS SQL 6.5

8. SQL Server Table References in .ASP SQL statement

9. SQL Web Assistant: Could not execute the SQL statement

10. transact sql equivalent of watcom sql statement

11. Translation from Oracle to SQL Server SQL statements ?

12. Different result from SQL server and Access Database by the same SQL statements


 
Powered by phpBB® Forum Software