Help with Writing a SQL Query - 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

General SQL Server Forums

New to SQL Server Programming

Help with Writing a SQL Query

Author

Topic

Dirch
Starting Member

8 Posts

Posted - 2012-01-31 : 11:05:12

I have an id and a last name. I want to write a query to get the next id based on the next last name in alphabetical order. So for example in this data:

Given: 89023 (for Cline)
Want: 5203 Daniels

How would you write the query that would return 5203 and Daniels when you are given the id of the previous last name in the table?

Thanks

domap
Starting Member

7 Posts

Posted - 2012-01-31 : 11:45:25

You could try something along the lines of:

select top 1 ID, LASTNAME from MYTABLE where LASTNAME > 'Cline' order by LASTNAME

Hope this helps!

Dirch
Starting Member

8 Posts

Posted - 2012-01-31 : 11:57:58

quote:
Originally posted by domap

You could try something along the lines of:

select top 1 ID, LASTNAME from MYTABLE where LASTNAME > 'Cline' order by LASTNAME

Hope this helps!

I think it does help. I could do something like this:

select top 1 attorney_tkid, attorney_lastname from matters_tbl
where attorney_lastname >
(Select top 1 attorney_lastname from matters_tbl where
attorney_tkid='14542')
and matter_location='Charlotte'
order by attorney_lastname

The subselect returns the lastname for the current attorney and the overall query returns the id and lastname of the next attorney

Thanks for the help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-31 : 11:59:45

why do you want to know the next entry ordered by the name?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Dirch
Starting Member

8 Posts

Posted - 2012-01-31 : 13:21:23

quote:
Originally posted by Transact Charlie

why do you want to know the next entry ordered by the name?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I want to use it with a next button and I want the next person in alphabetical order.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources