Returning records alphabetically -- sql server programming

Rick Mogstad
5/11/2004 2:46:39 PM

if you cannot guarantee that the pair of FirstName and LastName will be unique without the
customer ID, then you will not be able to determine the next row based on that information alone.
If you are using a disconnected recordset, you should be able to just return the records in the
correct order, and then move through the records in the order they are presented...

[quoted text, click to view]

Rick Mogstad
5/11/2004 2:59:07 PM

[quoted text, click to view]

It should still be noted that this will only work if the 'YourTable.Name' column is unique, as the
OP mentioned.

Rick Mogstad
5/11/2004 4:25:31 PM

Where did you get the 7 in CustomerID>7? I assume that this is just a test case, and it will be
replaced by some variable?

Also, are you planning to be able to pass in the FirstName in order to find the next row? If not,
you dont need it in this query.

[quoted text, click to view]

Adam Machanic
5/11/2004 5:53:00 PM

If you have a name and want to get only the next name, you can do the
following:

SELECT TOP 1 (whatever)
FROM YourTable
WHERE YourTable.Name > (current name)
ORDER BY Name

But it sounds as if you already have all of the names in a disconnected
recordset. Have you looked at the Sort property of the ADODB.Recordset
object? (Assuming you're using ADO) This will allow you to sort the data
in your disconnected recordset, after which MoveNext will work in the way
you describe. You could also simply append an ORDER BY clause to your query
that populates the recordset.

Finally, please note that even if you have a clustered index, on a given
column, you are not guaranteed to get the data back in that order unless you
specify an ORDER BY list.

[quoted text, click to view]

Adam Machanic
5/11/2004 6:05:12 PM

Ahh, I missed that requirement... This will fix that issue:

SELECT TOP 1 (whatever)
FROM YourTable
WHERE YourTable.Name >= (current name)
AND CASE
WHEN YourTable.Name = (current name) THEN
CASE WHEN YourTable.ID > (current id) THEN 1
ELSE 0 END
ELSE 1 END = 1
ORDER BY Name, ID

[quoted text, click to view]

Chris
5/11/2004 10:34:09 PM

I have a customers table with CustomerID, LastName and FirstName fields in
it. If I have a disconnected recordset is it possible to create a query
that returns the next customer based on a query supplying the one I am
currently on. For example:-

2, Evans, Frank,
4, Jones, Paul
3, Smith, Ian
1, Smith, John

Lets say i'm on Jones, then next person alphabetically is Smith, Ian (but no
numerically on the unique CustomerID). I thought about doing a TOP 1 query
that returned the next customer but how can I do this so it always returns
the next one alphabetically based on the order of LastName, FirstName and
finally CustomerID. I also need to cater for there being two customers with
the same LastName and FirstName (but having a unique CustomerID).

I thought maybe creating a clustered index on all three fields which sorts
them into the correct order, but I still can't work out how to return the
next one by supplying the one I'm currently on. Can this be done?

Thanks,
Chris

Chris
5/12/2004 12:16:23 AM

Thanks for the help. I changed your sample into the following query:-

SELECT TOP 1 *
FROM Customers
WHERE Customers.LastName >= 'Evans'
AND CASE
WHEN Customers.LastName = 'Evans' THEN
CASE WHEN Customers.CustomerID > 7 THEN 1
ELSE 0 END
ELSE 1 END = 1
ORDER BY LastName, CustomerID

This worked great - I never knew you could use CASE in a WHERE clause. I am
having trouble understanding exactly how it works. The CASE help in Books
Online doesn't seem to go into too much depth.

My next task is to build FirstName into this as well, but I can't figure out
how.

Thanks.

[quoted text, click to view]

Chris
5/12/2004 8:52:55 AM

Rick,

Yeah, 7 and Evans will both be replaced with variables. I was just doing
this as a test and this was one example.

Yes I would like to pass in FirstName as well. That way if i've got lots of
people with the same last name I will be moving through these in a sensible
order (e.g. Ian Smith, John Smith, Kevin Smith) instead of next going to the
CustomerID which might do - Kevin Smith, Ian Smith, John Smith for example.
So I want to order by LastName, FirstName and then finally CustomerID.

Thanks,
Chris

[quoted text, click to view]

v-mingqc NO[at]SPAM online.microsoft.com (
5/13/2004 7:11:50 AM

Hi Chris,

Based on my understanding to this issue, I think you may have a try on this
code, which may meet your needs to include both first name and last name.
--
SELECT TOP 1 *
FROM Customers
WHERE Customers.LastName >= 'Evans'
AND CASE
WHEN Customers.LastName = 'Evans' THEN
CASE WHEN Cusotmers.FirstName = 'Who' THEN
CASE WHEN Customers.CustomerID > 7 THEN 1
ELSE 0 END
ELSE 1 END
ELSE 1 END = 1
ORDER BY LastName, CustomerID
--
Anyway, if above could not meet your requirement, would you please post
show me some sample data and the result you want.

Moreover, more detailed information for CASE statement could be found at
Books Online or MSDN Online:

Conditional Data Processing Using CASE
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_qd_11_6l0z.asp

Using CASE
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_con_04_6ckl.asp

Hope this helps and thanks for your patience and cooperation. If you have
any questions or concerns, don't hesitate to let me know. We are here to be
of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Adam Machanic
5/13/2004 9:24:38 AM

I think he'll need something more along these lines:

SELECT TOP 1 *
FROM Customers
WHERE Customers.LastName >= 'Evans'
AND Customers.FirstName >= 'Who'
AND CASE
WHEN Customers.LastName = 'Evans' THEN
CASE WHEN Cusotmers.FirstName = 'Who' THEN
CASE WHEN Customers.CustomerID > 7 THEN 1
ELSE 0 END
ELSE 1 END
ELSE 1 END = 1
ORDER BY LastName, FirstName, CustomerID

[quoted text, click to view]

Steve Kass
5/13/2004 9:46:34 AM

Adam,

This query won't return 'Adam Machanic', if it is the first name in
alphabetical order after 'Who Evans', since 'Adam' >= 'Who' is false.
Unfortunately, SQL Server doesn't support row constructors, which would
be handy here:

where (Customers.LastName, Customers.FirstName, Customers.CustomerID) >
('Evans', 'Who', 7)

That leaves choices like these:

1.
where
Customers.LastName > 'Evans'
or (Customers.LastName = 'Evans' and Customers.FirstName > 'Who')
or (Customers.LastName = 'Evans' and Customers.FirstName = 'Who' and
Customers.CustomerID > 7)

2.
where
cast(Customers.LastName as char(20))
+ cast(Customers.FirstName as char(20))
+ str(Customers.CustomerID,15)
[quoted text, click to view]
cast('Evans' as char(20))
+ cast('Who' as char(20))
+ str(7,15)

-- replace the 20's with the declared maximum length of the varchar in
the column, or use nchar if it's nvarchar

3. If this is a critical query, depending on the size of the table and
the entire workload of the system this might be an option:

alter table Customers add NameSortColumn as
cast(Customers.LastName as char(20))
+ cast(Customers.FirstName as char(20))
+ str(Customers.CustomerID,15)

-- assuming these three columns form a key, otherwise eliminate unique
create unique index Customers_NameSortColumn on Customers(NameSortColumn)

and then the query is easier to write and potentially faster. The
nonclustered index should be ok for returning a single "next customer" row:

select top 1 * from Customers
where NameSort >
cast('Evans' as char(20))
+ cast('Who' as char(20))
+ str(7,15)

-- Steve Kass
-- Drew University
-- Ref: 7158F6BC-EC8B-4CC0-8009-A7DB70148783

[quoted text, click to view]

Adam Machanic
5/13/2004 9:51:48 AM

You're right, I didn't think that through too well... First post of the
morning :)

Anyway, I think the computed column (option 3) is probably the most
straightforward solution, from a query simplicity point of view.

[quoted text, click to view]

Adam Machanic
5/13/2004 5:50:51 PM

The problem is the use of STR(Customers.CustomerID, 15); I'm not sure why
that's non-deterministic, but you can use the following deterministic code
instead:

right(replicate(' ', 15) + cast(Customers.CustomerID as varchar(15)), 15)

[quoted text, click to view]

Chris
5/13/2004 10:40:04 PM

I tried choice 1 and 2 first. Both were not as fast as I require when i've
a large number of customers in the table. It goes slow when I add the ORDER
BY to the query to make sure it returns the next record based on the correct
sort order required (LastName, FirstName, CustomerID).

I then tried option 3. It would not let me create the index, giving the
error "Cannot create index because the key column 'NameSortColumn' is
non-deterministic or imprecise.". When I do a query to find out why it
failed, it's because it is imprecise (IsPrecise returns FALSE). Can I get
around this?

Thanks.

[quoted text, click to view]

Steve Kass
5/14/2004 10:22:22 AM

Thanks for the tip, Adam!

SK

[quoted text, click to view]

Chris
5/14/2004 2:50:41 PM

Thanks,

This now works and is fast!

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Returning records alphabetically