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
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!
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.
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.
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).
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.
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.
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.