SQL to Sort by Last Name, Count...

Mike Grodesky

SQL to Sort by Last Name, Count...
We have a table containing various columns with a few thousand records:

Col 1 = Last Name
Col 2 = First name
.
.
.
Col 8 = Box Number
.
.
.
Col N

A new requirement came up to "reshuffle" the Box Number (Col 8) by a
certain increment (that could change later, but now the counter is 800
records will fit in a box)

So, my task is to sort the records by last name, first name, then count
each "800" records to put in each Box, the first 800 Col 8 = "1", next 800
Col 8 = "2" and so on.

Just looking for suggestions on the logic. Once it's established, I can
change the increment from "800" to whatever if the customer wants to get
bigger (or smaller) boxes for storage purposes.

Thanks, Mike G.

Michael A. Grodesky
Information Technology
Marist College
3399 North Road
Poughkeepsie, NY 12601
office: (845) 575-3567

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: SQL to Sort by Last Name, Count...
(in response to Mike Grodesky)
Mike,

Try this. I use an increment of 5 below. The idea is to get the "rank" and
then massage it.

Should work as long as table is small/mdedium.

SELECT
MINE.TR_STUDENT_LST_NME
, MINE.TR_STUDENT_1ST_NME
, INTEGER((COUNT(*) - 1)/ 5) + 1 AS BOX
FROM ... MINE
, ... YOUR
WHERE
(MINE.TR_STUDENT_LST_NME CONCAT
MINE.TR_STUDENT_1ST_NME)
> (YOUR.TR_STUDENT_LST_NME CONCAT
YOUR.TR_STUDENT_1ST_NME)
GROUP BY MINE.TR_STUDENT_LST_NME
, MINE.TR_STUDENT_1ST_NME
ORDER BY 1,2,3
WITH UR

Holler if questions (and let me know if it works for you).

Thx
Suresh

>From: Mike Grodesky <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: [DB2-L] SQL to Sort by Last Name, Count...
>Date: Wed, 24 Jan 2007 10:00:40 -0500
>
>We have a table containing various columns with a few thousand records:
>
>Col 1 = Last Name
>Col 2 = First name
>.
>.
>.
>Col 8 = Box Number
>.
>.
>.
>Col N
>
>A new requirement came up to "reshuffle" the Box Number (Col 8) by a
>certain increment (that could change later, but now the counter is 800
>records will fit in a box)
>
>So, my task is to sort the records by last name, first name, then count
>each "800" records to put in each Box, the first 800 Col 8 = "1", next 800
>Col 8 = "2" and so on.
>
>Just looking for suggestions on the logic. Once it's established, I can
>change the increment from "800" to whatever if the customer wants to get
>bigger (or smaller) boxes for storage purposes.
>
>Thanks, Mike G.
>
>Michael A. Grodesky
>Information Technology
>Marist College
>3399 North Road
>Poughkeepsie, NY 12601
>office: (845) 575-3567
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

_________________________________________________________________
Turn searches into helpful donations. Make your search count.
http://click4thecause.live.com/search/charity/default.aspx?source=hmemtagline_donation&FORM=WLMTAG

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm