Next Previous issue with LastName

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)

Next Previous issue with LastName

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-11-25 : 02:19:43

I have read several of the forum topics on Next/Previous and unfortunately still do not know how to solve this problem that continues to haunt me. I need to get the next or previous record based on Last name. It all works perfectly until I have a duplicate last name. As usual, I will probably not send you the correct information to help you help me.. but I'm trying. :) I have created a sample table with some sample data. I hope I am sending this to you correctly.

CREATE TABLE [dbo].[Name] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Insert Into Name (FirstName, LastName) Values ('John', 'Doe')
Insert Into Name (FirstName, LastName) Values ('Jane', 'Doe')
Insert Into Name (FirstName, LastName) Values ('Max', 'Carlson')
Insert Into Name (FirstName, LastName) Values ('Jeff', 'Armstrong')
Insert Into Name (FirstName, LastName) Values ('Suzie', 'Armstrong')
Insert Into Name (LastName) Values ('Armstrong CC)

Now this data should provide two Doe's, Three Armstrongs (one with no first name) and one Carlson.

A routine will pass me the current person's name and I need to find either the next or previous. I figure if I can get one to work, I should be able to get the other.
I have tried several variations of the following:

Select Top 1 * from Name
WHERE Lastname >= {insert last name here}
AND (FirstName > (insert first name here) or firstName is null)
ORDER BY LastName

This works perfectly EXCEPT that if the name I am currently on is one with a blank first name. For example, if I want to find the name directly after Armstrong CC, it should return Max Carlson, but I get Jeff in this case. I keep thinking I have to be missing something small, but everytime I make a change, I make something else in this insane thing not work.. like null first names not being retrieved at all, or names being skipped completley.

Any advice is greatly appreciated. If I have not sent the info that helps you, please let me know for future reference.

Thanks so much
JAdauto

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-25 : 02:34:51

Why not use a combination of that ID and last name?


Select Top 1 * from Name
WHERE ID > @LastID
AND Lastname >= {insert last name here} 
AND (FirstName > (insert first name here) or firstName is null)
ORDER BY LastName

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-11-25 : 11:43:34

This may be a dumb question, and maybe I just dont understand how the ID is supposed to work. What if the ID for the next name that I need(alphabetically) is LESS then the ID I have? This would not work then would it??
Thanks so much,
JAdauto

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-25 : 12:26:14

Good Point


Select Top 1 * from Name
WHERE AND Lastname >= {insert last name here} 
AND (FirstName > (insert first name here) or firstName is null)
AND ID <> @LastID
ORDER BY LastName

"Give me one person order by lastname where the lastname is greater or equal to the last lastname and the ID of this new name do not match the ID of the last lastname."

I think that's what you are looking for.
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 13:13:06

try this out; check it out carefully to see how it works:

Select Top 1 * from Name
WHERE

Lastname > @LastName OR
(LastName=@Lastname AND FirstName > @FirstName)
ORDER BY LastName, FirstName

That should give you the basis to do what you need. It will return the next name AFTER @lastname,@firstname

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 13:25:54

to get the previous, by the way, you do the opposite:

Select Top 1 * from Name
WHERE
Lastname < @LastName OR
(LastName=@Lastname AND FirstName < @FirstName)
ORDER BY LastName DESC, FirstName DESC

- Jeff

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-11-25 : 14:48:01

This worked perfectly when I combined it with ID <> @LastID
from MichaelP. I needed that in the event that the firstname of my current person is null. Without the Id <> @LastID, it continued to return the same person. I'm still testing, but it all looks good so far. THANKS SO MUCH TO YOU BOTH!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources