sql query for returning top 1 column for each alphabet in one query

Question

4.00/5 (1 vote)

See more:

Hi ,

i need a sql query for the below req i have a solution but it is not efficient

what i need is

i need to highlight all the alphabets(A-Z) in the row, if there is a corresponding
element starting with that letter in the database.

currently i am using something like this

select top 1 [col] from aspnet_Membership where col like 'a%'

and i am repeating this for 26 times and then finding that there is an element for that letter and then highlighting that alphabet.

If there is an efficient way to do the query ,do reply.

Posted 10-Jun-11 9:15am

santoshamrutha

Add a Solution

2 solutions

Solution 2

hello,

I'm not sure if this is what you looking for. But I gave it a try anyway. You can store the letter from a-z in a table/temporary/variable table. Then write a SELECT statement to select the column where the first letter is in the list of letter.

SQL

DECLARE @temp table  (
    letter char(1)
)
INSERT INTO @temp SELECT 'a'
INSERT INTO @temp SELECT 'b'
INSERT INTO @temp SELECT 'c'
INSERT INTO @temp SELECT 'd'
INSERT INTO @temp SELECT 'e'
INSERT INTO @temp SELECT 'f'
INSERT INTO @temp SELECT 'g'
INSERT INTO @temp SELECT 'h'
INSERT INTO @temp SELECT 'i'
INSERT INTO @temp SELECT 'j'
INSERT INTO @temp SELECT 'k'
INSERT INTO @temp SELECT 'l'
INSERT INTO @temp SELECT 'm'
INSERT INTO @temp SELECT 'n'
INSERT INTO @temp SELECT 'o'
INSERT INTO @temp SELECT 'p'
INSERT INTO @temp SELECT 'q'
INSERT INTO @temp SELECT 'r'
INSERT INTO @temp SELECT 's'
INSERT INTO @temp SELECT 't'
INSERT INTO @temp SELECT 'u'
INSERT INTO @temp SELECT 'v'
INSERT INTO @temp SELECT 'w'
INSERT INTO @temp SELECT 'x'
INSERT INTO @temp SELECT 'y'
INSERT INTO @temp SELECT 'z'

SELECT TOP 1 [col] FROM aspnet_Membership 
 WHERE SUBSTRING([col], 1, 1) in (SELECT letter FROM @temp)

Posted 10-Jun-11 11:00am

Bryian Tan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Daniel_PS · Accepted Answer · 2011-06-10T10:20:00

Here's something that should do the trick in a single query:

SQL

SELECT Upper(SubString(csUriQuery, 1, 1)) as Letter, COUNT(*) as Number
FROM WebLogs
WHERE  ASCII(Upper(SubString(csUriQuery, 1, 1))) >= 65 AND ASCII(Upper(SubString(csUriQuery, 1, 1))) <= 90
GROUP BY Upper(SubString(csUriQuery, 1, 1))
ORDER BY Letter

If you're not worried about filtering out non-letter characters (i.e. 1, $, etc.) you should probably remove the WHERE clause.

You also don't really need the count in the select, except for verification.

Then, you could check if the letter is in the results, and you'll only hit the DB once.

If you would rather return a single string of characters that "exist" in the DB, You could probably modify it to return a single concatenated string of the letters, and use a Contains function in your code.

But this should get you where you want to go.

Happy coding.