select records from a table where the records belong to fist clienname when ordered in ascending order of clientname

  • Using SQLServer Management Studio 2005

    I need to develop a query/storedproc using which I can retrieve all records pertaining to first clientname after they are ordered in Ascending order of Clientname)

    Eg:

    # In the above table, I want to retrieve all the rows pertaining to clientname 'A123'

    But in future, if a new clientname say: 'A001' is added, then my query should retrieve records pertaining to 'A001' and not 'A123'

    Appreciate any help! Thanks!

  • are you familiar with Parameters yet?

    DECLARE @Client varchar(50)

    SET @Client = 'A123';

    --SELECT @Client = MIN(clientName) FROM MyTable;

    With MyCTE (clientName,col2,col3,col4)

    AS

    (

    SELECT 'A123','val1','val2','val3' UNION ALL

    SELECT 'A123','val4','val5','val6' UNION ALL

    SELECT 'B234','val7','val8','val9' UNION ALL

    SELECT 'C789','val1','val5','val6' UNION ALL

    SELECT 'C789','val2','val3','val4'

    )

    SELECT * FROM MyCTE

    WHERE clientName = @Client

    ORDER BY clientName ASC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I am not passing the clientname because it could be anything that comes first when ordered in ascending order.. today it could be A123 and tomorrow if I add a new clientname 'A012', then it should be 'A012'

  • In that case you can use ROW_NUMBER() to get the desired result

    With MyCTE (clientName,col2,col3,col4)

    AS

    (

    SELECT 'A123','val1','val2','val3' UNION ALL

    SELECT 'A123','val4','val5','val6' UNION ALL

    SELECT 'B234','val7','val8','val9' UNION ALL

    SELECT 'C789','val1','val5','val6' UNION ALL

    SELECT 'C789','val2','val3','val4'

    )

    SELECT*

    FROM(

    SELECTROW_NUMBER() OVER( ORDER BY clientName ) AS RN, *

    FROMMyCTE

    ) AS CTE

    WHERECTE.RN = 1


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • also, something like this, with a simple subquery would work as well:

    SELECT * FROM MyTable

    WHERE clientName IN (SELECT MIN(clientName) FROM MyTable)

    ORDER BY clientName ASC;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for both the responses.

    I will go with the Lowell's solution as it looks a bit simpler.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply