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

Question

Post reply

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

Annee

Ten Centuries

Points: 1064
More actions
May 20, 2013 at 1:37 pm

#274093

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!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Annee Ten Centuries Points: 1064 More actions · Answer 2

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'

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

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/

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!

Annee Ten Centuries Points: 1064 More actions · Answer 5

Thanks for both the responses.

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