home *** CD-ROM | disk | FTP | other *** search
- Newsgroups: comp.databases.sybase
- Path: sparky!uunet!psinntp!ctp!sreit
- From: sreit@ctp.com (Stefan Reitshamer)
- Subject: SQL sort problems (group by?)
- Message-ID: <1993Jan4.193801.25423@ctp.com>
- Sender: news@ctp.com
- Nntp-Posting-Host: punch.ctp.com
- Organization: Cambridge Technology Partners
- Date: Mon, 4 Jan 1993 19:38:01 GMT
- Lines: 52
-
- I need to sort data in a bizarre way and have NO idea how to do it (other
- than looping through rows and assigning index numbers and then selecting
- by ascending index number). Here's the data involved:
-
- Claim # Subscriber # Date
- ------- ------------ --------
- 22402 111111111 10/10/92
- 55600 333333333 04/04/92
- 45200 222222222 09/15/92
- 22404 111111111 08/08/92
- 22400 111111111 09/09/92
- 44900 111111111 08/30/92
- 45202 222222222 07/01/92
-
- The last 2 digits of the claim # are adjustment numbers (the 00 claim is the
- original, others are adjustments of the original).
- I need to sort this data by subscriber number, and by descending date
- within each sub #. BUT, if a claim has been adjusted, I need the adjusted
- claims to appear after the original (regardless of the date).
-
- Basically, rows with the first 3 digits of the claim # in common should be
- grouped together. These groups should then be sorted by sub # and descending
- date.
-
- So, the above data would be sorted as follows:
-
- Claim # Subscriber # Date
- ------- ------------ --------
- 44900 111111111 05/05/92 ]Group 1
- 22400 111111111 02/02/92 ]Group 2
- 22402 111111111 03/03/92 <- Adjustment of 22400 ]Group 2
- 22404 111111111 08/08/92 <- Adjustment of 22400 ]Group 2
- 45200 222222222 01/01/92 ]Group 3
- 45202 222222222 07/01/92 ]Group 3
- 55600 333333333 04/04/92 ]Group 4
-
- I was thinking something like:
- select *
- group by round(Claim#/100,0)
- order by Subscriber,Date desc
- but I guess this is incorrect usage of the "group by" clause.
-
- Does anyone know how to "select" this? (Hope this explanation makes some sort
- of sense)
-
- Thanks very much!
- Stefan
- --
- ``````````````````````````````````````````````````````````````````````````````
- Stefan Reitshamer sreit@ctp.com Cambridge Technology Partners, Inc.
- Opinions expressed herein belong to no one at all (just to be safe).
- ``````````````````````````````````````````````````````````````````````````````
-