0

First note the reason I ask the question and hold out hope it is possible.

In MS SQL Server 2016, you have the ability to create a table with a column defined mathematically similar to a row_number() over(order by (select 1)) as oid via the identity(1,1) in a table definition.

Which leads to the question...

Is there built in functionality to define a table something like the following? (Yes this is pseudo code people...)

CREATE TABLE Persons (
    LastName varchar(255) NOT NULL
    FirstName varchar(255),
    Age int,
    MemberInFamilyID int Row_number() over(partition by LastName order by 
    Age,FirstName),
);

I know you can just define the column and populate it after the fact, but I'm curious if there is an efficient way to do it on table definition that populates on insert.

My current application is MS SQL Server, but feasibility in any database would be useful information as well. Particularly PostgreSQL.

John Drinane
  • 1,279
  • 2
  • 14
  • 25
  • 3
    That would be pointless and actually *dangerous* as it would allow duplicate entries if any rows were deleted. What are you trying to do? What's wrong with IDENTITY or SEQUENCE ? – Panagiotis Kanavos Jan 23 '18 at 16:48
  • 1
    I believe a view is what you are looking for. – Jacob H Jan 23 '18 at 16:54
  • 2
    Slight detour...you really should never store a person's age. As soon as the data is stored in the database it is stale. You should instead store their birthdate and calculate their age when you need it. – Sean Lange Jan 23 '18 at 16:57
  • contrived example... i can't share the actual use case... – John Drinane Jan 23 '18 at 16:58
  • I edited the question to make it more clear all i need is an id only unique to the family. – John Drinane Jan 23 '18 at 17:01
  • What do you gain by limiting the uniqueness of the field? A better understanding of the use-case might help. – David Rushton Jan 23 '18 at 17:08
  • I originally wanted it to make the person using the data to have something more intuitive to work with, but these comments make me realize it's probably not as valuable as initially thought. Thanks ... good chance I delete the question unless others think it useful for the community to keep it up. The question could change to "Can cycle based off a calculated max value in a sequence" ... I think this is what @a_horse_with_no_name was eluding to... – John Drinane Jan 23 '18 at 17:14
  • You might be looking for something like this: https://stackoverflow.com/q/4672629/330315 or this: https://stackoverflow.com/q/17924675/330315 or this: https://stackoverflow.com/q/6821871/330315 –  Jan 23 '18 at 17:17
  • @a_horse_with_no_name lol that is like a rehashing of what just occurred here... – John Drinane Jan 23 '18 at 17:29
  • If you going to use dob instad of age then just `unique index on tablename (LastName,dob)` would do it. unless you have twins and date of birth is not a timestamp... usually people use some person ID, like in identity card or passport or driver license... Of course you understand Last name is not even close a unique identifier for a family – Vao Tsun Jan 23 '18 at 17:33
  • @a_horse_with_no_name that answer basically has someone creating functionality that IMHO the database should be providing... ok thanks..., I've learned much by asking this dumb question :) – John Drinane Jan 23 '18 at 17:37

0 Answers0