Basic SQL query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Basic SQL query question

Author

Topic

whitzman
Starting Member

3 Posts

Posted - 2004-07-13 : 19:58:19

I am newb with SQL

I don’t want Line 2 as I only want rows with maximum t_seqn for each group of
ttibom010100.t_mitm, ttibom010100.t_pono

Unique key is ttibom010100.t_mitm, ttibom010100.t_pono, ttibom010100.t_seqn

How can I modify this query to print only 3 lines

QUERY:
SELECT DISTINCT
ttibom010100.t_mitm, ttibom010100.t_pono, ttibom010100.t_seqn, ttibom010100.t_sitm, ttibom010100.t_exin, ttibom010100.t_qana, ttiedm110100.t_revi
FROM ttibom010100 INNER JOIN
ttiedm110100 ON ttibom010100.t_mitm = ttiedm110100.t_eitm AND ttibom010100.t_pono = ttiedm110100.t_pono
WHERE (ttibom010100.t_mitm = ' 0569-635-A10125')

RESULT:
t_mitm t_pono t_seqn t_sitm
0569-635-A10125 10 1 0569-635-A10300 2 B
0569-635-A10125 20 1 WFM071002TE-1 2 B
0569-635-A10125 20 2 WFM071002TE-1 2 C
0569-635-A10125 900 1 TP-ELEC-40004 2 B

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-13 : 23:35:38

Can you post some ddl and sample data?

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-14 : 05:19:42

[code]
SELECT ttibom010100.t_mitm, ttibom010100.t_pono, MAX(ttibom010100.t_seqn)
FROM ttibom010100 INNER JOIN
ttiedm110100 ON ttibom010100.t_mitm = ttiedm110100.t_eitm AND ttibom010100.t_pono = ttiedm110100.t_pono
WHERE (ttibom010100.t_mitm = ' 0569-635-A10125')
GROUP BY ttibom010100.t_mitm, ttibom010100.t_pono
[/code]

whitzman
Starting Member

3 Posts

Posted - 2004-07-14 : 10:05:59

quote:

Originally posted by JasonGoff


SELECT ttibom010100.t_mitm, ttibom010100.t_pono, MAX(ttibom010100.t_seqn)
FROM ttibom010100 INNER JOIN
ttiedm110100 ON ttibom010100.t_mitm = ttiedm110100.t_eitm AND ttibom010100.t_pono = ttiedm110100.t_pono
WHERE (ttibom010100.t_mitm = ' 0569-635-A10125')
GROUP BY ttibom010100.t_mitm, ttibom010100.t_pono

This is close but in neglects to include the additional fields in the result set such as ttiedm110100.t_revi

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-14 : 10:51:49

Derived table. Sample data and DDL would have helped to test this.

SELECT DISTINCT 
	  ttibom010100.t_mitm
	, ttibom010100.t_pono
	, ttibom010100.t_seqn
	, ttibom010100.t_sitm
	, ttibom010100.t_exin
	, ttibom010100.t_qana
	, ttiedm110100.t_revi
FROM	ttibom010100 INNER JOIN ttiedm110100
	  ON 	ttibom010100.t_mitm = ttiedm110100.t_eitm 
	  AND 	ttibom010100.t_pono = ttiedm110100.t_pono
	INNER JOIN	(
			SELECT	t_mitm, t_pono, MAX(t_seqn) 't_seqn'
			FROM	ttibom010100
			GROUP BY t_mitm, t_pono
			) x
	  ON 	ttibom010100.t_mitm = x.t_mitm
	  AND	ttibom010100.t_pono = x.t_pono
	  AND	ttibom010100.t_seqn = x.t_seqn

WHERE (ttibom010100.t_mitm = ' 0569-635-A10125')

whitzman
Starting Member

3 Posts

Posted - 2004-07-14 : 12:18:19

quote:

Originally posted by drymchaser

Derived table. Sample data and DDL would have helped to test this.

SELECT DISTINCT 
	  ttibom010100.t_mitm
	, ttibom010100.t_pono
	, ttibom010100.t_seqn
	, ttibom010100.t_sitm
	, ttibom010100.t_exin
	, ttibom010100.t_qana
	, ttiedm110100.t_revi
FROM	ttibom010100 INNER JOIN ttiedm110100
	  ON 	ttibom010100.t_mitm = ttiedm110100.t_eitm 
	  AND 	ttibom010100.t_pono = ttiedm110100.t_pono
	INNER JOIN	(
			SELECT	t_mitm, t_pono, MAX(t_seqn) 't_seqn'
			FROM	ttibom010100
			GROUP BY t_mitm, t_pono
			) x
	  ON 	ttibom010100.t_mitm = x.t_mitm
	  AND	ttibom010100.t_pono = x.t_pono
	  AND	ttibom010100.t_seqn = x.t_seqn

WHERE (ttibom010100.t_mitm = ' 0569-635-A10125')

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources