ADFS SAML Single Logout

Question

I think a follow-up question based on the problem from HackerRank, and wonder if it is possible to solve by SQL.

Sample Input

Let's say that CITY has four entries: DEFG, ABC, PQRSER, and WXY

Sample Output

ABC 3
DEFG 4
PQRSER 6

I was wondering if I can use SQL to ordered length of city and city name alphabetically with the respective lengths. And if there are options for the same length city (e.g., ABC, WXY), I just want to choose the first city name, because it comes first alphabetically. Thanks!

Solution

A bit late but hope it helps someone. The NameID has a role to play here. Did you see this quote in the "Using AD FS 2.0 as the SAML 2.0 Identity Provider for the Service Provider Sample" readme step 3?

"Note:

· A NameIdentifier claim is not included in the outgoing claim from AD FS by default. This can be added as a Claim transformation rule. This is necessary for logout to perform correctly. "

Was the partner IDP sending a NameID and was your ADFS configured to send a NameID to the RP App? I have configured your very same setup where my IDP and RP STS are both ADFS and this works fine.

Please note the WIF SAML extensions CTP is not supported at this stage. I am assuming this is not a production setup?

Windows Server 2012 AD FS 2.1 is not required for this to work. Although it has some nice extra features coming in the 8.1 server release that might influence your upgrade decisions :)

Comments

[Tim Biegeleisen]

@DanielGale Yes.

[Daniel Gale]

@TimBiegeleisen You are correct then, aggregation is needed.

[Daniel Gale]

LEN() is used in SQL Server, but MySQL uses Length()

[sarath s rajendran]

I think his question is about SQL not MySql :-)

[Daniel Gale]

Nice. I had the most of it, forgot the 'city' name as secondary order.

[cdaiga]

What you first wrote was not the answer to the question, you know it!

[Daniel Gale]

The question is tagged mysql.

[sarath s rajendran]

He tagged both sql and Mysql :-)

[Daniel Gale]

So if two cities exist of the same length, ABC and XYZ, you do not want to see XYZ in the list?

[Daniel Gale]

Looks like we both omitted that cities need to be grouped by size anyway :). Better luck next time.

[Tim Biegeleisen]

it works ... this query won't run on any other database, nor on certain versions of MySQL. Also, there is no guarantee that this will always pick the first city name. Doing SELECT * with GROUP BY is generally bad.

[cdaiga]

It will always pick the first alphabetically sorted city per the length. I don't really care if it won't run on any other database. The OP should adapt it for any other database.

[31piy]

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

[SY.]

Thanks, but this code did not return the column of CHAR_LENGTH(city).

[SY.]

I wonder if I can use only one query or that would be a problem? SELECT MIN(city) AS first_city, CHAR_LENGTH(city) FROM yourTable GROUP BY CHAR_LENGTH(city) ORDER BY CHAR_LENGTH(city);