Geek asks and answers

Order a single request in MS Access SQL

OK I have a particularly unpleasant problem of streamlining the connection, so any help would be appreciated.

The scenario is this:

Participants table with the following entries (actual data):

REI882
YUI987
POBO37
NUBS26
BTBU12
MZBY10
TYBW54

(They are listed in the order in which I want them to be returned from my request.)

There are a number of business rules for creating these MemberIDs, which, in my opinion, are not related to sorting. They are historical and stone. I am stuck with them. They indicate the experience of a member.

The order is executed from the last 4 characters in the ID, ascending. The first two characters of the identifier are completely meaningless regarding sorting.

Thus, the highest possible record is A001 (oldest) and the lowest possible record is ZZ99 (least senior).

-, , , ... , . , :

, . , .

SELECT * FROM (

    SELECT Member.ID
    FROM Member
    WHERE (((IsNumeric(Mid([Member.ID],4,1)))=-1)) **check the 4th character is a digit
    ORDER BY (Mid([Member.ID],3,1)), (Mid([Member.ID],4,1)), (Mid([Member.ID],5,1)), (Mid([Member.ID],6,1))
) t1

UNION

SELECT * FROM (
    SELECT Member.ID
    FROM Member
    WHERE (((IsNumeric(Mid([Member.ID],4,1)))=0)) **check the 4th character is a letter
    ORDER BY (Mid([Member.ID],3,1)), (Mid([Member.ID],4,1)), (Mid([Member.ID],5,1)), (Mid([Member.ID],6,1))
) t2

CRAZY ! - , ( ​​!) , .

, , , , - !!!

edit: :

YUI987
MZBY10
NUBS26
BTBU12
REI882
POBO37
TYBW54
+2
3

ORDER BY SELECT, UNION SELECT .

.

:

SELECT ID FROM(  
(SELECT Member.ID,1 AS T,Left([Member.ID],2) AS Part1, Right([Member.ID],4) AS Part2
  FROM Member
  WHERE (((IsNumeric(Mid([Member.ID],3,1)))=-1)))    
UNION    
  (SELECT Member.ID,2 AS T,Left([Member.ID],3) AS Part1, Right([Member.ID],3) AS Part2
  FROM Member
  WHERE (((IsNumeric(Mid([Member.ID],4,1)))=-1) and ((IsNumeric(Mid([Member.ID],3,1)))=0)))     
UNION    
  (SELECT Member.ID,3 AS T,Left([Member.ID],4) AS Part1, Right([Member.ID],2) AS Part2
  FROM Member
  WHERE (((IsNumeric(Mid([Member.ID],5,1)))=-1) and ((IsNumeric(Mid([Member.ID],4,1)))=0)))     

ORDER BY T,Part1,Part2)

@ : , . , .

+1

RIGHT.

-

SELECT  ID
FROM    (
            SELECT  ID 
            FROM    (
                        SELECT Member.ID
                        FROM Member
                        WHERE (((IsNumeric(Mid([Member.ID],4,1)))=-1)) **check the 4th character is a digit
                    ) t1
            UNION
            SELECT  ID 
            FROM    (
                        SELECT Member.ID
                        FROM Member
                        WHERE (((IsNumeric(Mid([Member.ID],4,1)))=0)) **check the 4th character is a letter
                    ) t2
        ) t3
ORDER BY RIGHT(ID,4)
0

How to skip UNION?

SELECT members.ID
FROM members
ORDER BY Right([ID],3), Right(id,4)

Under the new rules, this mess can work.

SELECT 
    Len(IIf([textId] Like "[a-z][a-z][0-9][0-9][0-9][0-9]",Left([textid],2),
    IIf([textId] Like "[a-z][a-z][a-z][0-9][0-9][0-9]",Left([textid],3),
    IIf([textId] Like "[a-z][a-z][a-z][a-z][0-9][0-9]",Left([textid],4),"_")))) AS Ln, 

    IIf(textId Like "[a-z][a-z][0-9][0-9][0-9][0-9]",Left(textid,2),
    IIf(textId Like "[a-z][a-z][a-z][0-9][0-9][0-9]",Left(textid,3),
    IIf(textId Like "[a-z][a-z][a-z][a-z][0-9][0-9]",Left(textid,4),"_"))) AS Alpha, 

    IIf(textId Like "[a-z][a-z][0-9][0-9][0-9][0-9]",Val(Right(textid,4)),
    IIf(textId Like "[a-z][a-z][a-z][0-9][0-9][0-9]",Val(Right(textid,3)),
    IIf(textId Like "[a-z][a-z][a-z][a-z][0-9][0-9]",Val(Right(textid,2)),0))) AS Numbr, 

    table.textid
FROM table
ORDER BY 
    Len(IIf([textId] Like "[a-z][a-z][0-9][0-9][0-9][0-9]",Left([textid],2),
    IIf([textId] Like "[a-z][a-z][a-z][0-9][0-9][0-9]",Left([textid],3),
    IIf([textId] Like "[a-z][a-z][a-z][a-z][0-9][0-9]",Left([textid],4),"_")))), 

    IIf(textId Like "[a-z][a-z][0-9][0-9][0-9][0-9]",Left(textid,2),
    IIf(textId Like "[a-z][a-z][a-z][0-9][0-9][0-9]",Left(textid,3),
    IIf(textId Like "[a-z][a-z][a-z][a-z][0-9][0-9]",Left(textid,4),"_"))), 

    IIf(textId Like "[a-z][a-z][0-9][0-9][0-9][0-9]",Val(Right(textid,4)),
    IIf(textId Like "[a-z][a-z][a-z][0-9][0-9][0-9]",Val(Right(textid,3)),
    IIf(textId Like "[a-z][a-z][a-z][a-z][0-9][0-9]",Val(Right(textid,2)),0)))
0
source

More articles:


All Articles