Can I have help with some T-SQL syntax?
Can someone help me with a T-SQL query I am trying to create. I am sure it will all look very simple when it is resolved, but after a couple of hours on my own I still have not cracked it.
Simplified, I have a table with say 4 fields, Code, Name1, Name2, Name3
The same "Code" value might appear in several records because it may have different "Names" associated with it. This means the table might have 20,000 different records, but there are perhaps only 10,000 distinct values for "Code".
(Not my data - it comes from Amazon.)
I would like to list each "unique" value of "Code", and am happy to see the "Name" fields from the first (any) record for that "distinct" value of "Code".
I have tried things like the following. I know this fails (and I am combining WHERE with ON) but it hopefully gives an idea of what I am trying to achieve :
SELECT AMZ1.Code AS 'CODE', AMZ2.*
FROM VIEW_AmazonDataGroupedByCo
LEFT OUTER JOIN (SELECT TOP 1 *
FROM TABLE_AmazonData AS AMZ3
WHERE AMZ3.Code = AMZ1.Code) AS AMZ2 ON AMZ2.Code = AMZ1.Code
Can anyone suggest how I read the additional fields from the first (any) record containing a distinct "Code"?
Many thanks.
Simplified, I have a table with say 4 fields, Code, Name1, Name2, Name3
The same "Code" value might appear in several records because it may have different "Names" associated with it. This means the table might have 20,000 different records, but there are perhaps only 10,000 distinct values for "Code".
(Not my data - it comes from Amazon.)
I would like to list each "unique" value of "Code", and am happy to see the "Name" fields from the first (any) record for that "distinct" value of "Code".
I have tried things like the following. I know this fails (and I am combining WHERE with ON) but it hopefully gives an idea of what I am trying to achieve :
SELECT AMZ1.Code AS 'CODE', AMZ2.*
FROM VIEW_AmazonDataGroupedByCo
LEFT OUTER JOIN (SELECT TOP 1 *
FROM TABLE_AmazonData AS AMZ3
WHERE AMZ3.Code = AMZ1.Code) AS AMZ2 ON AMZ2.Code = AMZ1.Code
Can anyone suggest how I read the additional fields from the first (any) record containing a distinct "Code"?
Many thanks.
Koen Van Wielink
Can you give some sample records of your table and the desired output for those records? It's not entirely clear to me what you're trying to achieve.
Try this. I don't know what the difference between VIEW_AmazonDataGroupedByCo
With MyTable as
(Select Code, [Name], ROW_NUMBER() OVER(PARTITION BY Code ORDER BY [Name]) as MyRow
FROM TABLE_AmazonData)
Select Code, [Name]
From MyTable
Where MyRow = 1ASKER
Thanks for the fast responses.
Just a quick reply from me :
My "VIEW" is an already created SQL_Server View that gives me a simple list of the distinct "Code" values from the underlying TABLE.
I was hoping to use the output from that VIEW to then find further details from the TABLE, for the first (any) record with a matching "Code".
I will now try working with your suggestion, Phillip.
Thanks.
Just a quick reply from me :
My "VIEW" is an already created SQL_Server View that gives me a simple list of the distinct "Code" values from the underlying TABLE.
I was hoping to use the output from that VIEW to then find further details from the TABLE, for the first (any) record with a matching "Code".
I will now try working with your suggestion, Phillip.
Thanks.
ASKER
Hi Phillip,
Using SQL Server Management Studio Express, I can get your syntax to work perfectly for me when I execute it from a Query pane.
However, when I paste the same syntax into a new "View" in my SQL Server database and try to save it, I get a "Visual Studio Just-In-Time Debugger" error message and the SQL SMSE program crashes and closes.
Is there something about your T-SQL syntax that prohibits it from being the source of a SQL Server "View"?
Using SQL Server Management Studio Express, I can get your syntax to work perfectly for me when I execute it from a Query pane.
However, when I paste the same syntax into a new "View" in my SQL Server database and try to save it, I get a "Visual Studio Just-In-Time Debugger" error message and the SQL SMSE program crashes and closes.
Is there something about your T-SQL syntax that prohibits it from being the source of a SQL Server "View"?
Don't believe so. It certainly shouldn't make SSMS crash!
Maybe you need a repair on your SSMS.
Maybe you need a repair on your SSMS.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No - if it works, why change it!
ASKER
Other suggested solutions caused a debugger message on my system. I created a less elegant, but workable, solution of my own.