December 21, 2018 at 1:15 pm
Hi,
I have a table with rows of data as follows:
R_Index Customer ID First Name Last Name
2 4205 CHRIS ROGERS
3 4205 AMANDA HOWARD
I am trying to query this such that I get all the records in the table with the first name in a customer ID.
And then all the records in a customer ID with second first name
So it should look like this :
Chris
then AMANDA
his is what I tryes :
SELECT Min([R_Index]), [Last Name]) AS "Last name"
FROM [PLM].[dbo].[References]
Where [Customer ID] = '4205'
Group by [R_Index]
Order by [R_Index]
Any ideas
Thank you
December 21, 2018 at 1:50 pm
I've really no idea what you mean here, sorry. Could you try to re-explain your goal? It would likely be far more useful, as well, to provide more sample data. You've given us 2 rows and said that the first name from both need to be returned; that would be as simple as:SELECT FirstName
FROM YourTable
ORDER BY R_Index;
Clearly that isn't your goal, so giving more samples, with more variations will really help us work out what you're after.
When you encounter an error, it's useful to let us know what the error is or why the SQL didn't work as you expected as well. In this case, however, it's because you passed 2 parameters to MIN. Aggregate functions, at least in T-SQL, only accept 1 parameter.MIN({Expression}) is valid, where as MIN({Expression1}, {Expression2}[,{Expression3},etc]) is not.
Help us help you and explain your goals more, and I;m sure someone will be able to find you a solution.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 21, 2018 at 1:57 pm
You might try grouping on the ID and ordering by the index, but as Thom noted, your description doesn't quite make sense.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 21, 2018 at 2:00 pm
itmasterw 60042 - Friday, December 21, 2018 1:15 PMHi,
I have a table with rows of data as follows:
R_Index Customer ID First Name Last Name
2 4205 CHRIS ROGERS
3 4205 AMANDA HOWARDI am trying to query this such that I get all the records in the table with the first name in a customer ID.
And then all the records in a customer ID with second first name
So it should look like this :
Chris
then AMANDAhis is what I tryes :
SELECT Min([R_Index]), [Last Name]) AS "Last name"
FROM [PLM].[dbo].[References]
Where [Customer ID] = '4205'
Group by [R_Index]
Order by [R_Index]
Any ideas
Thank you
Your explanation makes no sense to me, particularly this: "And then all the records in a customer ID with second first name " It's bonkers.
You've been here long enough to know this: please provide sample DDL, sample data in the form of INSERT statements and sample results, which correspond with the sample data you have provided.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 21, 2018 at 3:06 pm
Hi, Sorry,
Let me try to make this clearer.
I have records like this:
2 4205 CHRIS ROGERS
3 4205 AMANDA HOWARD
4 2256 Jim Welch
5 2256 Bill Horthone
So I need a query that will first retun all the names that come first in the customer ID
Then I need to be able to change the query so that I get all the 2nd names in the cust omer ID.
So the the first one would return:
2 4205 CHRIS ROGERS
4 2256 Jim Welch
The 2nd query returns this :
3 4205 AMANDA HOWARD
5 2256 Bill Horthone
Thank you
December 21, 2018 at 5:17 pm
Not sure if this would be the fastest solution, but this might be ok:
DECLARE @custid int = NULL --1805;
DECLARE @ord tinyint = 1;
SELECT Refs.R_Index, Lname.Lastname as "Last Name"
FROM [PLM].[dbo].[References] Refs
CROSS APPLY (
SELECT TOP 1 LnameInner.Lastname
FROM (
--@ord as depth into the inner group range
SELECT TOP @ord
RefInner.R_Index, RefInner.Lastname
FROM [PLM].[dbo].[References] RefInner
WHERE RefInner.Custid = Refs.Custid
ORDER BY RefInner.id ASC
--if you truly want to this ordered in the sequence / insert order of the References table p/group
) LnameInner
ORDER BY LnameInner.id DESC
)Lname ----CROSS APPLY
WHERE Refs.Custid =
CASE WHEN @custid IS NULL
THEN Refs.Custid
ELSE @custid
END
ORDER BY Refs.R_Index DESC
December 22, 2018 at 8:11 am
Declare @custid int = Null --1805;
Declare @ord tinyint = 1;
With customers
As (
Select ref.R_Index
, LastName = ref.[Last Name]
, CustOrder = row_number() over(Partition By CustID Order By R_Index)
From PLM.dbo.[References] ref
)
Select *
From customers
Where CustOrder = @ord
And (@custid Is Null Or CustID = @custid);
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 22, 2018 at 9:06 am
December 22, 2018 at 4:59 pm
Thanks, everyone, this really helped.
Thank you
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply