sql 2000 selecting - 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)

sql 2000 selecting

Author

Topic

bmci3374
Starting Member

5 Posts

Posted - 2006-01-30 : 19:20:45

I am having trouble selecting only one record in a list with a sequence, example:

name addseq address
================================
bob, doe 1 123 str.
bob, doe 2 456 ave.
jane, doe 1 789 blvd.

What I want to do is select the records that are current which would be:

name addseq address
================================
bob, doe 2 456 ave.
jane, doe 1 789 blvd.

Please help with some ideas.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-30 : 19:24:13

Assuming you know which one is current by the maximum of addseq:


SELECT y.name, y.addseq, y.address
FROM YourTable y
INNER JOIN 
(
	SELECT name, MAX(addseq) AS addseq
	FROM YourTable
	GROUP BY addseq
) t
ON y.name = t.name AND y.addseq = t.addseq

Tara Kizer
aka tduggan

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-01-31 : 08:59:57

(Moved to the Transact-SQL forum.)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 09:13:24

or

Select columns from yourTable
where Address in (select max(Address) from yourTable group by name)

Madhivanan

Failing to plan is Planning to fail

bmci3374
Starting Member

5 Posts

Posted - 2006-01-31 : 16:15:29

tkizer-

do you have two tables there your.table and y if so when does it switch to t?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-31 : 16:24:17

bmci, I don't understand your question. Just substitute YourTable in both places in my query with the name of your actual table.

Tara Kizer
aka tduggan

bmci3374
Starting Member

5 Posts

Posted - 2006-01-31 : 16:58:24

tkizer- it looks like you have 3 table names in your query and your doing a join with 2 table names. is that correct?

Initially I failed to mention that i need to do a join.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-31 : 17:09:05

There is only one table mentioned in my query.

Tara Kizer
aka tduggan

bmci3374
Starting Member

5 Posts

Posted - 2006-01-31 : 17:27:24

I have labled what I am noticing in your query. If this is a single table
query why are you doing a join? please explain.
SELECT y.name, y.addseq, y.address
FROM YourTable(1) y(2) (also missing a comma)
INNER JOIN
(
SELECT name, MAX(addseq) AS addseq
FROM YourTable(1)
GROUP BY addseq
) t(3)
ON y(2).name = t(3).name AND y(2).addseq = t(3).addseq

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-31 : 17:32:36

I am doing a join because I need to use a derived table. YourTable is the same table regardless of how many times it is listed in the query. Please try the query out on your machine. Does it work?

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources