SQL STATEMENT

SQL STATEMENT

Post by Shami » Fri, 25 May 2001 07:34:31



SQL SERVER 7.0

Hi All,
     I need a SQL which joins between 2 tables and should return only
     the first matching record.

For example Table T1 and T2 got fields like

T1
--
ID     NAME
1        AA
2        BB
3        CC

T2
---
ID     SEQ_NUM
1        5000
1        5001
1        5002
2        6000
2        6001

When i join between T1 and T2   ,   T1.id = T2.id  should return only the
first matching record .
Output should be like

1    5000
2    6000

Thanks in Advance
Shamim

 
 
 

SQL STATEMENT

Post by David Brown » Fri, 25 May 2001 07:45:31


SELECT *
FROM TI JOIN T2
ON T1.ID = T2.ID
AND T2.SEQ_NUM = (SELECT MIN(SEQ_NUM) FROM T2 WHERE ID = T1.ID)

There is a slightly faster way using group by T1.ID, but the query is very
ugly.

David

MAX(CASE WHEN

Quote:> SQL SERVER 7.0

> Hi All,
>      I need a SQL which joins between 2 tables and should return only
>      the first matching record.

> For example Table T1 and T2 got fields like

> T1
> --
> ID     NAME
> 1        AA
> 2        BB
> 3        CC

> T2
> ---
> ID     SEQ_NUM
> 1        5000
> 1        5001
> 1        5002
> 2        6000
> 2        6001

> When i join between T1 and T2   ,   T1.id = T2.id  should return only the
> first matching record .
> Output should be like

> 1    5000
> 2    6000

> Thanks in Advance
> Shamim


 
 
 

SQL STATEMENT

Post by Umachandar Jayachandra » Fri, 25 May 2001 07:57:39


    By first, do you mean MIN( SEQ_NUM)? If so, you can do:

SELECT t1.ID, ( SELECT MIN( t2.SEQ_NUM ) FROM t2
                 WHERE t2.ID = t1.ID ) AS SEQ_NUM
  FROM t1

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

SQL STATEMENT

Post by David Brown » Fri, 25 May 2001 08:01:25



Quote:>     By first, do you mean MIN( SEQ_NUM)? If so, you can do:

> SELECT t1.ID, ( SELECT MIN( t2.SEQ_NUM ) FROM t2
>                  WHERE t2.ID = t1.ID ) AS SEQ_NUM
>   FROM t1

This only works if you only want the SEQ_NUM from t2.

Using this method you cannot get other fields on the same row as the Min
SEQ_NUM.

David

 
 
 

SQL STATEMENT

Post by BP Margoli » Fri, 25 May 2001 08:50:21


Shamim,

select T1.ID, min(T2.SEQ_NUM)
from T1
join T2
  on (T1.ID = T2.ID)
group by T1.ID

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> SQL SERVER 7.0

> Hi All,
>      I need a SQL which joins between 2 tables and should return only
>      the first matching record.

> For example Table T1 and T2 got fields like

> T1
> --
> ID     NAME
> 1        AA
> 2        BB
> 3        CC

> T2
> ---
> ID     SEQ_NUM
> 1        5000
> 1        5001
> 1        5002
> 2        6000
> 2        6001

> When i join between T1 and T2   ,   T1.id = T2.id  should return only the
> first matching record .
> Output should be like

> 1    5000
> 2    6000

> Thanks in Advance
> Shamim

 
 
 

SQL STATEMENT

Post by David Brown » Fri, 25 May 2001 08:50:21



Quote:> Shamim,

> select T1.ID, min(T2.SEQ_NUM)
> from T1
> join T2
>   on (T1.ID = T2.ID)
> group by T1.ID

Again this only works for the SEQ_NUM field.
If you want other fields from T2, you can't use this method.

David

 
 
 

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

HI !

I'm building an dynamic SQL statement and I would like the result set
to be stored in a cursor so I can do some operations on result set.


(This is just an sample in the reality I have very complex SQL
statement)

HOW CAN I DO THIS ??? (is it possible to get the result set into
cursor ?)

DECLARE MyCursor CURSOR FOR

OPEN MyCursor

Thanks !
GoRaZD

2. Using columns_updated()

3. Problem with SQL statement using wildcards and Like statement

4. Foxpro / Xbase

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

6. Text field in form

7. if statement and SQL statement in Access

8. Project Management Systems and Software

9. SQL Statement Differ From SQL Server and MS Access

10. SQL and Oracle sources in same SQL statement

11. SQL Trace to get SQL statement of Access query

12. SQL statement to change SQL Server Authentication mode

13. maximum size of a sql statement in MS SQL 7.0