on 2017 Apr 24 3:15 AM
I am looking for an SQL statement to shuffle the last name column in a persons table. To be able to make a demo on the basis of existing data. A solution with the ability to shuffle more than one column in one statement would be welcome.
Thanks a lot and best regards, Robert
You can use a WINDOW clause to access the GivenName (or whatever value) of a neighbouring row.
As you have not supplied a test script, here's a sample from the SQL Anywhere (V16) Demo database, there's a table "employees" with the mentioned columns Surname, GivenName and Sex.
In that case, I have reduced the access to employess of one department and have rotated their surnames, so that each person gets the Surname of the previous person (ordered by EmployeeID) but separated for the sexes.
select EmployeeID, Surname, isnull( first_value(Surname) over (partition by Sex order by EmployeeID rows between 1 preceding and 1 preceding), last_value (Surname) over (partition by Sex order by EmployeeID rows between 1 following and unbounded following)) as PreviousRotatedSurname, GivenName, Sex from Employees where DepartmentID = 400 order by Sex, EmployeeID
Note the usage of the ISNULL() expression to supply the first person of each partition (i.e. the first female/male) with the surname of the last person.
The result contains both the original surname and the rotated value.
Result:
(Ah, I want to upload an image, however, that fails again...)
So here's the result as text output:
EmployeeID Surname PreviousRotatedSurname GivenName Sex ----------- -------------------- ---------------------- -------------------- --- 184 Espinoza Hildebrand Melissa F 207 Francis Espinoza Jane F 591 Barletta Francis Irene F 992 Butterfield Barletta Joyce F 1062 Blaikie Butterfield Barbara F 1507 Wetherby Blaikie Ruth F 1643 Lambert Wetherby Elizabeth F 1684 Hildebrand Lambert Janet F 318 Crow Ahmed John M 409 Weaver Crow Bruce M 888 Charlton Weaver Doug M 1191 Bucceri Charlton Matthew M 1576 Evans Bucceri Scott M 1607 Morris Evans Mark M 1740 Nielsen Morris Robert M 1751 Ahmed Nielsen Alex M
I don't know whether you might finally need to make sure that the original surname and the "rotated" one are different.
Aside: A completely different approach would be to use a CROSS JOIN between persons of the same sex and join when their surnames are different. Of course that will usually generate many more rows than the original table has. Just saying.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Of course I will do that Volker, thought it was easy to convert this to an UPDATE statement, but doesn't seem so. As in the end I'd like to update the surname column, I tried:
UPDATE Employees SET Surname = isnull( first_value(Surname) over (partition by Sex order by EmployeeID rows between 1 preceding and 1 preceding), last_value (Surname) over (partition by Sex order by EmployeeID rows between 1 following and unbounded following)) where DepartmentID = 400
But that doesn't work. Any idea how to convert the SELECT statement into an UPDATE of the Surname?
I'd put the original SELECT into a derived query (say, named dt) and then join the original TABLE with that dt, such as
UPDATE Employees e SET SurName = dt.PreviousRotatedSurname FROM Employees e INNER JOIN (...< the original SELECT, probably without the ORDER BY> ...) dt ON e.EmployeeID = dt.EmployeeID ORDER BY e.EmployeeID;
Note, for such ad-hoc updates, I do always use a SELECT to check whether the join will work as expected...
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.