Ask Your Question
0

Base query sequence number by group [closed]

asked 2017-08-31 03:04:07 +0100

Joe Castor gravatar image

I'm trying to give a sequential number for each record of a group; PhoneID for phone numbers where each PhoneID can have multiple records (phone numbers). I'd like the first phone number to be 1, then the next 2, etc. This will then be used to create a member directory, creating a phone number field containing up to 4 phone numbers, sequence and if it's a cell number. But I digress. My current method using COUNT give the total number of records in the group foe every record (where I thought I would get a sequence. Any ideas? I found examples using ROW_NUMBER() OVER (PARTITION BY but I couldn't get that to work at all. This is my current query: SELECT "PhoneID", "PhoneNumber", CASEWHEN( "Cell" = 1, 'C', '' ) AS "Cell", ( SELECT COUNT( "PhoneID" ) FROM "PhoneNumbers" WHERE "PhoneID" <= "a"."PhoneID" AND "PhoneID" = "a"."PhoneID" ) AS "GroupNr" FROM "PhoneNumbers" AS "a" ORDER BY "PhoneID" ASC, "PhoneName" ASC

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-09-01 05:06:20.864838

1 Answer

Sort by » oldest newest most voted
0

answered 2017-08-31 03:34:37 +0100

Ratslinger gravatar image

updated 2017-08-31 08:45:31 +0100

Just an educated guess - PhoneName has a sequence number in its' first position. You can use that:

SELECT "PhoneID", LEFT("PhoneName", 1) as "PhoneSequence",
       "PhoneNumber",
       CASEWHEN("Cell" = 1,'C','') AS "Cell"
FROM "PhoneNumbers" AS "a"
ORDER BY "PhoneID" ASC,
         "PhoneName" ASC

Dropped the 'GroupNr' as it didn't seem necessary.

Edit: Saw what you mean about not being able to rely on PhoneName. After some work came up with this:

SELECT PhoneID,
       PhoneNumber,
       ItemNr,
       CASEWHEN("Cell" = 1,'C','') AS "Cell"
FROM PhoneNumbers AS b
  LEFT JOIN (SELECT g.PhoneID,
                    COUNT(*) AS ItemNr,
                    g.PhoneName
             FROM PhoneNumbers AS g
               LEFT JOIN PhoneNumbers AS o
                      ON g.PhoneID = o.PhoneID
                     AND g.PhoneName >= o.PhoneName
             GROUP BY g.PhoneID,
                      g.PhoneName) AS z
         ON b.PhoneID = z.PhoneID
        AND b.PhoneName = z.PhoneName
ORDER BY "PhoneID" ASC,
         "PhoneName" ASC

This is based upon you having converted to a split DB. Also turn on Run SQL command directly on toolbar (SQL icon).

Hope this is better!

edit flag offensive delete link more

Comments

This works as far as syntax, but there is a problem in that there are gaps in the first digit of the "PhoneName" in that there at gaps (e.g. 2 is missing in some instances). I'll see if this is an impediment in the next step.

Joe Castor gravatar imageJoe Castor ( 2017-08-31 05:48:36 +0100 )edit

Thank you. This works great. Now I need to learn/understand "how" it works so I can do it in the future. Can I assume that b, g, o and z are just easy pseudo table names? A related question. I am not able to get an existing View to show in SQL Edit mode (I was testing in the embedded version before). I can create a new one, but not edit. Also, I can't change the name of a View. What am I overlooking? Thanks again for your help.

Joe Castor gravatar imageJoe Castor ( 2017-08-31 14:08:50 +0100 )edit

Hopefully my comments here will help. SQL. Yes, the letters are to give the tables another name since records are being combined in on themselves to create the groupings for the assignment of the sequence number. Joins may be a little tougher. This post (click here) may be of help in understanding the different types.

Views. First, you may want to look at this Bug Report (104651).

Ratslinger gravatar imageRatslinger ( 2017-08-31 18:41:45 +0100 )edit

Now, since I hardly use views, here is what I have uncovered. In the embedded DB I could edit in SQL mode but found nothing to rename the view. In split mode (this also includes external DB's such as MySQL, PostgreSQL, etc) you cannot rename or edit in SQL mode through Base (news to me). Don't see a specific bug report on this. I don't typically don't use Base for much on table, query, view creation/editing but rather external programs such as SQL Workbench/J or MySQL Workbench.

Ratslinger gravatar imageRatslinger ( 2017-08-31 18:50:39 +0100 )edit

Since Views are in a category of tables, with those external programs I have no problem modifying/renaming items. I have also found, especially in your case, the actual structure is in the database/xxx.script file. I was able to successfully modify/rename views there although extreme care should be taken if doing this. I would recommend copying the entire database sub folder before attempting.

Ratslinger gravatar imageRatslinger ( 2017-08-31 18:55:50 +0100 )edit

Thank you for the response and for the w3schools website info. What a great site. Back to school. I'll further investigate your tips about managing views. In the meantime, I've created a mirror of my views as queries which will allow me to make changes then delete and recreate the view. Thanks again for all your help and support.

Joe Castor gravatar imageJoe Castor ( 2017-09-01 14:38:05 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-08-31 03:04:07 +0100

Seen: 109 times

Last updated: Aug 31 '17