| Author |
Topic |
|
TGarmon
Starting Member
18 Posts |
 Posted - 2002-07-05 : 12:27:21
|
| I thought I knew a lot aobut SQL syntax, but I am lost on this one.I am trying to determine the SQL syntax for returning the previous record and next record from a table in conjuction with a where clause.I would use the where clause on LName column. Any ideas?For example:FName LNameJohn DoeSusan JonesAlex SmithHarry AszThanks,John |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-07-05 : 12:55:28
|
| Do you want the next and previous records based on the physical ordering of the file, or based on the WHERE clause.It may also help us to know the WHERE clause, and the primary key of the table. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-05 : 13:12:10
|
YIKES  !!!!!Tables are not files. Tables are sets of rows are sets of columns. This is a basic concept that simply must be understood to understand R[sql]DBMSs.There is no concept of next or previous indiginous to SQL. These concepts assume an order (like lines in a file) for which SQL knows nothing about (absent an ORDER BY clause).Where you mean for you query is "Gimme the max(lastname) that is less than somelastname" as the previous and "Gimme the min(lastname) that is greater than somelastname" as the next.ECHO OFF<O>Edited by - Page47 on 07/05/2002 13:27:00 |
|
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-07-05 : 13:24:49
|
| This type of thing would work assuming you are looking for the previous and next LastName from a list ordered by LastName:DECLARE @LastName varchar(100)SET @LastName = 'Johnson'--Previous last nameSELECT TOP 1 LastName FROM PeopleWHERE LastName < @LastNameORDER BY LastName DESC--Next last nameSELECT TOP 1 LastName FROM PeopleWHERE LastName > @LastNameORDER BY LastName ASC |
|
|
|
TGarmon
Starting Member
18 Posts |
Posted - 2002-07-05 : 14:03:38
|
| JoshB's answer worked for me. Thanks to everyone for your help.John |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-07-05 : 14:20:40
|
| Glad you got an answer from someone who understands the basic concepts of DBMSs!Be aware that the solution mentioned is not designed to include duplicate records - say you have 5 John Smith's. You won't get John Smith as a next or previous record using the query. If that's intended, you are fine.Edited by - kevin snow on 07/05/2002 14:30:23 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-05 : 16:10:02
|
quote:
Glad you got an answer from someone who understands the basic concepts of DBMSs!
OH yeah? Well, I'm glad someone came along after me, took my suggestion as their own and wrote out the code for you (TGarmon) so you didn't have to go though the trouble of THINKING!!!!!<O> |
|
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-07-05 : 16:25:06
|
Sorry Page47, no one is stealing your idea's. I had started typing that response before I ever saw your's but was forced to attend to some other business. Why are you so grumpy, it's Friday!!! |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-05 : 16:29:30
|
Yeah, I dunno . . . maybe I'm too ready for the weekend, maybe I'm just a sad bitter man who's gonna die alone, pennyless and friendless, maybe, just maybe it's time for a pint of personality  ...No worries. Time for a way-too-fast-commute-home-on-the-motorbike-lanesplitting-and-flicking-off-the-cages.....NUKE AND PAVE...One World, One Giant Slab of Tarmac!!!<O>Edited by - Page47 on 07/05/2002 16:31:06 |
|
|
|
|
Previous/Next Record